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

Commitb28ffd0

Browse files
committed
Fix pushing of index-expression qualifications through UNION ALL.
In commit57664ed, I made the plannerwrap non-simple-variable outputs of appendrel children (IOW, child SELECTsof UNION ALL subqueries) inside PlaceHolderVars, in order to solve someissues with EquivalenceClass processing. However, this means that anyupper-level WHERE clauses mentioning such outputs will now containPlaceHolderVars after they're pushed down into the appendrel child,and that prevents indxpath.c from recognizing that they could be matchedto index expressions. To fix, add explicit stripping of PlaceHolderVarsfrom index operands, same as we have long done for RelabelType nodes.Add a regression test covering both this and the plain-UNION case (whichis a totally different code path, but should also be able to do it).Per bug #6416 from Matteo Beccati. Back-patch to 9.1, same as theprevious change.
1 parented6e054 commitb28ffd0

File tree

4 files changed

+94
-1
lines changed

4 files changed

+94
-1
lines changed

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2580,6 +2580,15 @@ match_index_to_operand(Node *operand,
25802580
{
25812581
intindkey;
25822582

2583+
/*
2584+
* Ignore any PlaceHolderVar nodes above the operand. This is needed so
2585+
* that we can successfully use expression-index constraints pushed down
2586+
* through appendrels (UNION ALL). It's safe because a PlaceHolderVar
2587+
* appearing in a relation-scan-level expression is certainly a no-op.
2588+
*/
2589+
while (operand&&IsA(operand,PlaceHolderVar))
2590+
operand= (Node*) ((PlaceHolderVar*)operand)->phexpr;
2591+
25832592
/*
25842593
* Ignore any RelabelType node above the operand.This is needed to be
25852594
* able to apply indexscanning in binary-compatible-operator cases. Note:

‎src/backend/optimizer/plan/createplan.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2673,8 +2673,11 @@ fix_indexqual_operand(Node *node, IndexOptInfo *index, int indexcol)
26732673
ListCell*indexpr_item;
26742674

26752675
/*
2676-
* Remove any binary-compatible relabeling of the indexkey
2676+
* Remove any PlaceHolderVars or binary-compatible relabeling of the
2677+
* indexkey (this must match logic in match_index_to_operand()).
26772678
*/
2679+
while (IsA(node,PlaceHolderVar))
2680+
node= (Node*) ((PlaceHolderVar*)node)->phexpr;
26782681
if (IsA(node,RelabelType))
26792682
node= (Node*) ((RelabelType*)node)->arg;
26802683

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

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -455,3 +455,51 @@ SELECT '3.4'::numeric UNION SELECT 'foo';
455455
ERROR: invalid input syntax for type numeric: "foo"
456456
LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo';
457457
^
458+
--
459+
-- Test that expression-index constraints can be pushed down through
460+
-- UNION or UNION ALL
461+
--
462+
CREATE TEMP TABLE t1 (a text, b text);
463+
CREATE INDEX t1_ab_idx on t1 ((a || b));
464+
CREATE TEMP TABLE t2 (ab text primary key);
465+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
466+
INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y');
467+
INSERT INTO t2 VALUES ('ab'), ('xy');
468+
set enable_seqscan = off;
469+
set enable_indexscan = on;
470+
set enable_bitmapscan = off;
471+
explain (costs off)
472+
SELECT * FROM
473+
(SELECT a || b AS ab FROM t1
474+
UNION ALL
475+
SELECT * FROM t2) t
476+
WHERE ab = 'ab';
477+
QUERY PLAN
478+
---------------------------------------------------
479+
Result
480+
-> Append
481+
-> Index Scan using t1_ab_idx on t1
482+
Index Cond: ((a || b) = 'ab'::text)
483+
-> Index Only Scan using t2_pkey on t2
484+
Index Cond: (ab = 'ab'::text)
485+
(6 rows)
486+
487+
explain (costs off)
488+
SELECT * FROM
489+
(SELECT a || b AS ab FROM t1
490+
UNION
491+
SELECT * FROM t2) t
492+
WHERE ab = 'ab';
493+
QUERY PLAN
494+
---------------------------------------------------
495+
HashAggregate
496+
-> Append
497+
-> Index Scan using t1_ab_idx on t1
498+
Index Cond: ((a || b) = 'ab'::text)
499+
-> Index Only Scan using t2_pkey on t2
500+
Index Cond: (ab = 'ab'::text)
501+
(6 rows)
502+
503+
reset enable_seqscan;
504+
reset enable_indexscan;
505+
reset enable_bitmapscan;

‎src/test/regress/sql/union.sql

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -166,3 +166,36 @@ ORDER BY 1;
166166

167167
-- This should fail, but it should produce an error cursor
168168
SELECT'3.4'::numericUNIONSELECT'foo';
169+
170+
--
171+
-- Test that expression-index constraints can be pushed down through
172+
-- UNION or UNION ALL
173+
--
174+
175+
CREATE TEMP TABLE t1 (atext, btext);
176+
CREATEINDEXt1_ab_idxon t1 ((a|| b));
177+
CREATE TEMP TABLE t2 (abtextprimary key);
178+
INSERT INTO t1VALUES ('a','b'), ('x','y');
179+
INSERT INTO t2VALUES ('ab'), ('xy');
180+
181+
set enable_seqscan= off;
182+
set enable_indexscan=on;
183+
set enable_bitmapscan= off;
184+
185+
explain (costs off)
186+
SELECT*FROM
187+
(SELECT a|| bAS abFROM t1
188+
UNION ALL
189+
SELECT*FROM t2) t
190+
WHERE ab='ab';
191+
192+
explain (costs off)
193+
SELECT*FROM
194+
(SELECT a|| bAS abFROM t1
195+
UNION
196+
SELECT*FROM t2) t
197+
WHERE ab='ab';
198+
199+
reset enable_seqscan;
200+
reset enable_indexscan;
201+
reset enable_bitmapscan;

0 commit comments

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