diff options
| -rw-r--r-- | src/backend/optimizer/path/indxpath.c | 9 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/createplan.c | 5 | ||||
| -rw-r--r-- | src/test/regress/expected/union.out | 48 | ||||
| -rw-r--r-- | src/test/regress/sql/union.sql | 33 |
4 files changed, 94 insertions, 1 deletions
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 58b8797b738..e97c1636b1a 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -2286,6 +2286,15 @@ match_index_to_operand(Node *operand, int indkey; /* + * Ignore any PlaceHolderVar nodes above the operand. This is needed so + * that we can successfully use expression-index constraints pushed down + * through appendrels (UNION ALL). It's safe because a PlaceHolderVar + * appearing in a relation-scan-level expression is certainly a no-op. + */ + while (operand && IsA(operand, PlaceHolderVar)) + operand = (Node *) ((PlaceHolderVar *) operand)->phexpr; + + /* * Ignore any RelabelType node above the operand. This is needed to be * able to apply indexscanning in binary-compatible-operator cases. Note: * we can assume there is at most one RelabelType node; diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 7a81010e4cf..bf0c6208333 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -2623,8 +2623,11 @@ fix_indexqual_operand(Node *node, IndexOptInfo *index) ListCell *indexpr_item; /* - * Remove any binary-compatible relabeling of the indexkey + * Remove any PlaceHolderVars or binary-compatible relabeling of the + * indexkey (this must match logic in match_index_to_operand()). */ + while (IsA(node, PlaceHolderVar)) + node = (Node *) ((PlaceHolderVar *) node)->phexpr; if (IsA(node, RelabelType)) node = (Node *) ((RelabelType *) node)->arg; diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index 2f8037f9eb0..4fff2297751 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -455,3 +455,51 @@ SELECT '3.4'::numeric UNION SELECT 'foo'; ERROR: invalid input syntax for type numeric: "foo" LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo'; ^ +-- +-- Test that expression-index constraints can be pushed down through +-- UNION or UNION ALL +-- +CREATE TEMP TABLE t1 (a text, b text); +CREATE INDEX t1_ab_idx on t1 ((a || b)); +CREATE TEMP TABLE t2 (ab text primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2" +INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y'); +INSERT INTO t2 VALUES ('ab'), ('xy'); +set enable_seqscan = off; +set enable_indexscan = on; +set enable_bitmapscan = off; +explain (costs off) + SELECT * FROM + (SELECT a || b AS ab FROM t1 + UNION ALL + SELECT * FROM t2) t + WHERE ab = 'ab'; + QUERY PLAN +--------------------------------------------------- + Result + -> Append + -> Index Scan using t1_ab_idx on t1 + Index Cond: ((a || b) = 'ab'::text) + -> Index Scan using t2_pkey on t2 + Index Cond: (ab = 'ab'::text) +(6 rows) + +explain (costs off) + SELECT * FROM + (SELECT a || b AS ab FROM t1 + UNION + SELECT * FROM t2) t + WHERE ab = 'ab'; + QUERY PLAN +--------------------------------------------------- + HashAggregate + -> Append + -> Index Scan using t1_ab_idx on t1 + Index Cond: ((a || b) = 'ab'::text) + -> Index Scan using t2_pkey on t2 + Index Cond: (ab = 'ab'::text) +(6 rows) + +reset enable_seqscan; +reset enable_indexscan; +reset enable_bitmapscan; diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql index daa72c929c0..b5c2128f3f3 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -166,3 +166,36 @@ ORDER BY 1; -- This should fail, but it should produce an error cursor SELECT '3.4'::numeric UNION SELECT 'foo'; + +-- +-- Test that expression-index constraints can be pushed down through +-- UNION or UNION ALL +-- + +CREATE TEMP TABLE t1 (a text, b text); +CREATE INDEX t1_ab_idx on t1 ((a || b)); +CREATE TEMP TABLE t2 (ab text primary key); +INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y'); +INSERT INTO t2 VALUES ('ab'), ('xy'); + +set enable_seqscan = off; +set enable_indexscan = on; +set enable_bitmapscan = off; + +explain (costs off) + SELECT * FROM + (SELECT a || b AS ab FROM t1 + UNION ALL + SELECT * FROM t2) t + WHERE ab = 'ab'; + +explain (costs off) + SELECT * FROM + (SELECT a || b AS ab FROM t1 + UNION + SELECT * FROM t2) t + WHERE ab = 'ab'; + +reset enable_seqscan; +reset enable_indexscan; +reset enable_bitmapscan; |