8000 Fix IOS planning when only some index columns can return an attribute. · postgres/postgres@3f26be8 · GitHub
[go: up one dir, main page]
Skip to content

Commit 3f26be8

Browse files
tglsfdctglsfdc
committed
Fix IOS planning when only some index columns can return an attribute.
Since 9.5, it's possible that some but not all columns of an index support returning the indexed value for index-only scans. If the same indexed column appears in index columns that behave both ways, check_index_only() supposed that it'd be OK to do an index-only scan testing that column; but that fails if we have to recheck the indexed condition on one of the columns that doesn't support this. In principle we could make this work by remapping the recheck expressions to pull the value from a column that does support returning the indexed value. But such cases are so weird and rare that, at least for now, it doesn't seem worth the trouble. Instead, just teach check_index_only that a value is returnable only if all the index columns containing it are returnable, rather than any of them. Per report from David Pereiro Lagares. Back-patch to 9.5 where the possibility of this situation appeared. Kyotaro Horiguchi Discussion: https://postgr.es/m/1516210494.1798.16.camel@nlpgo.com
1 parent 11e7700 commit 3f26be8

File tree

3 files changed

+71
-1
lines changed

3 files changed

+71
-1
lines changed

‎contrib/btree_gist/expected/inet.out

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,3 +64,42 @@ SELECT count(*) FROM inettmp WHERE a > '89.225.196.191'::inet;
6464
386
6565
(1 row)
6666

67+
VACUUM inettmp;
68+
-- gist_inet_ops lacks a fetch function, so this should not be index-only scan
69+
EXPLAIN (COSTS OFF)
70+
SELECT count(*) FROM inettmp WHERE a = '89.225.196.191'::inet;
71+
QUERY PLAN
72+
--------------------------------------------------------
73+
Aggregate
74+
-> Bitmap Heap Scan on inettmp
75+
Recheck Cond: (a = '89.225.196.191'::inet)
76+
-> Bitmap Index Scan on inetidx
77+
Index Cond: (a = '89.225.196.191'::inet)
78+
(5 rows)
79+
80+
SELECT count(*) FROM inettmp WHERE a = '89.225.196.191'::inet;
81+
count
82+
-------
83+
1
84+
(1 row)
85+
86+
DROP INDEX inetidx;
87+
CREATE INDEX ON inettmp USING gist (a gist_inet_ops, a inet_ops);
88+
-- likewise here (checks for core planner bug)
89+
EXPLAIN (COSTS OFF)
90+
SELECT count(*) FROM inettmp WHERE a = '89.225.196.191'::inet;
91+
QUERY PLAN
92+
--------------------------------------------------------
93+
Aggregate
94+
-> Bitmap Heap Scan on inettmp
95+
Recheck Cond: (a = '89.225.196.191'::inet)
96+
-> Bitmap Index Scan on inettmp_a_a1_idx
97+
Index Cond: (a = '89.225.196.191'::inet)
98+
(5 rows)
99+
100+
SELECT count(*) FROM inettmp WHERE a = '89.225.196.191'::inet;
101+
count
102+
-------
103+
1
104+
(1 row)
105+

‎contrib/btree_gist/sql/inet.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,3 +29,21 @@ SELECT count(*) FROM inettmp WHERE a = '89.225.196.191'::inet;
2929
SELECT count(*) FROM inettmp WHERE a >= '89.225.196.191'::inet;
3030

3131
SELECT count(*) FROM inettmp WHERE a > '89.225.196.191'::inet;
32+
33+
VACUUM inettmp;
34+
35+
-- gist_inet_ops lacks a fetch function, so this should not be index-only scan
36+
EXPLAIN (COSTS OFF)
37+
SELECT count(*) FROM inettmp WHERE a = '89.225.196.191'::inet;
38+
39+
SELECT count(*) FROM inettmp WHERE a = '89.225.196.191'::inet;
40+
41+
DROP INDEX inetidx;
42+
43+
CREATE INDEX ON inettmp USING gist (a gist_inet_ops, a inet_ops);
44+
45+
-- likewise here (checks for core planner bug)
46+
EXPLAIN (COSTS OFF)
47+
SELECT count(*) FROM inettmp WHERE a = '89.225.196.191'::inet;
48+
49+
SELECT count(*) FROM inettmp WHERE a = '89.225.196.191'::inet;

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

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1791,6 +1791,7 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index)
17911791
bool result;
17921792
Bitmapset *attrs_used = NULL;
17931793
Bitmapset *index_canreturn_attrs = NULL;
1794+
Bitmapset *index_cannotreturn_attrs = NULL;
17941795
ListCell *lc;
17951796
int i;
17961797

@@ -1830,7 +1831,11 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index)
18301831

18311832
/*
18321833
* Construct a bitmapset of columns that the index can return back in an
1833-
* index-only scan.
1834+
* index-only scan. If there are multiple index columns containing the
1835+
* same attribute, all of them must be capable of returning the value,
1836+
* since we might recheck operators on any of them. (Potentially we could
1837+
* be smarter about that, but it's such a weird situation that it doesn't
1838+
* seem worth spending a lot of sweat on.)
18341839
*/
18351840
for (i = 0; i < index->ncolumns; i++)
18361841
{
@@ -1847,13 +1852,21 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index)
18471852
index_canreturn_attrs =
18481853
bms_add_member(index_canreturn_attrs,
18491854
attno - FirstLowInvalidHeapAttributeNumber);
1855+
else
1856+
index_cannotreturn_attrs =
1857+
bms_add_member(index_cannotreturn_attrs,
1858+
attno - FirstLowInvalidHeapAttributeNumber);
18501859
}
18511860

1861+
index_canreturn_attrs = bms_del_members(index_canreturn_attrs,
1862+
index_cannotreturn_attrs);
1863+
18521864
/* Do we have all the necessary attributes? */
18531865
result = bms_is_subset(attrs_used, index_canreturn_attrs);
18541866

18551867
bms_free(attrs_used);
18561868
bms_free(index_canreturn_attrs);
1869+
bms_free(index_cannotreturn_attrs);
18571870

18581871
return result;
18591872
}

0 commit comments

Comments
 (0)
0