On 08.10.2018 00:16, David Rowley wrote:
On 5 October 2018 at 04:45, Konstantin Knizhnik
<k.knizh...@postgrespro.ru> wrote:
Will the following test be enough:
-- check that columns for parent table are correctly mapped to child
partition of their order doesn't match
create table paren (a int, b text) partition by range(a);
create table child_1 partition of paren for values from (0) to (10);
create table child_2 (b text, a int);
alter table paren attach partition child_2 for values from (10) to (20);
insert into paren values (generate_series(0,19), generate_series(100,119));
explain (costs off) select * from paren where a between 0 and 9;
explain (costs off) select * from paren where a between 10 and 20;
explain (costs off) select * from paren where a >= 5;
explain (costs off) select * from paren where a <= 15;
select count(*) from paren where a >= 5;
select count(*) from paren where a < 15;
drop table paren cascade;
I started looking at this to see if this test would cause a crash with
the original code, but it does not. The closest I can get is:
drop table parent;
create table parent (a bytea, b int) partition by range(a);
create table child_1 (b int, a bytea);
alter table parent attach partition child_1 for values from ('a') to ('z');
explain (costs off) select * from parent where b = 1;
But despite the varattnos of the bytea and int accidentally matching,
there's no crash due to the way operator_predicate_proof() requires
more than just the varno and varattno to match. It requires the Vars
to be equal(), which includes vartype, and those are not the same. So
the proof just fails.
In short, probably this test is doing nothing in addition to what
various other tests are doing. So given the test is unable to crash
the unfixed code, then I think it's likely not a worthwhile test to
add.
I wrote:
create table listp (a int, b int) partition by list(a);
create table listp1 partition of listp for values in(1);
create index listp_a_b_idx on listp (a,b);
and a query:
select * from listp where a = 1 order by b;
if we remove the "a = 1" qual, then listp_a_b_idx can't be used.
I had a look at what allows this query still to use the index and it's
down to pathkey_is_redundant() returning true because there's still an
equivalence class containing {a,1}. I don't quite see any reason why
it would not be okay to rely on that working, but that only works for
pathkeys. If you have a case like:
set max_parallel_workers_per_gather=0;
create table listp (a int, b int) partition by list(a);
create table listp1 partition of listp for values in(1);
insert into listp select 1,x from generate_Series(1,1000000) x;
create index listp_a_b_idx on listp (a,b);
vacuum analyze listp;
explain analyze select * from listp where a = 1 and b = 1;
the "a = 1" will be dropped and the index on (a,b) does not get used.
Patched results in:
postgres=# explain analyze select * from listp where a = 1 and b = 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Append (cost=0.00..16925.01 rows=1 width=8) (actual
time=0.019..169.231 rows=1 loops=1)
-> Seq Scan on listp1 (cost=0.00..16925.00 rows=1 width=8)
(actual time=0.017..169.228 rows=1 loops=1)
Filter: (b = 1)
Rows Removed by Filter: 999999
Planning Time: 0.351 ms
Execution Time: 169.257 ms
(6 rows)
Whereas unpatched gets:
postgres=# explain analyze select * from listp where a = 1 and b = 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.42..4.45 rows=1 width=8) (actual time=0.657..0.660
rows=1 loops=1)
-> Index Only Scan using listp1_a_b_idx on listp1
(cost=0.42..4.44 rows=1 width=8) (actual time=0.653..0.655 rows=1
loops=1)
Index Cond: ((a = 1) AND (b = 1))
Heap Fetches: 0
Planning Time: 32.303 ms
Execution Time: 0.826 ms
(6 rows)
so I was probably wrong about suggesting set_append_rel_size() as a
good place to remove these quals. It should perhaps be done later, or
maybe we can add some sort of marker to the qual to say it does not
need to be enforced during execution. Probably the former would be
best as we don't want to show these in EXPLAIN.
Well, I made a different conclusion from this problem (inability use of
compound index because of redundant qual elimination).
Is it really good idea to define compound index with first key equal to
partitioning key?
Restriction on this key in any case will lead to partition pruning. We
do no need index for it...
In your case if we create index listp_b_idx:
create index listp_b_idx on listp (b);
then right plan we be generated:
Append (cost=0.42..8.45 rows=1 width=8) (actual time=0.046..0.047
rows=1 loops=1)
-> Index Scan using listp1_b_idx on listp1 (cost=0.42..8.44 rows=1
width=8) (actual time=0.046..0.046 rows=1 loops=1)
Index Cond: (b = 1)
and it is definitely more efficient than original plan with unpacked
Postgres.
Append (cost=0.42..4.45 rows=1 width=8) (actual time=0.657..0.660
rows=1 loops=1)
-> Index Only Scan using listp1_a_b_idx on listp1
(cost=0.42..4.44 rows=1 width=8) (actual time=0.653..0.655 rows=1
loops=1)
Index Cond: ((a = 1) AND (b = 1))
Heap Fetches: 0
In any case, I have attached yet another version of the patch: it is my
original patch with removed predicate test proof logic.
Unlike your patch, it works also for CHECK constraints, not only for
standard partitions.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 21a2ef5..ea223bb 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -631,12 +631,12 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- Nu
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NULL))
(3 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL and c3 is not null; -- NullTest
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NOT NULL))
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c3 IS NOT NULL))
(3 rows)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 88c4cb4..ada5934 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -298,7 +298,7 @@ RESET enable_nestloop;
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL and c3 is not null; -- NullTest
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 5f74d3b..9694303 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -345,6 +345,7 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
switch (rel->rtekind)
{
case RTE_RELATION:
+ remove_restrictions_implied_by_constraints(root, rel, rte);
if (rte->relkind == RELKIND_FOREIGN_TABLE)
{
/* Foreign table */
@@ -1149,6 +1150,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
set_dummy_rel_pathlist(childrel);
continue;
}
+ remove_restrictions_implied_by_constraints(root, childrel, childRTE);
/* CE failed, so finish copying/modifying join quals. */
childrel->joininfo = (List *)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 8369e3a..32409dd 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -65,8 +65,10 @@ static bool infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
List *idxExprs);
static int32 get_rel_data_width(Relation rel, int32 *attr_widths);
static List *get_relation_constraints(PlannerInfo *root,
- Oid relationObjectId, RelOptInfo *rel,
- bool include_notnull);
+ Oid relationObjectId, RelOptInfo *rel,
+ bool include_notnull,
+ bool include_partition_quals
+ );
static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
Relation heapRelation);
static List *get_relation_statistics(RelOptInfo *rel, Relation relation);
@@ -1174,7 +1176,8 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
static List *
get_relation_constraints(PlannerInfo *root,
Oid relationObjectId, RelOptInfo *rel,
- bool include_notnull)
+ bool include_notnull,
+ bool include_partition_quals)
{
List *result = NIL;
Index varno = rel->relid;
@@ -1272,7 +1275,7 @@ get_relation_constraints(PlannerInfo *root,
* descriptor, instead of constraint exclusion which is driven by the
* individual partition's partition constraint.
*/
- if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
+ if (enable_partition_pruning && include_partition_quals)
{
List *pcqual = RelationGetPartitionQual(relation);
@@ -1495,7 +1498,7 @@ relation_excluded_by_constraints(PlannerInfo *root,
* OK to fetch the constraint expressions. Include "col IS NOT NULL"
* expressions for attnotnull columns, in case we can refute those.
*/
- constraint_pred = get_relation_constraints(root, rte->relid, rel, true);
+ constraint_pred = get_relation_constraints(root, rte->relid, rel, true, root->parse->commandType != CMD_SELECT);
/*
* We do not currently enforce that CHECK constraints contain only
@@ -1532,6 +1535,51 @@ relation_excluded_by_constraints(PlannerInfo *root,
return false;
}
+/*
+ * Remove from restrictions list items implied by table constraints
+ */
+void remove_restrictions_implied_by_constraints(PlannerInfo *root,
+ RelOptInfo *rel, RangeTblEntry *rte)
+{
+ List *constraint_pred;
+ List *safe_constraints = NIL;
+ List *safe_restrictions = NIL;
+ ListCell *lc;
+
+ if (rte->rtekind != RTE_RELATION || rte->inh)
+ return;
+
+ /*
+ * OK to fetch the constraint expressions. Include "col IS NOT NULL"
+ * expressions for attnotnull columns, in case we can refute those.
+ */
+ constraint_pred = get_relation_constraints(root, rte->relid, rel, true, true);
+
+ /*
+ * We do not currently enforce that CHECK constraints contain only
+ * immutable functions, so it's necessary to check here. We daren't draw
+ * conclusions from plan-time evaluation of non-immutable functions. Since
+ * they're ANDed, we can just ignore any mutable constraints in the list,
+ * and reason about the rest.
+ */
+ foreach(lc, constraint_pred)
+ {
+ Node *pred = (Node*) lfirst(lc);
+
+ if (!contain_mutable_functions(pred))
+ safe_constraints = lappend(safe_constraints, pred);
+ }
+
+ foreach(lc, rel->baserestrictinfo)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+ if (!predicate_implied_by(list_make1(rinfo->clause), safe_constraints, false)) {
+ safe_restrictions = lappend(safe_restrictions, rinfo);
+ }
+ }
+ rel->baserestrictinfo = safe_restrictions;
+}
+
/*
* build_physical_tlist
diff --git a/src/include/optimizer/plancat.h b/src/include/optimizer/plancat.h
index 7d53cbb..12b3c55 100644
--- a/src/include/optimizer/plancat.h
+++ b/src/include/optimizer/plancat.h
@@ -38,6 +38,9 @@ extern int32 get_relation_data_width(Oid relid, int32 *attr_widths);
extern bool relation_excluded_by_constraints(PlannerInfo *root,
RelOptInfo *rel, RangeTblEntry *rte);
+extern void remove_restrictions_implied_by_constraints(PlannerInfo *root,
+ RelOptInfo *rel, RangeTblEntry *rte);
+
extern List *build_physical_tlist(PlannerInfo *root, RelOptInfo *rel);
extern bool has_unique_index(RelOptInfo *rel, AttrNumber attno);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 4f29d9f..67d7a41 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1772,30 +1772,26 @@ explain (costs off) select * from list_parted where a is not null;
---------------------------------
Append
-> Seq Scan on part_ab_cd
- Filter: (a IS NOT NULL)
-> Seq Scan on part_ef_gh
- Filter: (a IS NOT NULL)
-> Seq Scan on part_null_xy
Filter: (a IS NOT NULL)
-(7 rows)
+(5 rows)
explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
QUERY PLAN
----------------------------------------------------------
Append
-> Seq Scan on part_ab_cd
- Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-> Seq Scan on part_ef_gh
Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-(5 rows)
+(4 rows)
explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
- QUERY PLAN
----------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on part_ab_cd
- Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-(3 rows)
+(2 rows)
explain (costs off) select * from list_parted where a = 'ab';
QUERY PLAN
@@ -1848,30 +1844,25 @@ explain (costs off) select * from range_list_parted where a = 5;
(5 rows)
explain (costs off) select * from range_list_parted where b = 'ab';
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+----------------------------------
Append
-> Seq Scan on part_1_10_ab
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_10_20_ab
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_21_30_ab
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_40_inf_ab
- Filter: (b = 'ab'::bpchar)
-(9 rows)
+(5 rows)
explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab');
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------
Append
-> Seq Scan on part_1_10_ab
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
+ Filter: (a >= 3)
-> Seq Scan on part_10_20_ab
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-> Seq Scan on part_21_30_ab
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-(7 rows)
+ Filter: (a <= 23)
+(6 rows)
/* Should select no rows because range partition key cannot be null */
explain (costs off) select * from range_list_parted where a is null;
@@ -1887,44 +1878,34 @@ explain (costs off) select * from range_list_parted where b is null;
------------------------------------
Append
-> Seq Scan on part_40_inf_null
- Filter: (b IS NULL)
-(3 rows)
+(2 rows)
explain (costs off) select * from range_list_parted where a is not null and a < 67;
- QUERY PLAN
-------------------------------------------------
+ QUERY PLAN
+------------------------------------
Append
-> Seq Scan on part_1_10_ab
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_1_10_cd
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_10_20_ab
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_10_20_cd
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_21_30_ab
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_21_30_cd
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_40_inf_ab
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Filter: (a < 67)
-> Seq Scan on part_40_inf_cd
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Filter: (a < 67)
-> Seq Scan on part_40_inf_null
- Filter: ((a IS NOT NULL) AND (a < 67))
-(19 rows)
+ Filter: (a < 67)
+(13 rows)
explain (costs off) select * from range_list_parted where a >= 30;
QUERY PLAN
------------------------------------
Append
-> Seq Scan on part_40_inf_ab
- Filter: (a >= 30)
-> Seq Scan on part_40_inf_cd
- Filter: (a >= 30)
-> Seq Scan on part_40_inf_null
- Filter: (a >= 30)
-(7 rows)
+(4 rows)
drop table list_parted;
drop table range_list_parted;
@@ -1965,7 +1946,7 @@ explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scan
-> Seq Scan on mcrparted1
Filter: ((a = 10) AND (abs(b) = 5))
-> Seq Scan on mcrparted2
- Filter: ((a = 10) AND (abs(b) = 5))
+ Filter: (abs(b) = 5)
-> Seq Scan on mcrparted_def
Filter: ((a = 10) AND (abs(b) = 5))
(7 rows)
@@ -1997,25 +1978,20 @@ explain (costs off) select * from mcrparted where a > -1; -- scans all partition
-> Seq Scan on mcrparted0
Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted1
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted2
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted3
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted4
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted5
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted_def
Filter: (a > '-1'::integer)
-(15 rows)
+(10 rows)
explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------
Append
-> Seq Scan on mcrparted4
- Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+ Filter: ((c > 10) AND (abs(b) = 10))
(3 rows)
explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def
@@ -2025,7 +2001,7 @@ explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mc
-> Seq Scan on mcrparted3
Filter: ((c > 20) AND (a = 20))
-> Seq Scan on mcrparted4
- Filter: ((c > 20) AND (a = 20))
+ Filter: (c > 20)
-> Seq Scan on mcrparted5
Filter: ((c > 20) AND (a = 20))
-> Seq Scan on mcrparted_def
@@ -2048,13 +2024,13 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
-> Merge Append
Sort Key: parted_minmax1.a
-> Index Only Scan using parted_minmax1i on parted_minmax1
- Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
+ Index Cond: (b = '12345'::text)
InitPlan 2 (returns $1)
-> Limit
-> Merge Append
Sort Key: parted_minmax1_1.a DESC
-> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax1_1
- Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
+ Index Cond: (b = '12345'::text)
(13 rows)
select min(a), max(a) from parted_minmax where b = '12345';
diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index 6bc1068..41f3ac5 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -732,7 +732,6 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOI
Hash Cond: (pagg_tab1_p1.x = y)
Filter: ((pagg_tab1_p1.x > 5) OR (y < 20))
-> Seq Scan on pagg_tab1_p1
- Filter: (x < 20)
-> Hash
-> Result
One-Time Filter: false
@@ -742,11 +741,10 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOI
Hash Cond: (pagg_tab1_p2.x = pagg_tab2_p2.y)
Filter: ((pagg_tab1_p2.x > 5) OR (pagg_tab2_p2.y < 20))
-> Seq Scan on pagg_tab1_p2
- Filter: (x < 20)
-> Hash
-> Seq Scan on pagg_tab2_p2
Filter: (y > 10)
-(23 rows)
+(21 rows)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
x | y | count
@@ -778,7 +776,6 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOI
Hash Cond: (pagg_tab1_p1.x = y)
Filter: ((pagg_tab1_p1.x > 5) OR (y < 20))
-> Seq Scan on pagg_tab1_p1
- Filter: (x < 20)
-> Hash
-> Result
One-Time Filter: false
@@ -788,7 +785,6 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOI
Hash Cond: (pagg_tab1_p2.x = pagg_tab2_p2.y)
Filter: ((pagg_tab1_p2.x > 5) OR (pagg_tab2_p2.y < 20))
-> Seq Scan on pagg_tab1_p2
- Filter: (x < 20)
-> Hash
-> Seq Scan on pagg_tab2_p2
Filter: (y > 10)
@@ -798,11 +794,10 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOI
Hash Cond: (pagg_tab2_p3.y = x)
Filter: ((x > 5) OR (pagg_tab2_p3.y < 20))
-> Seq Scan on pagg_tab2_p3
- Filter: (y > 10)
-> Hash
-> Result
One-Time Filter: false
-(35 rows)
+(32 rows)
SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2;
x | y | count
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 3ba3aaf..169f431 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -216,7 +216,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JO
-> Hash Left Join
Hash Cond: (prt1_p1.a = b)
-> Seq Scan on prt1_p1
- Filter: ((a < 450) AND (b = 0))
+ Filter: (b = 0)
-> Hash
-> Result
One-Time Filter: false
@@ -254,7 +254,6 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JO
Hash Cond: (prt1_p1.a = b)
Filter: ((prt1_p1.b = 0) OR (a = 0))
-> Seq Scan on prt1_p1
- Filter: (a < 450)
-> Hash
-> Result
One-Time Filter: false
@@ -270,11 +269,10 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JO
Hash Cond: (prt2_p3.b = a)
Filter: ((b = 0) OR (prt2_p3.a = 0))
-> Seq Scan on prt2_p3
- Filter: (b > 250)
-> Hash
-> Result
One-Time Filter: false
-(27 rows)
+(25 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b;
a | c | b | c
@@ -1006,7 +1004,7 @@ SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
-> Sort
Sort Key: prt1_p1.a
-> Seq Scan on prt1_p1
- Filter: ((a < 450) AND (b = 0))
+ Filter: (b = 0)
-> Sort
Sort Key: b
-> Result
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 24313e8..d99ab9d 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -25,22 +25,20 @@ explain (costs off) select * from lp where a > 'a' and a < 'd';
-----------------------------------------------------------
Append
-> Seq Scan on lp_bc
- Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
-> Seq Scan on lp_default
Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
-(5 rows)
+(4 rows)
explain (costs off) select * from lp where a > 'a' and a <= 'd';
QUERY PLAN
------------------------------------------------------------
Append
-> Seq Scan on lp_ad
- Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
+ Filter: (a > 'a'::bpchar)
-> Seq Scan on lp_bc
- Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
-> Seq Scan on lp_default
Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
-(7 rows)
+(6 rows)
explain (costs off) select * from lp where a = 'a';
QUERY PLAN
@@ -59,28 +57,22 @@ explain (costs off) select * from lp where 'a' = a; /* commuted */
(3 rows)
explain (costs off) select * from lp where a is not null;
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on lp_ad
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_bc
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_ef
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_g
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_default
- Filter: (a IS NOT NULL)
-(11 rows)
+(6 rows)
explain (costs off) select * from lp where a is null;
- QUERY PLAN
------------------------------
+ QUERY PLAN
+---------------------------
Append
-> Seq Scan on lp_null
- Filter: (a IS NULL)
-(3 rows)
+(2 rows)
explain (costs off) select * from lp where a = 'a' or a = 'c';
QUERY PLAN
@@ -93,56 +85,44 @@ explain (costs off) select * from lp where a = 'a' or a = 'c';
(5 rows)
explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------
Append
-> Seq Scan on lp_ad
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
-> Seq Scan on lp_bc
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
(5 rows)
explain (costs off) select * from lp where a <> 'g';
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on lp_ad
- Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_bc
- Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_ef
- Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_default
- Filter: (a <> 'g'::bpchar)
-(9 rows)
+(5 rows)
explain (costs off) select * from lp where a <> 'a' and a <> 'd';
- QUERY PLAN
--------------------------------------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on lp_bc
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_ef
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_g
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_default
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-(9 rows)
+(5 rows)
explain (costs off) select * from lp where a not in ('a', 'd');
- QUERY PLAN
-------------------------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on lp_bc
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_ef
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_g
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_default
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-(9 rows)
+(5 rows)
-- collation matches the partitioning collation, pruning works
create table coll_pruning (a text collate "C") partition by list (a);
@@ -150,12 +130,11 @@ create table coll_pruning_a partition of coll_pruning for values in ('a');
create table coll_pruning_b partition of coll_pruning for values in ('b');
create table coll_pruning_def partition of coll_pruning default;
explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C";
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+----------------------------------
Append
-> Seq Scan on coll_pruning_a
- Filter: (a = 'a'::text COLLATE "C")
-(3 rows)
+(2 rows)
-- collation doesn't match the partitioning collation, no pruning occurs
explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX";
@@ -192,30 +171,27 @@ create table rlp5 partition of rlp for values from (31) to (maxvalue) partition
create table rlp5_default partition of rlp5 default;
create table rlp5_1 partition of rlp5 for values from (31) to (40);
explain (costs off) select * from rlp where a < 1;
- QUERY PLAN
--------------------------
+ QUERY PLAN
+------------------------
Append
-> Seq Scan on rlp1
- Filter: (a < 1)
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where 1 > a; /* commuted */
- QUERY PLAN
--------------------------
+ QUERY PLAN
+------------------------
Append
-> Seq Scan on rlp1
- Filter: (1 > a)
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where a <= 1;
QUERY PLAN
--------------------------
Append
-> Seq Scan on rlp1
- Filter: (a <= 1)
-> Seq Scan on rlp2
Filter: (a <= 1)
-(5 rows)
+(4 rows)
explain (costs off) select * from rlp where a = 1;
QUERY PLAN
@@ -274,65 +250,47 @@ explain (costs off) select * from rlp where a <= 10;
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a <= 10)
-> Seq Scan on rlp2
- Filter: (a <= 10)
-> Seq Scan on rlp_default_10
- Filter: (a <= 10)
-> Seq Scan on rlp_default_default
Filter: (a <= 10)
-(9 rows)
+(6 rows)
explain (costs off) select * from rlp where a > 10;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp3abcd
- Filter: (a > 10)
-> Seq Scan on rlp3efgh
- Filter: (a > 10)
-> Seq Scan on rlp3nullxy
- Filter: (a > 10)
-> Seq Scan on rlp3_default
- Filter: (a > 10)
-> Seq Scan on rlp4_1
- Filter: (a > 10)
-> Seq Scan on rlp4_2
- Filter: (a > 10)
-> Seq Scan on rlp4_default
- Filter: (a > 10)
-> Seq Scan on rlp5_1
- Filter: (a > 10)
-> Seq Scan on rlp5_default
- Filter: (a > 10)
-> Seq Scan on rlp_default_30
- Filter: (a > 10)
-> Seq Scan on rlp_default_default
Filter: (a > 10)
-(23 rows)
+(13 rows)
explain (costs off) select * from rlp where a < 15;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a < 15)
-> Seq Scan on rlp2
- Filter: (a < 15)
-> Seq Scan on rlp_default_10
- Filter: (a < 15)
-> Seq Scan on rlp_default_default
Filter: (a < 15)
-(9 rows)
+(6 rows)
explain (costs off) select * from rlp where a <= 15;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a <= 15)
-> Seq Scan on rlp2
- Filter: (a <= 15)
-> Seq Scan on rlp3abcd
Filter: (a <= 15)
-> Seq Scan on rlp3efgh
@@ -342,10 +300,9 @@ explain (costs off) select * from rlp where a <= 15;
-> Seq Scan on rlp3_default
Filter: (a <= 15)
-> Seq Scan on rlp_default_10
- Filter: (a <= 15)
-> Seq Scan on rlp_default_default
Filter: (a <= 15)
-(17 rows)
+(14 rows)
explain (costs off) select * from rlp where a > 15 and b = 'ab';
QUERY PLAN
@@ -354,17 +311,17 @@ explain (costs off) select * from rlp where a > 15 and b = 'ab';
-> Seq Scan on rlp3abcd
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp4_1
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp4_2
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp4_default
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp5_1
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp5_default
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_30
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_default
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
(17 rows)
@@ -422,13 +379,13 @@ explain (costs off) select * from rlp where a = 16 and b is not null;
------------------------------------------------
Append
-> Seq Scan on rlp3abcd
- Filter: ((b IS NOT NULL) AND (a = 16))
+ Filter: (a = 16)
-> Seq Scan on rlp3efgh
- Filter: ((b IS NOT NULL) AND (a = 16))
+ Filter: (a = 16)
-> Seq Scan on rlp3nullxy
Filter: ((b IS NOT NULL) AND (a = 16))
-> Seq Scan on rlp3_default
- Filter: ((b IS NOT NULL) AND (a = 16))
+ Filter: (a = 16)
(9 rows)
explain (costs off) select * from rlp where a is null;
@@ -436,96 +393,67 @@ explain (costs off) select * from rlp where a is null;
------------------------------------
Append
-> Seq Scan on rlp_default_null
- Filter: (a IS NULL)
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where a is not null;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp2
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3abcd
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3efgh
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3nullxy
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3_default
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_1
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_2
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_default
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp5_1
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp5_default
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_10
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_30
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_default
- Filter: (a IS NOT NULL)
-(29 rows)
+(15 rows)
explain (costs off) select * from rlp where a > 30;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp5_1
- Filter: (a > 30)
-> Seq Scan on rlp5_default
- Filter: (a > 30)
-> Seq Scan on rlp_default_default
Filter: (a > 30)
-(7 rows)
+(5 rows)
explain (costs off) select * from rlp where a = 30; /* only default is scanned */
QUERY PLAN
----------------------------------
Append
-> Seq Scan on rlp_default_30
- Filter: (a = 30)
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where a <= 31;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a <= 31)
-> Seq Scan on rlp2
- Filter: (a <= 31)
-> Seq Scan on rlp3abcd
- Filter: (a <= 31)
-> Seq Scan on rlp3efgh
- Filter: (a <= 31)
-> Seq Scan on rlp3nullxy
- Filter: (a <= 31)
-> Seq Scan on rlp3_default
- Filter: (a <= 31)
-> Seq Scan on rlp4_1
- Filter: (a <= 31)
-> Seq Scan on rlp4_2
- Filter: (a <= 31)
-> Seq Scan on rlp4_default
- Filter: (a <= 31)
-> Seq Scan on rlp5_1
Filter: (a <= 31)
-> Seq Scan on rlp5_default
Filter: (a <= 31)
-> Seq Scan on rlp_default_10
- Filter: (a <= 31)
-> Seq Scan on rlp_default_30
- Filter: (a <= 31)
-> Seq Scan on rlp_default_default
Filter: (a <= 31)
-(29 rows)
+(18 rows)
explain (costs off) select * from rlp where a = 1 or a = 7;
QUERY PLAN
@@ -570,9 +498,9 @@ explain (costs off) select * from rlp where a > 20 and a < 27;
-----------------------------------------
Append
-> Seq Scan on rlp4_1
- Filter: ((a > 20) AND (a < 27))
+ Filter: (a > 20)
-> Seq Scan on rlp4_2
- Filter: ((a > 20) AND (a < 27))
+ Filter: (a < 27)
-> Seq Scan on rlp4_default
Filter: ((a > 20) AND (a < 27))
-> Seq Scan on rlp_default_default
@@ -594,51 +522,37 @@ explain (costs off) select * from rlp where a >= 29;
-> Seq Scan on rlp4_default
Filter: (a >= 29)
-> Seq Scan on rlp5_1
- Filter: (a >= 29)
-> Seq Scan on rlp5_default
- Filter: (a >= 29)
-> Seq Scan on rlp_default_30
- Filter: (a >= 29)
-> Seq Scan on rlp_default_default
Filter: (a >= 29)
-(11 rows)
+(8 rows)
-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+----------------------------------
Append
-> Seq Scan on rlp_default_10
- Filter: ((a > 1) AND (a = 10))
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
QUERY PLAN
-----------------------------------------
Append
-> Seq Scan on rlp3abcd
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3efgh
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3nullxy
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3_default
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_1
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_2
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_default
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp5_1
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp5_default
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp_default_30
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp_default_default
Filter: ((a > 1) AND (a >= 15))
-(23 rows)
+(13 rows)
explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
QUERY PLAN
@@ -725,28 +639,23 @@ explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
-> Seq Scan on mc3p1
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p2
- Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p3
- Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p4
- Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
+ Filter: (abs(b) <= 35)
-> Seq Scan on mc3p_default
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-(11 rows)
+(9 rows)
explain (costs off) select * from mc3p where a > 10;
QUERY PLAN
--------------------------------
Append
-> Seq Scan on mc3p5
- Filter: (a > 10)
-> Seq Scan on mc3p6
- Filter: (a > 10)
-> Seq Scan on mc3p7
- Filter: (a > 10)
-> Seq Scan on mc3p_default
Filter: (a > 10)
-(9 rows)
+(6 rows)
explain (costs off) select * from mc3p where a >= 10;
QUERY PLAN
@@ -755,43 +664,36 @@ explain (costs off) select * from mc3p where a >= 10;
-> Seq Scan on mc3p1
Filter: (a >= 10)
-> Seq Scan on mc3p2
- Filter: (a >= 10)
-> Seq Scan on mc3p3
- Filter: (a >= 10)
-> Seq Scan on mc3p4
- Filter: (a >= 10)
-> Seq Scan on mc3p5
- Filter: (a >= 10)
-> Seq Scan on mc3p6
- Filter: (a >= 10)
-> Seq Scan on mc3p7
- Filter: (a >= 10)
-> Seq Scan on mc3p_default
Filter: (a >= 10)
-(17 rows)
+(11 rows)
explain (costs off) select * from mc3p where a < 10;
QUERY PLAN
--------------------------------
Append
-> Seq Scan on mc3p0
- Filter: (a < 10)
-> Seq Scan on mc3p1
Filter: (a < 10)
-> Seq Scan on mc3p_default
Filter: (a < 10)
-(7 rows)
+(6 rows)
explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10;
QUERY PLAN
-----------------------------------------------
Append
-> Seq Scan on mc3p0
- Filter: ((a <= 10) AND (abs(b) < 10))
+ Filter: (abs(b) < 10)
-> Seq Scan on mc3p1
- Filter: ((a <= 10) AND (abs(b) < 10))
+ Filter: (abs(b) < 10)
-> Seq Scan on mc3p2
- Filter: ((a <= 10) AND (abs(b) < 10))
+ Filter: (abs(b) < 10)
-> Seq Scan on mc3p_default
Filter: ((a <= 10) AND (abs(b) < 10))
(9 rows)
@@ -805,11 +707,11 @@ explain (costs off) select * from mc3p where a = 11 and abs(b) = 0;
(3 rows)
explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Append
-> Seq Scan on mc3p6
- Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10))
+ Filter: ((c = 100) AND (abs(b) = 10))
(3 rows)
explain (costs off) select * from mc3p where a > 20;
@@ -829,12 +731,10 @@ explain (costs off) select * from mc3p where a >= 20;
-> Seq Scan on mc3p5
Filter: (a >= 20)
-> Seq Scan on mc3p6
- Filter: (a >= 20)
-> Seq Scan on mc3p7
- Filter: (a >= 20)
-> Seq Scan on mc3p_default
Filter: (a >= 20)
-(9 rows)
+(7 rows)
explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20);
QUERY PLAN
@@ -871,7 +771,6 @@ explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or
-------------------------------------------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on mc3p0
- Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p1
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p2
@@ -880,7 +779,7 @@ explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p_default
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-(11 rows)
+(10 rows)
explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
QUERY PLAN
@@ -917,12 +816,11 @@ explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 a
-> Seq Scan on mc3p2
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p3
- Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p4
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p_default
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-(13 rows)
+(12 rows)
explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9);
QUERY PLAN
@@ -952,22 +850,18 @@ explain (costs off) select * from mc2p where a < 2;
--------------------------------
Append
-> Seq Scan on mc2p0
- Filter: (a < 2)
-> Seq Scan on mc2p1
- Filter: (a < 2)
-> Seq Scan on mc2p2
- Filter: (a < 2)
-> Seq Scan on mc2p_default
Filter: (a < 2)
-(9 rows)
+(6 rows)
explain (costs off) select * from mc2p where a = 2 and b < 1;
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+-------------------------
Append
-> Seq Scan on mc2p3
- Filter: ((b < 1) AND (a = 2))
-(3 rows)
+(2 rows)
explain (costs off) select * from mc2p where a > 1;
QUERY PLAN
@@ -976,14 +870,11 @@ explain (costs off) select * from mc2p where a > 1;
-> Seq Scan on mc2p2
Filter: (a > 1)
-> Seq Scan on mc2p3
- Filter: (a > 1)
-> Seq Scan on mc2p4
- Filter: (a > 1)
-> Seq Scan on mc2p5
- Filter: (a > 1)
-> Seq Scan on mc2p_default
Filter: (a > 1)
-(11 rows)
+(8 rows)
explain (costs off) select * from mc2p where a = 1 and b > 1;
QUERY PLAN
@@ -1054,16 +945,14 @@ explain (costs off) select * from boolpart where a = false;
------------------------------
Append
-> Seq Scan on boolpart_f
- Filter: (NOT a)
-(3 rows)
+(2 rows)
explain (costs off) select * from boolpart where not a = false;
QUERY PLAN
------------------------------
Append
-> Seq Scan on boolpart_t
- Filter: a
-(3 rows)
+(2 rows)
explain (costs off) select * from boolpart where a is true or a is not true;
QUERY PLAN
@@ -1208,7 +1097,6 @@ explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2
Nested Loop
-> Append
-> Seq Scan on mc2p1 t1
- Filter: (a = 1)
-> Seq Scan on mc2p2 t1_1
Filter: (a = 1)
-> Seq Scan on mc2p_default t1_2
@@ -1233,7 +1121,7 @@ explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2
Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p_default t2_8
Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
-(28 rows)
+(27 rows)
-- pruning should work fine, because values for a prefix of keys (a, b) are
-- available
@@ -1243,7 +1131,6 @@ explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2
Nested Loop
-> Append
-> Seq Scan on mc2p1 t1
- Filter: (a = 1)
-> Seq Scan on mc2p2 t1_1
Filter: (a = 1)
-> Seq Scan on mc2p_default t1_2
@@ -1256,7 +1143,7 @@ explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2
Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p_default t2_2
Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
-(16 rows)
+(15 rows)
-- also here, because values for all keys are provided
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
@@ -1269,12 +1156,11 @@ explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2
Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1))
-> Append
-> Seq Scan on mc2p1 t1
- Filter: (a = 1)
-> Seq Scan on mc2p2 t1_1
Filter: (a = 1)
-> Seq Scan on mc2p_default t1_2
Filter: (a = 1)
-(12 rows)
+(11 rows)
--
-- pruning with clauses containing <> operator
@@ -1289,24 +1175,21 @@ explain (costs off) select * from rp where a <> 1;
--------------------------
Append
-> Seq Scan on rp0
- Filter: (a <> 1)
-> Seq Scan on rp1
Filter: (a <> 1)
-> Seq Scan on rp2
- Filter: (a <> 1)
-(7 rows)
+(5 rows)
explain (costs off) select * from rp where a <> 1 and a <> 2;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+--------------------------
Append
-> Seq Scan on rp0
- Filter: ((a <> 1) AND (a <> 2))
-> Seq Scan on rp1
- Filter: ((a <> 1) AND (a <> 2))
+ Filter: (a <> 1)
-> Seq Scan on rp2
- Filter: ((a <> 1) AND (a <> 2))
-(7 rows)
+ Filter: (a <> 2)
+(6 rows)
-- null partition should be eliminated due to strict <> clause.
explain (costs off) select * from lp where a <> 'a';
@@ -1316,14 +1199,10 @@ explain (costs off) select * from lp where a <> 'a';
-> Seq Scan on lp_ad
Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_bc
- Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_ef
- Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_g
- Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_default
- Filter: (a <> 'a'::bpchar)
-(11 rows)
+(7 rows)
-- ensure we detect contradictions in clauses; a can't be NULL and NOT NULL.
explain (costs off) select * from lp where a <> 'a' and a is null;
@@ -1334,32 +1213,27 @@ explain (costs off) select * from lp where a <> 'a' and a is null;
(2 rows)
explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on lp_bc
- Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_ef
- Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_g
- Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_null
- Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_default
- Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-(11 rows)
+(6 rows)
-- check that it also works for a partitioned table that's not root,
-- which in this case are partitions of rlp that are themselves
-- list-partitioned on b
explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------
Append
-> Seq Scan on rlp3efgh
- Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
+ Filter: (a = 15)
-> Seq Scan on rlp3_default
- Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
+ Filter: (a = 15)
(5 rows)
--
@@ -1694,36 +1568,25 @@ execute ab_q1 (1, 8, 3);
(0 rows)
explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Append (actual rows=0 loops=1)
- Subplans Removed: 6
-> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-(8 rows)
+(4 rows)
explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Append (actual rows=0 loops=1)
- Subplans Removed: 3
-> Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a1_b3 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-(14 rows)
+(7 rows)
deallocate ab_q1;
-- Runtime pruning after optimizer pruning
@@ -1757,29 +1620,29 @@ execute ab_q1 (1, 8);
(0 rows)
explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Append (actual rows=0 loops=1)
Subplans Removed: 4
-> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ Filter: ((a >= $1) AND (a <= $2))
-> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ Filter: ((a >= $1) AND (a <= $2))
(6 rows)
explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Append (actual rows=0 loops=1)
Subplans Removed: 2
-> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ Filter: ((a >= $1) AND (a <= $2))
-> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ Filter: ((a >= $1) AND (a <= $2))
-> Seq Scan on ab_a3_b1 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ Filter: ((a >= $1) AND (a <= $2))
-> Seq Scan on ab_a3_b2 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+ Filter: ((a >= $1) AND (a <= $2))
(10 rows)
-- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at
@@ -1812,19 +1675,18 @@ execute ab_q2 (1, 8);
(0 rows)
explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Append (actual rows=0 loops=1)
InitPlan 1 (returns $0)
-> Result (actual rows=1 loops=1)
- Subplans Removed: 6
-> Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
+ Filter: (b < $0)
-> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
- Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
+ Filter: (b < $0)
-> Seq Scan on ab_a2_b3 (never executed)
- Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
-(10 rows)
+ Filter: (b < $0)
+(9 rows)
-- As above, but swap the PARAM_EXEC Param to the first partition level
prepare ab_q3 (int, int) as
@@ -1855,19 +1717,18 @@ execute ab_q3 (1, 8);
(0 rows)
explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Append (actual rows=0 loops=1)
InitPlan 1 (returns $0)
-> Result (actual rows=1 loops=1)
- Subplans Removed: 6
-> Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
- Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
+ Filter: (a < $0)
-> Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
- Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
+ Filter: (a < $0)
-> Seq Scan on ab_a3_b2 (never executed)
- Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
-(10 rows)
+ Filter: (a < $0)
+(9 rows)
-- Test a backwards Append scan
create table list_part (a int) partition by list (a);
@@ -2011,11 +1872,11 @@ select explain_parallel_append('execute ab_q4 (2, 2)');
-> Parallel Append (actual rows=0 loops=N)
Subplans Removed: 6
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ Filter: ((a >= $1) AND (a <= $2))
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ Filter: ((a >= $1) AND (a <= $2))
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+ Filter: ((a >= $1) AND (a <= $2))
(13 rows)
-- Test run-time pruning with IN lists.
@@ -2064,11 +1925,11 @@ select explain_parallel_append('execute ab_q5 (1, 1, 1)');
-> Parallel Append (actual rows=0 loops=N)
Subplans Removed: 6
-> Parallel Seq Scan on ab_a1_b1 (actual rows=0 loops=N)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
-> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
-> Parallel Seq Scan on ab_a1_b3 (actual rows=0 loops=N)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
(13 rows)
select explain_parallel_append('execute ab_q5 (2, 3, 3)');
@@ -2082,24 +1943,24 @@ select explain_parallel_append('execute ab_q5 (2, 3, 3)');
-> Parallel Append (actual rows=0 loops=N)
Subplans Removed: 3
-> Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=N)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
-> Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=N)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
-> Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=N)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
-> Parallel Seq Scan on ab_a3_b1 (actual rows=0 loops=N)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
-> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
-> Parallel Seq Scan on ab_a3_b3 (actual rows=0 loops=N)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
(19 rows)
-- Try some params whose values do not belong to any partition.
-- We'll still get a single subplan in this case, but it should not be scanned.
select explain_parallel_append('execute ab_q5 (33, 44, 55)');
- explain_parallel_append
--------------------------------------------------------------------------------
+ explain_parallel_append
+------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=3 loops=1)
Workers Planned: 2
@@ -2108,30 +1969,31 @@ select explain_parallel_append('execute ab_q5 (33, 44, 55)');
-> Parallel Append (actual rows=0 loops=N)
Subplans Removed: 8
-> Parallel Seq Scan on ab_a1_b1 (never executed)
- Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+ Filter: (a = ANY (ARRAY[$1, $2, $3]))
(9 rows)
-- Test Parallel Append with PARAM_EXEC Params
select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2');
- explain_parallel_append
--------------------------------------------------------------------------
- Aggregate (actual rows=1 loops=1)
+ explain_parallel_append
+-------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
InitPlan 1 (returns $0)
-> Result (actual rows=1 loops=1)
InitPlan 2 (returns $1)
-> Result (actual rows=1 loops=1)
- -> Gather (actual rows=0 loops=1)
+ -> Gather (actual rows=3 loops=1)
Workers Planned: 2
Params Evaluated: $0, $1
Workers Launched: 2
- -> Parallel Append (actual rows=0 loops=N)
- -> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
- -> Parallel Seq Scan on ab_a2_b2 (never executed)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
- -> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N)
- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
-(16 rows)
+ -> Partial Aggregate (actual rows=1 loops=3)
+ -> Parallel Append (actual rows=0 loops=N)
+ -> Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=N)
+ Filter: ((a = $0) OR (a = $1))
+ -> Parallel Seq Scan on ab_a2_b2 (never executed)
+ Filter: ((a = $0) OR (a = $1))
+ -> Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=N)
+ Filter: ((a = $0) OR (a = $1))
+(17 rows)
-- Test pruning during parallel nested loop query
create table lprt_a (a int not null);
@@ -2385,27 +2247,18 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1
-- Test run-time partition pruning with UNION ALL parents
explain (analyze, costs off, summary off, timing off)
select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Append (actual rows=0 loops=1)
InitPlan 1 (returns $0)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
+ -> Seq Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
Filter: (b = $0)
- -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (never executed)
- Recheck Cond: (a = 1)
+ -> Seq Scan on ab_a1_b2 ab_a1_b2_1 (never executed)
Filter: (b = $0)
- -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (never executed)
- Recheck Cond: (a = 1)
+ -> Seq Scan on ab_a1_b3 ab_a1_b3_1 (never executed)
Filter: (b = $0)
- -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
- Index Cond: (a = 1)
-> Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
Filter: (b = $0)
-> Seq Scan on ab_a1_b2 (never executed)
@@ -2424,32 +2277,23 @@ select * from (select * from ab where a = 1 union all select * from ab) ab where
Filter: (b = $0)
-> Seq Scan on ab_a3_b3 (never executed)
Filter: (b = $0)
-(37 rows)
+(28 rows)
-- A case containing a UNION ALL with a non-partitioned child.
explain (analyze, costs off, summary off, timing off)
select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Append (actual rows=0 loops=1)
InitPlan 1 (returns $0)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
+ -> Seq Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
Filter: (b = $0)
- -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (never executed)
- Recheck Cond: (a = 1)
+ -> Seq Scan on ab_a1_b2 ab_a1_b2_1 (never executed)
Filter: (b = $0)
- -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (never executed)
- Recheck Cond: (a = 1)
+ -> Seq Scan on ab_a1_b3 ab_a1_b3_1 (never executed)
Filter: (b = $0)
- -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
- Index Cond: (a = 1)
-> Result (actual rows=0 loops=1)
One-Time Filter: (5 = $0)
-> Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
@@ -2470,7 +2314,7 @@ select * from (select * from ab where a = 1 union all (values(10,5)) union all s
Filter: (b = $0)
-> Seq Scan on ab_a3_b3 (never executed)
Filter: (b = $0)
-(39 rows)
+(30 rows)
deallocate ab_q1;
deallocate ab_q2;
@@ -2481,74 +2325,34 @@ deallocate ab_q5;
insert into ab values (1,2);
explain (analyze, costs off, summary off, timing off)
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
- QUERY PLAN
--------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Update on ab_a1 (actual rows=0 loops=1)
Update on ab_a1_b1
Update on ab_a1_b2
Update on ab_a1_b3
-> Nested Loop (actual rows=0 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
- -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
- Recheck Cond: (a = 1)
- Heap Blocks: exact=1
- -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
- -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=0 loops=1)
- Index Cond: (a = 1)
+ -> Seq Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
+ -> Seq Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
+ -> Seq Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
-> Materialize (actual rows=0 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
- -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
- Index Cond: (a = 1)
+ -> Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
-> Nested Loop (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
- -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
- Recheck Cond: (a = 1)
- Heap Blocks: exact=1
- -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
- -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
- Index Cond: (a = 1)
+ -> Seq Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
+ -> Seq Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
+ -> Seq Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
-> Materialize (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b2 (actual rows=1 loops=1)
- Recheck Cond: (a = 1)
- Heap Blocks: exact=1
- -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
- Index Cond: (a = 1)
+ -> Seq Scan on ab_a1_b2 (actual rows=1 loops=1)
-> Nested Loop (actual rows=0 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
- -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
- Recheck Cond: (a = 1)
- Heap Blocks: exact=1
- -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
- Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
- -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
- Index Cond: (a = 1)
+ -> Seq Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
+ -> Seq Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
+ -> Seq Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
-> Materialize (actual rows=0 loops=1)
- -> Bitmap Heap Scan on ab_a1_b3 (actual rows=0 loops=1)
- Recheck Cond: (a = 1)
- -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
- Index Cond: (a = 1)
-(65 rows)
+ -> Seq Scan on ab_a1_b3 (actual rows=0 loops=1)
+(25 rows)
table ab;
a | b
@@ -3133,14 +2937,13 @@ explain (analyze, costs off, summary off, timing off) select * from ma_test wher
InitPlan 1 (returns $0)
-> Limit (actual rows=1 loops=1)
-> Index Scan using ma_test_p2_a_idx on ma_test_p2 ma_test_p2_1 (actual rows=1 loops=1)
- Index Cond: (a IS NOT NULL)
-> Index Scan using ma_test_p1_a_idx on ma_test_p1 (never executed)
Index Cond: (a >= $1)
-> Index Scan using ma_test_p2_a_idx on ma_test_p2 (actual rows=10 loops=1)
Index Cond: (a >= $1)
-> Index Scan using ma_test_p3_a_idx on ma_test_p3 (actual rows=10 loops=1)
Index Cond: (a >= $1)
-(14 rows)
+(13 rows)
reset enable_seqscan;
reset enable_sort;
@@ -3155,12 +2958,11 @@ create table pp_arrpart (a int[]) partition by list (a);
create table pp_arrpart1 partition of pp_arrpart for values in ('{1}');
create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}');
explain (costs off) select * from pp_arrpart where a = '{1}';
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+-------------------------------
Append
-> Seq Scan on pp_arrpart1
- Filter: (a = '{1}'::integer[])
-(3 rows)
+(2 rows)
explain (costs off) select * from pp_arrpart where a = '{1, 2}';
QUERY PLAN
@@ -3174,28 +2976,25 @@ explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
----------------------------------------------------------------------
Append
-> Seq Scan on pp_arrpart1
- Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-> Seq Scan on pp_arrpart2
Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-(5 rows)
+(4 rows)
explain (costs off) update pp_arrpart set a = a where a = '{1}';
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+-------------------------------
Update on pp_arrpart
Update on pp_arrpart1
-> Seq Scan on pp_arrpart1
- Filter: (a = '{1}'::integer[])
-(4 rows)
+(3 rows)
explain (costs off) delete from pp_arrpart where a = '{1}';
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+-------------------------------
Delete on pp_arrpart
Delete on pp_arrpart1
-> Seq Scan on pp_arrpart1
- Filter: (a = '{1}'::integer[])
-(4 rows)
+(3 rows)
drop table pp_arrpart;
-- array type hash partition key
@@ -3244,12 +3043,11 @@ create table pp_enumpart (a pp_colors) partition by list (a);
create table pp_enumpart_green partition of pp_enumpart for values in ('green');
create table pp_enumpart_blue partition of pp_enumpart for values in ('blue');
explain (costs off) select * from pp_enumpart where a = 'blue';
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+------------------------------------
Append
-> Seq Scan on pp_enumpart_blue
- Filter: (a = 'blue'::pp_colors)
-(3 rows)
+(2 rows)
explain (costs off) select * from pp_enumpart where a = 'black';
QUERY PLAN
@@ -3266,12 +3064,11 @@ create table pp_recpart (a pp_rectype) partition by list (a);
create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)');
create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)');
explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype;
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+---------------------------------
Append
-> Seq Scan on pp_recpart_11
- Filter: (a = '(1,1)'::pp_rectype)
-(3 rows)
+(2 rows)
explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype;
QUERY PLAN
@@ -3287,12 +3084,11 @@ create table pp_intrangepart (a int4range) partition by list (a);
create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]');
create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)');
explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range;
- QUERY PLAN
-------------------------------------------
+ QUERY PLAN
+-------------------------------------
Append
-> Seq Scan on pp_intrangepart12
- Filter: (a = '[1,3)'::int4range)
-(3 rows)
+(2 rows)
explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
QUERY PLAN
@@ -3313,8 +3109,7 @@ explain (costs off) select * from pp_lp where a = 1;
--------------------------
Append
-> Seq Scan on pp_lp1
- Filter: (a = 1)
-(3 rows)
+(2 rows)
explain (costs off) update pp_lp set value = 10 where a = 1;
QUERY PLAN
@@ -3322,8 +3117,7 @@ explain (costs off) update pp_lp set value = 10 where a = 1;
Update on pp_lp
Update on pp_lp1
-> Seq Scan on pp_lp1
- Filter: (a = 1)
-(4 rows)
+(3 rows)
explain (costs off) delete from pp_lp where a = 1;
QUERY PLAN
@@ -3331,8 +3125,7 @@ explain (costs off) delete from pp_lp where a = 1;
Delete on pp_lp
Delete on pp_lp1
-> Seq Scan on pp_lp1
- Filter: (a = 1)
-(4 rows)
+(3 rows)
set enable_partition_pruning = off;
set constraint_exclusion = 'partition'; -- this should not affect the result.
@@ -3423,8 +3216,7 @@ explain (costs off) select * from inh_lp where a = 1;
-> Seq Scan on inh_lp
Filter: (a = 1)
-> Seq Scan on inh_lp1
- Filter: (a = 1)
-(5 rows)
+(4 rows)
explain (costs off) update inh_lp set value = 10 where a = 1;
QUERY PLAN
@@ -3435,8 +3227,7 @@ explain (costs off) update inh_lp set value = 10 where a = 1;
-> Seq Scan on inh_lp
Filter: (a = 1)
-> Seq Scan on inh_lp1
- Filter: (a = 1)
-(7 rows)
+(6 rows)
explain (costs off) delete from inh_lp where a = 1;
QUERY PLAN
@@ -3447,8 +3238,7 @@ explain (costs off) delete from inh_lp where a = 1;
-> Seq Scan on inh_lp
Filter: (a = 1)
-> Seq Scan on inh_lp1
- Filter: (a = 1)
-(7 rows)
+(6 rows)
-- Ensure we don't exclude normal relations when we only expect to exclude
-- inheritance children
@@ -3509,15 +3299,15 @@ from (
select 1, 1, 1
) s(a, b, c)
where s.a = 1 and s.b = 1 and s.c = (select 1);
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+----------------------------------------
Append
InitPlan 1 (returns $0)
-> Result
-> Seq Scan on p1
- Filter: ((a = 1) AND (b = 1) AND (c = $0))
+ Filter: ((b = 1) AND (c = $0))
-> Seq Scan on q111
- Filter: ((a = 1) AND (b = 1) AND (c = $0))
+ Filter: (c = $0)
-> Result
One-Time Filter: (1 = $0)
(9 rows)
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index bc16ca4..08a3b41 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -1057,14 +1057,14 @@ NOTICE: f_leak => awesome science fiction
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Append
InitPlan 1 (returns $0)
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction
- Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
(6 rows)
-- pp1 ERROR
@@ -1136,14 +1136,14 @@ NOTICE: f_leak => awesome science fiction
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Append
InitPlan 1 (returns $0)
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction
- Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
(6 rows)
-- viewpoint from regress_rls_carol
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index d09326c..8253534 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -275,12 +275,10 @@ EXPLAIN (costs off) UPDATE range_parted set c = c - 50 WHERE c > 97;
-> Seq Scan on part_c_1_100
Filter: (c > '97'::numeric)
-> Seq Scan on part_d_1_15
- Filter: (c > '97'::numeric)
-> Seq Scan on part_d_15_20
- Filter: (c > '97'::numeric)
-> Seq Scan on part_b_20_b_30
Filter: (c > '97'::numeric)
-(22 rows)
+(20 rows)
-- fail, row movement happens only within the partition subtree.
UPDATE part_c_100_200 set c = c - 20, d = c WHERE c = 105;
