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

Commitb5febc1

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 parent6452b09 commitb5febc1

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
@@ -1866,6 +1866,7 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index)
18661866
boolresult;
18671867
Bitmapset*attrs_used=NULL;
18681868
Bitmapset*index_canreturn_attrs=NULL;
1869+
Bitmapset*index_cannotreturn_attrs=NULL;
18691870
ListCell*lc;
18701871
inti;
18711872

@@ -1905,7 +1906,11 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index)
19051906

19061907
/*
19071908
* Construct a bitmapset of columns that the index can return back in an
1908-
* index-only scan.
1909+
* index-only scan. If there are multiple index columns containing the
1910+
* same attribute, all of them must be capable of returning the value,
1911+
* since we might recheck operators on any of them. (Potentially we could
1912+
* be smarter about that, but it's such a weird situation that it doesn't
1913+
* seem worth spending a lot of sweat on.)
19091914
*/
19101915
for (i=0;i<index->ncolumns;i++)
19111916
{
@@ -1922,13 +1927,21 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index)
19221927
index_canreturn_attrs=
19231928
bms_add_member(index_canreturn_attrs,
19241929
attno-FirstLowInvalidHeapAttributeNumber);
1930+
else
1931+
index_cannotreturn_attrs=
1932+
bms_add_member(index_cannotreturn_attrs,
1933+
attno-FirstLowInvalidHeapAttributeNumber);
19251934
}
19261935

1936+
index_canreturn_attrs=bms_del_members(index_canreturn_attrs,
1937+
index_cannotreturn_attrs);
1938+
19271939
/* Do we have all the necessary attributes? */
19281940
result=bms_is_subset(attrs_used,index_canreturn_attrs);
19291941

19301942
bms_free(attrs_used);
19311943
bms_free(index_canreturn_attrs);
1944+
bms_free(index_cannotreturn_attrs);
19321945

19331946
returnresult;
19341947
}

0 commit comments

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