Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6bdc300

Browse files
committed
Fix indexable-row-comparison logic to account for covering indexes.
indxpath.c needs a good deal more attention for covering indexes thanit's gotten. But so far as I can tell, the only really awful breakageis in expand_indexqual_rowcompare (nee adjust_rowcompare_for_index),which was only half fixed inc266ed3. The other problems aren'tbad enough to take the risk of a just-before-wrap fix.The problem here is that if the leading column of a row comparisonmatches an index (allowing this code to be reached), and some latercolumn doesn't match the index, it'll nonetheless believe that thatcolumn matches the first included index column. Typically that'lllead to an error like "operator M is not a member of opfamily N" asa result of fetching a garbage opfamily OID. But with enough badluck, maybe a broken plan would be generated.Discussion:https://postgr.es/m/25526.1549847928@sss.pgh.pa.us
1 parent72d71e0 commit6bdc300

File tree

3 files changed

+22
-2
lines changed

3 files changed

+22
-2
lines changed

‎src/backend/optimizer/path/indxpath.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3979,7 +3979,7 @@ expand_indexqual_rowcompare(RestrictInfo *rinfo,
39793979
break;/* no good, volatile comparison value */
39803980

39813981
/*
3982-
* The Var side can match any column of the index.
3982+
* The Var side can match anykeycolumn of the index.
39833983
*/
39843984
for (i=0;i<index->nkeycolumns;i++)
39853985
{
@@ -3991,7 +3991,7 @@ expand_indexqual_rowcompare(RestrictInfo *rinfo,
39913991

39923992
break;
39933993
}
3994-
if (i >=index->ncolumns)
3994+
if (i >=index->nkeycolumns)
39953995
break;/* no match found */
39963996

39973997
/* Add column number to returned list */

‎src/test/regress/expected/index_including.out

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -127,6 +127,23 @@ INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) A
127127
ERROR: null value in column "c2" violates not-null constraint
128128
DETAIL: Failing row contains (1, null, 3, (4,4),(4,4)).
129129
INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
130+
explain (costs off)
131+
select * from tbl where (c1,c2,c3) < (2,5,1);
132+
QUERY PLAN
133+
------------------------------------------------
134+
Bitmap Heap Scan on tbl
135+
Filter: (ROW(c1, c2, c3) < ROW(2, 5, 1))
136+
-> Bitmap Index Scan on covering
137+
Index Cond: (ROW(c1, c2) <= ROW(2, 5))
138+
(4 rows)
139+
140+
select * from tbl where (c1,c2,c3) < (2,5,1);
141+
c1 | c2 | c3 | c4
142+
----+----+----+----
143+
1 | 2 | |
144+
2 | 4 | |
145+
(2 rows)
146+
130147
DROP TABLE tbl;
131148
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
132149
UNIQUE(c1,c2) INCLUDE(c3,c4));

‎src/test/regress/sql/index_including.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -73,6 +73,9 @@ SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conre
7373
INSERT INTO tblSELECT1,2,3*x,box('4,4,4,4')FROM generate_series(1,10)AS x;
7474
INSERT INTO tblSELECT1,NULL,3*x,box('4,4,4,4')FROM generate_series(1,10)AS x;
7575
INSERT INTO tblSELECT x,2*x,NULL,NULLFROM generate_series(1,10)AS x;
76+
explain (costs off)
77+
select*from tblwhere (c1,c2,c3)< (2,5,1);
78+
select*from tblwhere (c1,c2,c3)< (2,5,1);
7679
DROPTABLE tbl;
7780

7881
CREATETABLEtbl (c1int,c2int, c3int, c4box,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp