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

Commit3f26be8

Browse files
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 indexsupport returning the indexed value for index-only scans. If thesame indexed column appears in index columns that behave both ways,check_index_only() supposed that it'd be OK to do an index-only scantesting that column; but that fails if we have to recheck the indexedcondition on one of the columns that doesn't support this.In principle we could make this work by remapping the recheck expressionsto pull the value from a column that does support returning the indexedvalue. 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_onlythat a value is returnable only if all the index columns containing itare returnable, rather than any of them.Per report from David Pereiro Lagares. Back-patch to 9.5 where thepossibility of this situation appeared.Kyotaro HoriguchiDiscussion:https://postgr.es/m/1516210494.1798.16.camel@nlpgo.com
1 parent11e7700 commit3f26be8

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
SELECTcount(*)FROM inettmpWHERE a>='89.225.196.191'::inet;
3030

3131
SELECTcount(*)FROM inettmpWHERE 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+
SELECTcount(*)FROM inettmpWHERE a='89.225.196.191'::inet;
38+
39+
SELECTcount(*)FROM inettmpWHERE a='89.225.196.191'::inet;
40+
41+
DROPINDEX inetidx;
42+
43+
CREATEINDEXON inettmp USING gist (a gist_inet_ops, a inet_ops);
44+
45+
-- likewise here (checks for core planner bug)
46+
EXPLAIN (COSTS OFF)
47+
SELECTcount(*)FROM inettmpWHERE a='89.225.196.191'::inet;
48+
49+
SELECTcount(*)FROM inettmpWHERE 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
boolresult;
17921792
Bitmapset*attrs_used=NULL;
17931793
Bitmapset*index_canreturn_attrs=NULL;
1794+
Bitmapset*index_cannotreturn_attrs=NULL;
17941795
ListCell*lc;
17951796
inti;
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
returnresult;
18591872
}

0 commit comments

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