From: Justin Pryzby <pryzby@telsasoft.com>
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: pgsql-hackers@postgresql.org
Subject: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
Date: Fri, 3 Jul 2020 19:45:03 -0500
Message-ID: <20200704004503.GA23805@telsasoft.com> (raw)
(resending to the list)
Hi All
I started looking into Konstantin's 30 month old thread/patch:
|Re: [HACKERS] Secondary index access optimizations
https://www.postgresql.org/message-id/27516421-5afa-203c-e22a-8407e9187327%40postgrespro.ru
..to which David directed me 12 months ago:
|Subject: Re: scans on table fail to be excluded by partition bounds
https://www.postgresql.org/message-id/CAKJS1f_iOmCW11dFzifpDGUgSLAoSTDOjw2tcec%3D7Cgq%2BsR80Q%40mail...
My complaint at the time was for a query plan like:
CREATE TABLE p (i int, j int) PARTITION BY RANGE(i);
SELECT format('CREATE TABLE p%s PARTITION OF p FOR VALUES FROM(%s)TO(%s)', i, 10*(i-1), 10*i) FROM generate_series(1,10)i; \gexec
INSERT INTO p SELECT i%99, i%9 FROM generate_series(1,99999)i;
VACUUM ANALYZE p;
CREATE INDEX ON p(i);
CREATE INDEX ON p(j);
postgres=# explain analyze SELECT * FROM p WHERE (i=10 OR i=20 OR i=30) AND j<2;
Append (cost=28.51..283.25 rows=546 width=12) (actual time=0.100..1.364 rows=546 loops=1)
-> Bitmap Heap Scan on p2 (cost=28.51..93.51 rows=182 width=12) (actual time=0.099..0.452 rows=182 loops=1)
Recheck Cond: ((i = 10) OR (i = 20) OR (i = 30))
Filter: (j < 2)
Rows Removed by Filter: 818
Heap Blocks: exact=45
-> BitmapOr (cost=28.51..28.51 rows=1000 width=0) (actual time=0.083..0.083 rows=0 loops=1)
-> Bitmap Index Scan on p2_i_idx (cost=0.00..19.79 rows=1000 width=0) (actual time=0.074..0.074 rows=1000 loops=1)
Index Cond: (i = 10)
-> Bitmap Index Scan on p2_i_idx (cost=0.00..4.29 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (i = 20)
-> Bitmap Index Scan on p2_i_idx (cost=0.00..4.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (i = 30)
...
This 2nd and 3rd index scan on p2_i_idx are useless, and benign here, but
harmful if we have a large OR list.
I tried rebasing Konstantin's patch, but it didn't handle the case of
"refuting" inconsistent arms of an "OR" list, so I came up with this. This
currently depends on the earlier patch only to call RelationGetPartitionQual,
so appears to be mostly a separate issue.
I believe the current behavior of "OR" lists is also causing another issue I
reported, which a customer hit again last week:
https://www.postgresql.org/message-id/20191216184906.GA2082@telsasoft.com
|ERROR: could not resize shared memory segment...No space left on device
When I looked into it, their explain(format text) was 50MB, due to a list of
~500 "OR" conditions, *each* of which was causing an index scan for each of
~500 partitions, where only one index scan per partition was needed or useful,
all the others being inconsistent with the partition constraint. Thus the
query ultimately errors when it exceeds a resource limit (maybe no surprise
with 8500 index scans).
Here, I was trying to create a test case reproducing that error to see if this
resolves it, but so far hasn't failed. Tomas has a reproducer with a different
(much simpler) plan, though.
CREATE TABLE p (i int, j int) PARTITION BY RANGE(i);
\pset pager off
SELECT format('CREATE TABLE p%s PARTITION OF p FOR VALUES FROM(%s)TO(%s)', i, 10*(i-1), 10*i) FROM generate_series(1,500)i;
\timing off
\set quiet
\set echo always
\gexec
\timing on
INSERT INTO p SELECT i%5000, i%500 FROM generate_series(1,9999999)i;
VACUUM ANALYZE p;
CREATE INDEX ON p(i);
CREATE INDEX ON p(j);
SELECT format('explain analyze SELECT * FROM p WHERE %s', array_to_string(array_agg('i='||(i*10)::text),' OR ')) FROM generate_series(1,500)i;
--
Justin
Attachments:download all (2 files, .tar.gz)0001-Secondary-index-access-optimizations.patch (text/x-diff, 87.2K) download
From bd1f9f93f9bec0585a79bcdf932d1a5e2c0001a0 Mon Sep 17 00:00:00 2001
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Date: Fri, 12 Oct 2018 15:53:51 +0300
Subject: [PATCH 1/2] Secondary index access optimizations
---
.../postgres_fdw/expected/postgres_fdw.out | 8 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 2 +-
src/backend/optimizer/path/allpaths.c | 2 +
src/backend/optimizer/util/plancat.c | 45 ++
src/include/optimizer/plancat.h | 3 +
src/test/regress/expected/create_table.out | 14 +-
src/test/regress/expected/inherit.out | 123 ++--
.../regress/expected/partition_aggregate.out | 10 +-
src/test/regress/expected/partition_join.out | 38 +-
src/test/regress/expected/partition_prune.out | 571 ++++++------------
src/test/regress/expected/rowsecurity.out | 12 +-
src/test/regress/expected/update.out | 4 +-
12 files changed, 313 insertions(+), 519 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.outindex 82fc1290ef..7ab8639dc7 100644--- a/contrib/postgres_fdw/expected/postgres_fdw.out+++ b/contrib/postgres_fdw/expected/postgres_fdw.out@@ -629,12 +629,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.sqlindex 83971665e3..08aef9289e 100644--- a/contrib/postgres_fdw/sql/postgres_fdw.sql+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql@@ -304,7 +304,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.cindex d984da25d7..14de65b792 100644--- a/src/backend/optimizer/path/allpaths.c+++ b/src/backend/optimizer/path/allpaths.c@@ -387,6 +387,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 */
@@ -1037,6 +1038,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
set_dummy_rel_pathlist(childrel);
continue;
}
+ remove_restrictions_implied_by_constraints(root, childrel, childRTE);
/*
* Constraint exclusion failed, so copy the parent's join quals and
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.cindex 25545029d7..45cd72a0fe 100644--- a/src/backend/optimizer/util/plancat.c+++ b/src/backend/optimizer/util/plancat.c@@ -1557,6 +1557,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, 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.hindex c29a7091ec..792c809ed3 100644--- a/src/include/optimizer/plancat.h+++ b/src/include/optimizer/plancat.h@@ -39,6 +39,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/create_table.out b/src/test/regress/expected/create_table.outindex 1c72f23bc9..59cd42f48d 100644--- a/src/test/regress/expected/create_table.out+++ b/src/test/regress/expected/create_table.out@@ -529,11 +529,10 @@ create table partitioned2
partition of partitioned for values in ('(2,4)'::partitioned);
explain (costs off)
select * from partitioned where row(a,b)::partitioned = '(1,2)'::partitioned;
- QUERY PLAN ------------------------------------------------------------+ QUERY PLAN +--------------------------------------
Seq Scan on partitioned1 partitioned
- Filter: (ROW(a, b)::partitioned = '(1,2)'::partitioned)-(2 rows)+(1 row)
drop table partitioned;
-- whole-row Var in partition key works too
@@ -545,11 +544,10 @@ create table partitioned2
partition of partitioned for values in ('(2,4)');
explain (costs off)
select * from partitioned where partitioned = '(1,2)'::partitioned;
- QUERY PLAN ------------------------------------------------------------------+ QUERY PLAN +--------------------------------------
Seq Scan on partitioned1 partitioned
- Filter: ((partitioned.*)::partitioned = '(1,2)'::partitioned)-(2 rows)+(1 row)
\d+ partitioned1
Table "public.partitioned1"
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.outindex 2b68aef654..d76f3d462f 100644--- a/src/test/regress/expected/inherit.out+++ b/src/test/regress/expected/inherit.out@@ -1800,29 +1800,25 @@ explain (costs off) select * from list_parted where a is not null;
----------------------------------------------
Append
-> Seq Scan on part_ab_cd list_parted_1
- Filter: (a IS NOT NULL)
-> Seq Scan on part_ef_gh list_parted_2
- Filter: (a IS NOT NULL)
-> Seq Scan on part_null_xy list_parted_3
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 list_parted_1
- Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-> Seq Scan on part_ef_gh list_parted_2
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 +------------------------------------
Seq Scan on part_ab_cd list_parted
- Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))-(2 rows)+(1 row)
explain (costs off) select * from list_parted where a = 'ab';
QUERY PLAN
@@ -1878,26 +1874,21 @@ explain (costs off) select * from range_list_parted where b = 'ab';
------------------------------------------------------
Append
-> Seq Scan on part_1_10_ab range_list_parted_1
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_10_20_ab range_list_parted_2
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_21_30_ab range_list_parted_3
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_40_inf_ab range_list_parted_4
- 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 range_list_parted_1
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))+ Filter: (a >= 3)
-> Seq Scan on part_10_20_ab range_list_parted_2
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-> Seq Scan on part_21_30_ab range_list_parted_3
- 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;
@@ -1912,44 +1903,34 @@ explain (costs off) select * from range_list_parted where b is null;
QUERY PLAN
------------------------------------------------
Seq Scan on part_40_inf_null range_list_parted
- Filter: (b IS NULL)-(2 rows)+(1 row)
explain (costs off) select * from range_list_parted where a is not null and a < 67;
QUERY PLAN
--------------------------------------------------------
Append
-> Seq Scan on part_1_10_ab range_list_parted_1
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_1_10_cd range_list_parted_2
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_10_20_ab range_list_parted_3
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_10_20_cd range_list_parted_4
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_21_30_ab range_list_parted_5
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_21_30_cd range_list_parted_6
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_40_inf_ab range_list_parted_7
- Filter: ((a IS NOT NULL) AND (a < 67))+ Filter: (a < 67)
-> Seq Scan on part_40_inf_cd range_list_parted_8
- Filter: ((a IS NOT NULL) AND (a < 67))+ Filter: (a < 67)
-> Seq Scan on part_40_inf_null range_list_parted_9
- 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 range_list_parted_1
- Filter: (a >= 30)
-> Seq Scan on part_40_inf_cd range_list_parted_2
- Filter: (a >= 30)
-> Seq Scan on part_40_inf_null range_list_parted_3
- Filter: (a >= 30)-(7 rows)+(4 rows)
drop table list_parted;
drop table range_list_parted;
@@ -1990,7 +1971,7 @@ explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scan
-> Seq Scan on mcrparted1 mcrparted_1
Filter: ((a = 10) AND (abs(b) = 5))
-> Seq Scan on mcrparted2 mcrparted_2
- Filter: ((a = 10) AND (abs(b) = 5))+ Filter: (abs(b) = 5)
-> Seq Scan on mcrparted_def mcrparted_3
Filter: ((a = 10) AND (abs(b) = 5))
(7 rows)
@@ -2022,24 +2003,19 @@ explain (costs off) select * from mcrparted where a > -1; -- scans all partition
-> Seq Scan on mcrparted0 mcrparted_1
Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted1 mcrparted_2
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted2 mcrparted_3
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted3 mcrparted_4
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted4 mcrparted_5
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted5 mcrparted_6
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted_def mcrparted_7
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 +----------------------------------------
Seq Scan on mcrparted4 mcrparted
- Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))+ Filter: ((c > 10) AND (abs(b) = 10))
(2 rows)
explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def
@@ -2049,7 +2025,7 @@ explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mc
-> Seq Scan on mcrparted3 mcrparted_1
Filter: ((c > 20) AND (a = 20))
-> Seq Scan on mcrparted4 mcrparted_2
- Filter: ((c > 20) AND (a = 20))+ Filter: (c > 20)
-> Seq Scan on mcrparted5 mcrparted_3
Filter: ((c > 20) AND (a = 20))
-> Seq Scan on mcrparted_def mcrparted_4
@@ -2069,11 +2045,11 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
InitPlan 1 (returns $0)
-> Limit
-> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax
- Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))+ Index Cond: (b = '12345'::text)
InitPlan 2 (returns $1)
-> Limit
-> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1
- Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))+ Index Cond: (b = '12345'::text)
(9 rows)
select min(a), max(a) from parted_minmax where b = '12345';
@@ -2173,14 +2149,11 @@ explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c;
-------------------------------------------------------------------------
Append
-> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
- Index Cond: (a < 20)
-> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- Index Cond: (a < 20)
-> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- Index Cond: (a < 20)
-> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
Index Cond: (a < 20)
-(9 rows)+(6 rows)
create table mclparted (a int) partition by list(a);
create table mclparted1 partition of mclparted for values in(1);
@@ -2226,14 +2199,11 @@ explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c l
-> Sort
Sort Key: mcrparted_1.a, (abs(mcrparted_1.b)), mcrparted_1.c
-> Seq Scan on mcrparted0 mcrparted_1
- Filter: (a < 20)
-> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
- Index Cond: (a < 20)
-> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
- Index Cond: (a < 20)
-> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
Index Cond: (a < 20)
-(12 rows)+(9 rows)
set enable_bitmapscan = 0;
-- Ensure Append node can be used when the partition is ordered by some
@@ -2245,8 +2215,7 @@ explain (costs off) select * from mcrparted where a = 10 order by a, abs(b), c;
-> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_1
Index Cond: (a = 10)
-> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_2
- Index Cond: (a = 10)-(5 rows)+(4 rows)
reset enable_bitmapscan;
drop table mcrparted;
@@ -2276,39 +2245,35 @@ explain (costs off) select * from bool_rp where b = true order by b,a;
----------------------------------------------------------------------------------
Append
-> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
- Index Cond: (b = true)
-> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
- Index Cond: (b = true)-(5 rows)+(3 rows)
explain (costs off) select * from bool_rp where b = false order by b,a;
QUERY PLAN
------------------------------------------------------------------------------------
Append
-> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
- Index Cond: (b = false)
-> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
- Index Cond: (b = false)-(5 rows)+(3 rows)
explain (costs off) select * from bool_rp where b = true order by a;
- QUERY PLAN ------------------------------------------------------------------------------------ Append- -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1- Index Cond: (b = true)- -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2- Index Cond: (b = true)+ QUERY PLAN +---------------------------------------------------+ Sort+ Sort Key: bool_rp.a+ -> Append+ -> Seq Scan on bool_rp_true_1k bool_rp_1+ -> Seq Scan on bool_rp_true_2k bool_rp_2
(5 rows)
explain (costs off) select * from bool_rp where b = false order by a;
- QUERY PLAN -------------------------------------------------------------------------------------- Append- -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1- Index Cond: (b = false)- -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2- Index Cond: (b = false)+ QUERY PLAN +----------------------------------------------------+ Sort+ Sort Key: bool_rp.a+ -> Append+ -> Seq Scan on bool_rp_false_1k bool_rp_1+ -> Seq Scan on bool_rp_false_2k bool_rp_2
(5 rows)
drop table bool_rp;
diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.outindex c36970575f..f6079752a4 100644--- a/src/test/regress/expected/partition_aggregate.out+++ b/src/test/regress/expected/partition_aggregate.out@@ -738,16 +738,13 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOI
Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
-> Append
-> Seq Scan on pagg_tab1_p1 pagg_tab1_1
- Filter: (x < 20)
-> Seq Scan on pagg_tab1_p2 pagg_tab1_2
- Filter: (x < 20)
-> Hash
-> Append
-> Seq Scan on pagg_tab2_p2 pagg_tab2_1
Filter: (y > 10)
-> Seq Scan on pagg_tab2_p3 pagg_tab2_2
- Filter: (y > 10)-(18 rows)+(15 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,16 +775,13 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOI
Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
-> Append
-> Seq Scan on pagg_tab1_p1 pagg_tab1_1
- Filter: (x < 20)
-> Seq Scan on pagg_tab1_p2 pagg_tab1_2
- Filter: (x < 20)
-> Hash
-> Append
-> Seq Scan on pagg_tab2_p2 pagg_tab2_1
Filter: (y > 10)
-> Seq Scan on pagg_tab2_p3 pagg_tab2_2
- Filter: (y > 10)-(18 rows)+(15 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.outindex b45a590b94..7e2d23a3d4 100644--- a/src/test/regress/expected/partition_join.out+++ b/src/test/regress/expected/partition_join.out@@ -218,14 +218,13 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JO
-> Seq Scan on prt2_p2 prt2_1
Filter: (b > 250)
-> Seq Scan on prt2_p3 prt2_2
- Filter: (b > 250)
-> Hash
-> Append
-> Seq Scan on prt1_p1 prt1_1
- Filter: ((a < 450) AND (b = 0))+ Filter: (b = 0)
-> Seq Scan on prt1_p2 prt1_2
Filter: ((a < 450) AND (b = 0))
-(15 rows)+(14 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
a | c | b | c
@@ -253,7 +252,6 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JO
Filter: ((prt1.b = 0) OR (prt2.a = 0))
-> Append
-> Seq Scan on prt1_p1 prt1_1
- Filter: (a < 450)
-> Seq Scan on prt1_p2 prt1_2
Filter: (a < 450)
-> Hash
@@ -261,8 +259,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JO
-> Seq Scan on prt2_p2 prt2_1
Filter: (b > 250)
-> Seq Scan on prt2_p3 prt2_2
- Filter: (b > 250)-(16 rows)+(14 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
@@ -1181,7 +1178,7 @@ SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
Sort Key: prt1.a
-> Append
-> Seq Scan on prt1_p1 prt1_1
- Filter: ((a < 450) AND (b = 0))+ Filter: (b = 0)
-> Seq Scan on prt1_p2 prt1_2
Filter: ((a < 450) AND (b = 0))
-> Sort
@@ -1190,8 +1187,7 @@ SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
-> Seq Scan on prt2_p2 prt2_1
Filter: (b > 250)
-> Seq Scan on prt2_p3 prt2_2
- Filter: (b > 250)-(18 rows)+(17 rows)
SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
a | b
@@ -2998,8 +2994,8 @@ INSERT INTO prt2_adv SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series
ANALYZE prt2_adv;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN ------------------------------------------------------------+ QUERY PLAN +------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
@@ -3008,13 +3004,13 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a =
-> Seq Scan on prt2_adv_p1 t2_1
-> Hash
-> Seq Scan on prt1_adv_p1 t1_1
- Filter: ((a < 300) AND (b = 0))+ Filter: (b = 0)
-> Hash Join
Hash Cond: (t2_2.b = t1_2.a)
-> Seq Scan on prt2_adv_p2 t2_2
-> Hash
-> Seq Scan on prt1_adv_p2 t1_2
- Filter: ((a < 300) AND (b = 0))+ Filter: (b = 0)
(15 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
@@ -3037,8 +3033,8 @@ CREATE TABLE prt2_adv_default PARTITION OF prt2_adv DEFAULT;
ANALYZE prt2_adv;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a >= 100 AND t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN ---------------------------------------------------------------------------+ QUERY PLAN +------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
@@ -3047,13 +3043,13 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a =
-> Seq Scan on prt2_adv_p1 t2_1
-> Hash
-> Seq Scan on prt1_adv_p1 t1_1
- Filter: ((a >= 100) AND (a < 300) AND (b = 0))+ Filter: (b = 0)
-> Hash Join
Hash Cond: (t2_2.b = t1_2.a)
-> Seq Scan on prt2_adv_p2 t2_2
-> Hash
-> Seq Scan on prt1_adv_p2 t1_2
- Filter: ((a >= 100) AND (a < 300) AND (b = 0))+ Filter: (b = 0)
(15 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a >= 100 AND t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
@@ -4352,7 +4348,7 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a =
-> Seq Scan on plt2_adv_p3 t2_1
-> Hash
-> Seq Scan on plt1_adv_p3 t1_1
- Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))+ Filter: (b < 10)
-> Hash Join
Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
-> Seq Scan on plt2_adv_p4 t2_2
@@ -4405,7 +4401,7 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a =
-> Seq Scan on plt2_adv_p3 t2_1
-> Hash
-> Seq Scan on plt1_adv_p3 t1_1
- Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))+ Filter: (b < 10)
-> Hash Join
Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
-> Seq Scan on plt2_adv_p4 t2_2
@@ -4595,7 +4591,7 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
-> Hash Join
Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b))
-> Seq Scan on alpha_neg_p1 t1_1
- Filter: ((b >= 125) AND (b < 225))+ Filter: (b >= 125)
-> Hash
-> Seq Scan on beta_neg_p1 t2_1
-> Hash Join
@@ -4603,7 +4599,7 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
-> Seq Scan on beta_neg_p2 t2_2
-> Hash
-> Seq Scan on alpha_neg_p2 t1_2
- Filter: ((b >= 125) AND (b < 225))+ Filter: (b < 225)
-> Hash Join
Hash Cond: ((t2_4.a = t1_4.a) AND (t2_4.b = t1_4.b))
-> Append
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.outindex 4315e8e0a3..c469379aec 100644--- a/src/test/regress/expected/partition_prune.out+++ b/src/test/regress/expected/partition_prune.out@@ -27,22 +27,20 @@ explain (costs off) select * from lp where a > 'a' and a < 'd';
-----------------------------------------------------------
Append
-> Seq Scan on lp_bc lp_1
- Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
-> Seq Scan on lp_default lp_2
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 lp_1
- Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))+ Filter: (a > 'a'::bpchar)
-> Seq Scan on lp_bc lp_2
- Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
-> Seq Scan on lp_default lp_3
Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
-(7 rows)+(6 rows)
explain (costs off) select * from lp where a = 'a';
QUERY PLAN
@@ -63,23 +61,17 @@ explain (costs off) select * from lp where a is not null;
-----------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_bc lp_2
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_ef lp_3
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_g lp_4
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_default lp_5
- Filter: (a IS NOT NULL)-(11 rows)+(6 rows)
explain (costs off) select * from lp where a is null;
QUERY PLAN
------------------------
Seq Scan on lp_null lp
- Filter: (a IS NULL)-(2 rows)+(1 row)
explain (costs off) select * from lp where a = 'a' or a = 'c';
QUERY PLAN
@@ -92,56 +84,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 lp_1
- 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 lp_2
- 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 lp_1
- Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_bc lp_2
- Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_ef lp_3
- Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_default lp_4
- 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 lp_1
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_ef lp_2
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_g lp_3
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_default lp_4
- 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 lp_1
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_ef lp_2
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_g lp_3
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_default lp_4
- 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);
@@ -152,8 +132,7 @@ explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate
QUERY PLAN
-----------------------------------------
Seq Scan on coll_pruning_a coll_pruning
- Filter: (a = 'a'::text COLLATE "C")-(2 rows)+(1 row)
-- collation doesn't match the partitioning collation, no pruning occurs
explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX";
@@ -193,25 +172,22 @@ explain (costs off) select * from rlp where a < 1;
QUERY PLAN
----------------------
Seq Scan on rlp1 rlp
- Filter: (a < 1)-(2 rows)+(1 row)
explain (costs off) select * from rlp where 1 > a; /* commuted */
QUERY PLAN
----------------------
Seq Scan on rlp1 rlp
- Filter: (1 > a)-(2 rows)+(1 row)
explain (costs off) select * from rlp where a <= 1;
QUERY PLAN
------------------------------
Append
-> Seq Scan on rlp1 rlp_1
- Filter: (a <= 1)
-> Seq Scan on rlp2 rlp_2
Filter: (a <= 1)
-(5 rows)+(4 rows)
explain (costs off) select * from rlp where a = 1;
QUERY PLAN
@@ -268,65 +244,47 @@ explain (costs off) select * from rlp where a <= 10;
---------------------------------------------
Append
-> Seq Scan on rlp1 rlp_1
- Filter: (a <= 10)
-> Seq Scan on rlp2 rlp_2
- Filter: (a <= 10)
-> Seq Scan on rlp_default_10 rlp_3
- Filter: (a <= 10)
-> Seq Scan on rlp_default_default rlp_4
Filter: (a <= 10)
-(9 rows)+(6 rows)
explain (costs off) select * from rlp where a > 10;
QUERY PLAN
----------------------------------------------
Append
-> Seq Scan on rlp3abcd rlp_1
- Filter: (a > 10)
-> Seq Scan on rlp3efgh rlp_2
- Filter: (a > 10)
-> Seq Scan on rlp3nullxy rlp_3
- Filter: (a > 10)
-> Seq Scan on rlp3_default rlp_4
- Filter: (a > 10)
-> Seq Scan on rlp4_1 rlp_5
- Filter: (a > 10)
-> Seq Scan on rlp4_2 rlp_6
- Filter: (a > 10)
-> Seq Scan on rlp4_default rlp_7
- Filter: (a > 10)
-> Seq Scan on rlp5_1 rlp_8
- Filter: (a > 10)
-> Seq Scan on rlp5_default rlp_9
- Filter: (a > 10)
-> Seq Scan on rlp_default_30 rlp_10
- Filter: (a > 10)
-> Seq Scan on rlp_default_default rlp_11
Filter: (a > 10)
-(23 rows)+(13 rows)
explain (costs off) select * from rlp where a < 15;
QUERY PLAN
---------------------------------------------
Append
-> Seq Scan on rlp1 rlp_1
- Filter: (a < 15)
-> Seq Scan on rlp2 rlp_2
- Filter: (a < 15)
-> Seq Scan on rlp_default_10 rlp_3
- Filter: (a < 15)
-> Seq Scan on rlp_default_default rlp_4
Filter: (a < 15)
-(9 rows)+(6 rows)
explain (costs off) select * from rlp where a <= 15;
QUERY PLAN
---------------------------------------------
Append
-> Seq Scan on rlp1 rlp_1
- Filter: (a <= 15)
-> Seq Scan on rlp2 rlp_2
- Filter: (a <= 15)
-> Seq Scan on rlp3abcd rlp_3
Filter: (a <= 15)
-> Seq Scan on rlp3efgh rlp_4
@@ -336,10 +294,9 @@ explain (costs off) select * from rlp where a <= 15;
-> Seq Scan on rlp3_default rlp_6
Filter: (a <= 15)
-> Seq Scan on rlp_default_10 rlp_7
- Filter: (a <= 15)
-> Seq Scan on rlp_default_default rlp_8
Filter: (a <= 15)
-(17 rows)+(14 rows)
explain (costs off) select * from rlp where a > 15 and b = 'ab';
QUERY PLAN
@@ -348,17 +305,17 @@ explain (costs off) select * from rlp where a > 15 and b = 'ab';
-> Seq Scan on rlp3abcd rlp_1
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp4_1 rlp_2
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp4_2 rlp_3
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp4_default rlp_4
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp5_1 rlp_5
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp5_default rlp_6
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_30 rlp_7
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_default rlp_8
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
(17 rows)
@@ -413,106 +370,77 @@ explain (costs off) select * from rlp where a = 16 and b is not null;
------------------------------------------------
Append
-> Seq Scan on rlp3abcd rlp_1
- Filter: ((b IS NOT NULL) AND (a = 16))+ Filter: (a = 16)
-> Seq Scan on rlp3efgh rlp_2
- Filter: ((b IS NOT NULL) AND (a = 16))+ Filter: (a = 16)
-> Seq Scan on rlp3nullxy rlp_3
Filter: ((b IS NOT NULL) AND (a = 16))
-> Seq Scan on rlp3_default rlp_4
- Filter: ((b IS NOT NULL) AND (a = 16))+ Filter: (a = 16)
(9 rows)
explain (costs off) select * from rlp where a is null;
QUERY PLAN
----------------------------------
Seq Scan on rlp_default_null rlp
- Filter: (a IS NULL)-(2 rows)+(1 row)
explain (costs off) select * from rlp where a is not null;
QUERY PLAN
----------------------------------------------
Append
-> Seq Scan on rlp1 rlp_1
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp2 rlp_2
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3abcd rlp_3
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3efgh rlp_4
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3nullxy rlp_5
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3_default rlp_6
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_1 rlp_7
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_2 rlp_8
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_default rlp_9
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp5_1 rlp_10
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp5_default rlp_11
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_10 rlp_12
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_30 rlp_13
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_default rlp_14
- 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 rlp_1
- Filter: (a > 30)
-> Seq Scan on rlp5_default rlp_2
- Filter: (a > 30)
-> Seq Scan on rlp_default_default rlp_3
Filter: (a > 30)
-(7 rows)+(5 rows)
explain (costs off) select * from rlp where a = 30; /* only default is scanned */
QUERY PLAN
--------------------------------
Seq Scan on rlp_default_30 rlp
- Filter: (a = 30)-(2 rows)+(1 row)
explain (costs off) select * from rlp where a <= 31;
QUERY PLAN
----------------------------------------------
Append
-> Seq Scan on rlp1 rlp_1
- Filter: (a <= 31)
-> Seq Scan on rlp2 rlp_2
- Filter: (a <= 31)
-> Seq Scan on rlp3abcd rlp_3
- Filter: (a <= 31)
-> Seq Scan on rlp3efgh rlp_4
- Filter: (a <= 31)
-> Seq Scan on rlp3nullxy rlp_5
- Filter: (a <= 31)
-> Seq Scan on rlp3_default rlp_6
- Filter: (a <= 31)
-> Seq Scan on rlp4_1 rlp_7
- Filter: (a <= 31)
-> Seq Scan on rlp4_2 rlp_8
- Filter: (a <= 31)
-> Seq Scan on rlp4_default rlp_9
- Filter: (a <= 31)
-> Seq Scan on rlp5_1 rlp_10
Filter: (a <= 31)
-> Seq Scan on rlp_default_10 rlp_11
- Filter: (a <= 31)
-> Seq Scan on rlp_default_30 rlp_12
- Filter: (a <= 31)
-> Seq Scan on rlp_default_default rlp_13
Filter: (a <= 31)
-(27 rows)+(16 rows)
explain (costs off) select * from rlp where a = 1 or a = 7;
QUERY PLAN
@@ -552,13 +480,13 @@ explain (costs off) select * from rlp where a = 1 or b = 'ab';
(25 rows)
explain (costs off) select * from rlp where a > 20 and a < 27;
- QUERY PLAN ------------------------------------------+ QUERY PLAN +--------------------------------
Append
-> Seq Scan on rlp4_1 rlp_1
- Filter: ((a > 20) AND (a < 27))+ Filter: (a > 20)
-> Seq Scan on rlp4_2 rlp_2
- Filter: ((a > 20) AND (a < 27))+ Filter: (a < 27)
(5 rows)
explain (costs off) select * from rlp where a = 29;
@@ -575,24 +503,20 @@ explain (costs off) select * from rlp where a >= 29;
-> Seq Scan on rlp4_default rlp_1
Filter: (a >= 29)
-> Seq Scan on rlp5_1 rlp_2
- Filter: (a >= 29)
-> Seq Scan on rlp5_default rlp_3
- Filter: (a >= 29)
-> Seq Scan on rlp_default_30 rlp_4
- Filter: (a >= 29)
-> Seq Scan on rlp_default_default rlp_5
Filter: (a >= 29)
-(11 rows)+(8 rows)
explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
QUERY PLAN
------------------------------------------------------
Append
-> Seq Scan on rlp1 rlp_1
- Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
-> Seq Scan on rlp4_1 rlp_2
Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
-(5 rows)+(4 rows)
-- where clause contradicts sub-partition's constraint
explain (costs off) select * from rlp where a = 20 or a = 40;
@@ -614,39 +538,28 @@ explain (costs off) select * from rlp3 where a = 20; /* empty */
-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
- QUERY PLAN -----------------------------------+ QUERY PLAN +--------------------------------
Seq Scan on rlp_default_10 rlp
- Filter: ((a > 1) AND (a = 10))-(2 rows)+(1 row)
explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
QUERY PLAN
----------------------------------------------
Append
-> Seq Scan on rlp3abcd rlp_1
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3efgh rlp_2
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3nullxy rlp_3
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3_default rlp_4
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_1 rlp_5
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_2 rlp_6
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_default rlp_7
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp5_1 rlp_8
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp5_default rlp_9
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp_default_30 rlp_10
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp_default_default rlp_11
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
@@ -733,28 +646,23 @@ explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
-> Seq Scan on mc3p1 mc3p_1
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p2 mc3p_2
- Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p3 mc3p_3
- Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p4 mc3p_4
- Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))+ Filter: (abs(b) <= 35)
-> Seq Scan on mc3p_default mc3p_5
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 mc3p_1
- Filter: (a > 10)
-> Seq Scan on mc3p6 mc3p_2
- Filter: (a > 10)
-> Seq Scan on mc3p7 mc3p_3
- Filter: (a > 10)
-> Seq Scan on mc3p_default mc3p_4
Filter: (a > 10)
-(9 rows)+(6 rows)
explain (costs off) select * from mc3p where a >= 10;
QUERY PLAN
@@ -763,43 +671,36 @@ explain (costs off) select * from mc3p where a >= 10;
-> Seq Scan on mc3p1 mc3p_1
Filter: (a >= 10)
-> Seq Scan on mc3p2 mc3p_2
- Filter: (a >= 10)
-> Seq Scan on mc3p3 mc3p_3
- Filter: (a >= 10)
-> Seq Scan on mc3p4 mc3p_4
- Filter: (a >= 10)
-> Seq Scan on mc3p5 mc3p_5
- Filter: (a >= 10)
-> Seq Scan on mc3p6 mc3p_6
- Filter: (a >= 10)
-> Seq Scan on mc3p7 mc3p_7
- Filter: (a >= 10)
-> Seq Scan on mc3p_default mc3p_8
Filter: (a >= 10)
-(17 rows)+(11 rows)
explain (costs off) select * from mc3p where a < 10;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on mc3p0 mc3p_1
- Filter: (a < 10)
-> Seq Scan on mc3p1 mc3p_2
Filter: (a < 10)
-> Seq Scan on mc3p_default mc3p_3
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 mc3p_1
- Filter: ((a <= 10) AND (abs(b) < 10))+ Filter: (abs(b) < 10)
-> Seq Scan on mc3p1 mc3p_2
- Filter: ((a <= 10) AND (abs(b) < 10))+ Filter: (abs(b) < 10)
-> Seq Scan on mc3p2 mc3p_3
- Filter: ((a <= 10) AND (abs(b) < 10))+ Filter: (abs(b) < 10)
-> Seq Scan on mc3p_default mc3p_4
Filter: ((a <= 10) AND (abs(b) < 10))
(9 rows)
@@ -812,10 +713,10 @@ explain (costs off) select * from mc3p where a = 11 and abs(b) = 0;
(2 rows)
explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100;
- QUERY PLAN -------------------------------------------------------+ QUERY PLAN +-----------------------------------------
Seq Scan on mc3p6 mc3p
- Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10))+ Filter: ((c = 100) AND (abs(b) = 10))
(2 rows)
explain (costs off) select * from mc3p where a > 20;
@@ -835,12 +736,10 @@ explain (costs off) select * from mc3p where a >= 20;
-> Seq Scan on mc3p5 mc3p_1
Filter: (a >= 20)
-> Seq Scan on mc3p6 mc3p_2
- Filter: (a >= 20)
-> Seq Scan on mc3p7 mc3p_3
- Filter: (a >= 20)
-> Seq Scan on mc3p_default mc3p_4
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
@@ -877,7 +776,6 @@ explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or
-------------------------------------------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on mc3p0 mc3p_1
- 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 mc3p_2
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 mc3p_3
@@ -886,7 +784,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 mc3p_5
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
@@ -923,12 +821,11 @@ explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 a
-> Seq Scan on mc3p2 mc3p_3
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p3 mc3p_4
- Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p4 mc3p_5
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p_default mc3p_6
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
@@ -958,21 +855,17 @@ explain (costs off) select * from mc2p where a < 2;
---------------------------------------
Append
-> Seq Scan on mc2p0 mc2p_1
- Filter: (a < 2)
-> Seq Scan on mc2p1 mc2p_2
- Filter: (a < 2)
-> Seq Scan on mc2p2 mc2p_3
- Filter: (a < 2)
-> Seq Scan on mc2p_default mc2p_4
Filter: (a < 2)
-(9 rows)+(6 rows)
explain (costs off) select * from mc2p where a = 2 and b < 1;
- QUERY PLAN ----------------------------------+ QUERY PLAN +------------------------
Seq Scan on mc2p3 mc2p
- Filter: ((b < 1) AND (a = 2))-(2 rows)+(1 row)
explain (costs off) select * from mc2p where a > 1;
QUERY PLAN
@@ -981,14 +874,11 @@ explain (costs off) select * from mc2p where a > 1;
-> Seq Scan on mc2p2 mc2p_1
Filter: (a > 1)
-> Seq Scan on mc2p3 mc2p_2
- Filter: (a > 1)
-> Seq Scan on mc2p4 mc2p_3
- Filter: (a > 1)
-> Seq Scan on mc2p5 mc2p_4
- Filter: (a > 1)
-> Seq Scan on mc2p_default mc2p_5
Filter: (a > 1)
-(11 rows)+(8 rows)
explain (costs off) select * from mc2p where a = 1 and b > 1;
QUERY PLAN
@@ -1052,15 +942,13 @@ explain (costs off) select * from boolpart where a = false;
QUERY PLAN
---------------------------------
Seq Scan on boolpart_f boolpart
- Filter: (NOT a)-(2 rows)+(1 row)
explain (costs off) select * from boolpart where not a = false;
QUERY PLAN
---------------------------------
Seq Scan on boolpart_t boolpart
- Filter: a-(2 rows)+(1 row)
explain (costs off) select * from boolpart where a is true or a is not true;
QUERY PLAN
@@ -1117,10 +1005,10 @@ create table boolrangep_ff1 partition of boolrangep for values from ('false', 'f
create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
-- try a more complex case that's been known to trip up pruning in the past
explain (costs off) select * from boolrangep where not a and not b and c = 25;
- QUERY PLAN -----------------------------------------------+ QUERY PLAN +---------------------------------------
Seq Scan on boolrangep_ff1 boolrangep
- Filter: ((NOT a) AND (NOT b) AND (c = 25))+ Filter: (c = 25)
(2 rows)
-- test scalar-to-array operators
@@ -1189,21 +1077,18 @@ explain (costs off) select * from coercepart where a !~ all ('{ab,bc}');
(7 rows)
explain (costs off) select * from coercepart where a = any ('{ab,bc}');
- QUERY PLAN --------------------------------------------------------+ QUERY PLAN +----------------------------------------------
Append
-> Seq Scan on coercepart_ab coercepart_1
- Filter: ((a)::text = ANY ('{ab,bc}'::text[]))
-> Seq Scan on coercepart_bc coercepart_2
- Filter: ((a)::text = ANY ('{ab,bc}'::text[]))-(5 rows)+(3 rows)
explain (costs off) select * from coercepart where a = any ('{ab,null}');
- QUERY PLAN ----------------------------------------------------+ QUERY PLAN +--------------------------------------
Seq Scan on coercepart_ab coercepart
- Filter: ((a)::text = ANY ('{ab,NULL}'::text[]))-(2 rows)+(1 row)
explain (costs off) select * from coercepart where a = any (null::text[]);
QUERY PLAN
@@ -1213,11 +1098,10 @@ explain (costs off) select * from coercepart where a = any (null::text[]);
(2 rows)
explain (costs off) select * from coercepart where a = all ('{ab}');
- QUERY PLAN -----------------------------------------------+ QUERY PLAN +--------------------------------------
Seq Scan on coercepart_ab coercepart
- Filter: ((a)::text = ALL ('{ab}'::text[]))-(2 rows)+(1 row)
explain (costs off) select * from coercepart where a = all ('{ab,bc}');
QUERY PLAN
@@ -1289,7 +1173,6 @@ explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2
Nested Loop
-> Append
-> Seq Scan on mc2p1 t1_1
- Filter: (a = 1)
-> Seq Scan on mc2p2 t1_2
Filter: (a = 1)
-> Seq Scan on mc2p_default t1_3
@@ -1314,7 +1197,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_9
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
@@ -1324,7 +1207,6 @@ explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2
Nested Loop
-> Append
-> Seq Scan on mc2p1 t1_1
- Filter: (a = 1)
-> Seq Scan on mc2p2 t1_2
Filter: (a = 1)
-> Seq Scan on mc2p_default t1_3
@@ -1337,7 +1219,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_3
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;
@@ -1349,12 +1231,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_1
- Filter: (a = 1)
-> Seq Scan on mc2p2 t1_2
Filter: (a = 1)
-> Seq Scan on mc2p_default t1_3
Filter: (a = 1)
-(11 rows)+(10 rows)
--
-- pruning with clauses containing <> operator
@@ -1369,24 +1250,21 @@ explain (costs off) select * from rp where a <> 1;
----------------------------
Append
-> Seq Scan on rp0 rp_1
- Filter: (a <> 1)
-> Seq Scan on rp1 rp_2
Filter: (a <> 1)
-> Seq Scan on rp2 rp_3
- 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 rp_1
- Filter: ((a <> 1) AND (a <> 2))
-> Seq Scan on rp1 rp_2
- Filter: ((a <> 1) AND (a <> 2))+ Filter: (a <> 1)
-> Seq Scan on rp2 rp_3
- 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';
@@ -1396,14 +1274,10 @@ explain (costs off) select * from lp where a <> 'a';
-> Seq Scan on lp_ad lp_1
Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_bc lp_2
- Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_ef lp_3
- Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_g lp_4
- Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_default lp_5
- 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;
@@ -1414,32 +1288,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 lp_1
- Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_ef lp_2
- Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_g lp_3
- Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_null lp_4
- Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_default lp_5
- 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 rlp_1
- 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 rlp_2
- 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)
--
@@ -1780,9 +1649,9 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
Append (actual rows=0 loops=1)
Subplans Removed: 4
-> Seq Scan on ab_a2_b1 ab_1 (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 ab_2 (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);
@@ -1791,13 +1660,13 @@ explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
Append (actual rows=0 loops=1)
Subplans Removed: 2
-> Seq Scan on ab_a2_b1 ab_1 (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 ab_2 (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 ab_3 (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 ab_4 (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
@@ -1952,11 +1821,11 @@ select explain_parallel_append('execute ab_q4 (2, 2)');
-> Parallel Append (actual rows=N loops=N)
Subplans Removed: 6
-> Parallel Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 4))+ Filter: ((a >= $1) AND (a <= $2))
-> Parallel Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N)
- Filter: ((a >= $1) AND (a <= $2) AND (b < 4))+ Filter: ((a >= $1) AND (a <= $2))
-> Parallel Seq Scan on ab_a2_b3 ab_3 (actual rows=N 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.
@@ -1973,11 +1842,11 @@ select explain_parallel_append('execute ab_q5 (1, 1, 1)');
-> Parallel Append (actual rows=N loops=N)
Subplans Removed: 6
-> Parallel Seq Scan on ab_a1_b1 ab_1 (actual rows=N 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 ab_2 (actual rows=N 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 ab_3 (actual rows=N 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)');
@@ -1991,17 +1860,17 @@ select explain_parallel_append('execute ab_q5 (2, 3, 3)');
-> Parallel Append (actual rows=N loops=N)
Subplans Removed: 3
-> Parallel Seq Scan on ab_a2_b1 ab_1 (actual rows=N 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 ab_2 (actual rows=N 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 ab_3 (actual rows=N 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 ab_4 (actual rows=N 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 ab_5 (actual rows=N 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 ab_6 (actual rows=N 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.
@@ -2019,9 +1888,9 @@ select explain_parallel_append('execute ab_q5 (33, 44, 55)');
-- 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=N loops=N)+ explain_parallel_append +------------------------------------------------------------------------------------+ Finalize Aggregate (actual rows=N loops=N)
InitPlan 1 (returns $0)
-> Result (actual rows=N loops=N)
InitPlan 2 (returns $1)
@@ -2030,14 +1899,15 @@ select explain_parallel_append('select count(*) from ab where (a = (select 1) or
Workers Planned: 2
Params Evaluated: $0, $1
Workers Launched: N
- -> Parallel Append (actual rows=N loops=N)- -> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))- -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))- -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)- Filter: ((b = 2) AND ((a = $0) OR (a = $1)))-(16 rows)+ -> Partial Aggregate (actual rows=N loops=N)+ -> Parallel Append (actual rows=N loops=N)+ -> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)+ Filter: ((a = $0) OR (a = $1))+ -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)+ Filter: ((a = $0) OR (a = $1))+ -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N 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);
@@ -2291,27 +2161,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_11 (actual rows=0 loops=1)- Recheck Cond: (a = 1)+ -> Seq Scan on ab_a1_b1 ab_11 (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_12 (never executed)- Recheck Cond: (a = 1)+ -> Seq Scan on ab_a1_b2 ab_12 (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_13 (never executed)- Recheck Cond: (a = 1)+ -> Seq Scan on ab_a1_b3 ab_13 (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 ab_1 (actual rows=0 loops=1)
Filter: (b = $0)
-> Seq Scan on ab_a1_b2 ab_2 (never executed)
@@ -2330,32 +2191,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 ab_9 (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_11 (actual rows=0 loops=1)- Recheck Cond: (a = 1)+ -> Seq Scan on ab_a1_b1 ab_11 (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_12 (never executed)- Recheck Cond: (a = 1)+ -> Seq Scan on ab_a1_b2 ab_12 (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_13 (never executed)- Recheck Cond: (a = 1)+ -> Seq Scan on ab_a1_b3 ab_13 (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 ab_1 (actual rows=0 loops=1)
@@ -2376,7 +2228,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 ab_9 (never executed)
Filter: (b = $0)
-(39 rows)+(30 rows)
-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
create table xy_1 (x int, y int);
@@ -2435,74 +2287,34 @@ deallocate ab_q6;
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 ab_a1_1
Update on ab_a1_b2 ab_a1_2
Update on ab_a1_b3 ab_a1_3
-> Nested Loop (actual rows=0 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_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_2 (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_3 (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_1 (actual rows=0 loops=1)+ -> Seq Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)+ -> Seq Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
-> Materialize (actual rows=0 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_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)+ -> Seq Scan on ab_a1_b1 ab_a1_1 (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_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_2 (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_3 (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_1 (actual rows=0 loops=1)+ -> Seq Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)+ -> Seq Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
-> Materialize (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (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 ab_a1_2 (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_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_2 (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_3 (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_1 (actual rows=0 loops=1)+ -> Seq Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)+ -> Seq Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
-> Materialize (actual rows=0 loops=1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (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 ab_a1_3 (actual rows=0 loops=1)+(25 rows)
table ab;
a | b
@@ -3013,9 +2825,9 @@ select * from mc3p where a < 3 and abs(b) = 1;
--------------------------------------------------------
Append (actual rows=3 loops=1)
-> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1)
- Filter: ((a < 3) AND (abs(b) = 1))+ Filter: (abs(b) = 1)
-> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1)
- Filter: ((a < 3) AND (abs(b) = 1))+ Filter: (abs(b) = 1)
-> Seq Scan on mc3p2 mc3p_3 (actual rows=1 loops=1)
Filter: ((a < 3) AND (abs(b) = 1))
(7 rows)
@@ -3210,8 +3022,7 @@ explain (costs off) select * from pp_arrpart where a = '{1}';
QUERY PLAN
------------------------------------
Seq Scan on pp_arrpart1 pp_arrpart
- Filter: (a = '{1}'::integer[])-(2 rows)+(1 row)
explain (costs off) select * from pp_arrpart where a = '{1, 2}';
QUERY PLAN
@@ -3225,10 +3036,9 @@ explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
----------------------------------------------------------------------
Append
-> Seq Scan on pp_arrpart1 pp_arrpart_1
- Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-> Seq Scan on pp_arrpart2 pp_arrpart_2
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
@@ -3236,8 +3046,7 @@ explain (costs off) update pp_arrpart set a = a where a = '{1}';
Update on pp_arrpart
Update on pp_arrpart1 pp_arrpart_1
-> Seq Scan on pp_arrpart1 pp_arrpart_1
- Filter: (a = '{1}'::integer[])-(4 rows)+(3 rows)
explain (costs off) delete from pp_arrpart where a = '{1}';
QUERY PLAN
@@ -3245,8 +3054,7 @@ explain (costs off) delete from pp_arrpart where a = '{1}';
Delete on pp_arrpart
Delete on pp_arrpart1 pp_arrpart_1
-> Seq Scan on pp_arrpart1 pp_arrpart_1
- Filter: (a = '{1}'::integer[])-(4 rows)+(3 rows)
drop table pp_arrpart;
-- array type hash partition key
@@ -3296,8 +3104,7 @@ explain (costs off) select * from pp_enumpart where a = 'blue';
QUERY PLAN
------------------------------------------
Seq Scan on pp_enumpart_blue pp_enumpart
- Filter: (a = 'blue'::pp_colors)-(2 rows)+(1 row)
explain (costs off) select * from pp_enumpart where a = 'black';
QUERY PLAN
@@ -3317,8 +3124,7 @@ explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype;
QUERY PLAN
--------------------------------------
Seq Scan on pp_recpart_11 pp_recpart
- Filter: (a = '(1,1)'::pp_rectype)-(2 rows)+(1 row)
explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype;
QUERY PLAN
@@ -3337,8 +3143,7 @@ explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range;
QUERY PLAN
-----------------------------------------------
Seq Scan on pp_intrangepart12 pp_intrangepart
- Filter: (a = '[1,3)'::int4range)-(2 rows)+(1 row)
explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
QUERY PLAN
@@ -3358,8 +3163,7 @@ explain (costs off) select * from pp_lp where a = 1;
QUERY PLAN
--------------------------
Seq Scan on pp_lp1 pp_lp
- Filter: (a = 1)-(2 rows)+(1 row)
explain (costs off) update pp_lp set value = 10 where a = 1;
QUERY PLAN
@@ -3367,8 +3171,7 @@ explain (costs off) update pp_lp set value = 10 where a = 1;
Update on pp_lp
Update on pp_lp1 pp_lp_1
-> Seq Scan on pp_lp1 pp_lp_1
- Filter: (a = 1)-(4 rows)+(3 rows)
explain (costs off) delete from pp_lp where a = 1;
QUERY PLAN
@@ -3376,8 +3179,7 @@ explain (costs off) delete from pp_lp where a = 1;
Delete on pp_lp
Delete on pp_lp1 pp_lp_1
-> Seq Scan on pp_lp1 pp_lp_1
- Filter: (a = 1)-(4 rows)+(3 rows)
set enable_partition_pruning = off;
set constraint_exclusion = 'partition'; -- this should not affect the result.
@@ -3386,10 +3188,9 @@ explain (costs off) select * from pp_lp where a = 1;
----------------------------------
Append
-> Seq Scan on pp_lp1 pp_lp_1
- Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
-(5 rows)+(4 rows)
explain (costs off) update pp_lp set value = 10 where a = 1;
QUERY PLAN
@@ -3398,10 +3199,9 @@ explain (costs off) update pp_lp set value = 10 where a = 1;
Update on pp_lp1 pp_lp_1
Update on pp_lp2 pp_lp_2
-> Seq Scan on pp_lp1 pp_lp_1
- Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
-(7 rows)+(6 rows)
explain (costs off) delete from pp_lp where a = 1;
QUERY PLAN
@@ -3410,10 +3210,9 @@ explain (costs off) delete from pp_lp where a = 1;
Delete on pp_lp1 pp_lp_1
Delete on pp_lp2 pp_lp_2
-> Seq Scan on pp_lp1 pp_lp_1
- Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
-(7 rows)+(6 rows)
set constraint_exclusion = 'off'; -- this should not affect the result.
explain (costs off) select * from pp_lp where a = 1;
@@ -3421,10 +3220,9 @@ explain (costs off) select * from pp_lp where a = 1;
----------------------------------
Append
-> Seq Scan on pp_lp1 pp_lp_1
- Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
-(5 rows)+(4 rows)
explain (costs off) update pp_lp set value = 10 where a = 1;
QUERY PLAN
@@ -3433,10 +3231,9 @@ explain (costs off) update pp_lp set value = 10 where a = 1;
Update on pp_lp1 pp_lp_1
Update on pp_lp2 pp_lp_2
-> Seq Scan on pp_lp1 pp_lp_1
- Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
-(7 rows)+(6 rows)
explain (costs off) delete from pp_lp where a = 1;
QUERY PLAN
@@ -3445,10 +3242,9 @@ explain (costs off) delete from pp_lp where a = 1;
Delete on pp_lp1 pp_lp_1
Delete on pp_lp2 pp_lp_2
-> Seq Scan on pp_lp1 pp_lp_1
- Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
-(7 rows)+(6 rows)
drop table pp_lp;
-- Ensure enable_partition_prune does not affect non-partitioned tables.
@@ -3468,8 +3264,7 @@ explain (costs off) select * from inh_lp where a = 1;
-> Seq Scan on inh_lp inh_lp_1
Filter: (a = 1)
-> Seq Scan on inh_lp1 inh_lp_2
- Filter: (a = 1)-(5 rows)+(4 rows)
explain (costs off) update inh_lp set value = 10 where a = 1;
QUERY PLAN
@@ -3480,8 +3275,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 inh_lp_1
- Filter: (a = 1)-(7 rows)+(6 rows)
explain (costs off) delete from inh_lp where a = 1;
QUERY PLAN
@@ -3492,8 +3286,7 @@ explain (costs off) delete from inh_lp where a = 1;
-> Seq Scan on inh_lp
Filter: (a = 1)
-> Seq Scan on inh_lp1 inh_lp_1
- Filter: (a = 1)-(7 rows)+(6 rows)
-- Ensure we don't exclude normal relations when we only expect to exclude
-- inheritance children
@@ -3553,15 +3346,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 p
- Filter: ((a = 1) AND (b = 1) AND (c = $0))+ Filter: ((b = 1) AND (c = $0))
-> Seq Scan on q111 q1
- 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.outindex 9506aaef82..3309d26c87 100644--- a/src/test/regress/expected/rowsecurity.out+++ b/src/test/regress/expected/rowsecurity.out@@ -1057,10 +1057,10 @@ NOTICE: f_leak => awesome science fiction
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
- QUERY PLAN ---------------------------------------------------------------+ QUERY PLAN +----------------------------------------------------
Seq Scan on part_document_fiction part_document
- Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
InitPlan 1 (returns $0)
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
@@ -1135,10 +1135,10 @@ NOTICE: f_leak => awesome science fiction
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
- QUERY PLAN ---------------------------------------------------------------+ QUERY PLAN +----------------------------------------------------
Seq Scan on part_document_fiction part_document
- Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
InitPlan 1 (returns $0)
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.outindex bf939d79f6..0d821ade5b 100644--- a/src/test/regress/expected/update.out+++ b/src/test/regress/expected/update.out@@ -327,12 +327,10 @@ EXPLAIN (costs off) UPDATE range_parted set c = c - 50 WHERE c > 97;
-> Seq Scan on part_c_1_100 range_parted_4
Filter: (c > '97'::numeric)
-> Seq Scan on part_d_1_15 range_parted_5
- Filter: (c > '97'::numeric)
-> Seq Scan on part_d_15_20 range_parted_6
- Filter: (c > '97'::numeric)
-> Seq Scan on part_b_20_b_30 range_parted_7
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;
--
2.17.0
0002-Avoid-index-scan-inconsistent-with-partition-constra.patch (text/x-diff, 3.6K) download
From 926fe578495242fe2b400c2f7a4310c12a24459f Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Thu, 2 Jul 2020 18:46:48 -0500
Subject: [PATCH 2/2] Avoid index scan inconsistent with partition constraint
---
src/backend/optimizer/path/indxpath.c | 5 +++++
src/test/regress/expected/create_index.out | 25 ++++++++++++++++++++++
src/test/regress/sql/create_index.sql | 10 +++++++++
3 files changed, 40 insertions(+)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.cindex 2a50272da6..c103496bfc 100644--- a/src/backend/optimizer/path/indxpath.c+++ b/src/backend/optimizer/path/indxpath.c@@ -1315,6 +1315,11 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(!restriction_is_or_clause(rinfo));
orargs = list_make1(rinfo);
+ /* Avoid scanning indexes using a scan condition which is+ * inconsistent with the partition constraint */+ if (predicate_refuted_by(rel->partition_qual, orargs, false))+ continue;+
indlist = build_paths_for_OR(root, rel,
orargs,
all_clauses);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.outindex e3e6634d7e..1a976ad211 100644--- a/src/test/regress/expected/create_index.out+++ b/src/test/regress/expected/create_index.out@@ -1843,6 +1843,31 @@ SELECT count(*) FROM tenk1
10
(1 row)
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;+VACUUM ANALYZE bitmapor;+CREATE INDEX ON bitmapor(i);+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);+ QUERY PLAN +--------------------------------------------------------+ Append+ -> Bitmap Heap Scan on bitmapor1 bitmapor_1+ Recheck Cond: ((i = 1) OR (i = 2))+ -> BitmapOr+ -> Bitmap Index Scan on bitmapor1_i_idx+ Index Cond: (i = 1)+ -> Bitmap Index Scan on bitmapor1_i_idx+ Index Cond: (i = 2)+ -> Bitmap Heap Scan on bitmapor2 bitmapor_2+ Recheck Cond: (i = 11)+ -> Bitmap Index Scan on bitmapor2_i_idx+ Index Cond: (i = 11)+(12 rows)++DROP TABLE bitmapor;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sqlindex f3667bacdc..dd1de8ee1d 100644--- a/src/test/regress/sql/create_index.sql+++ b/src/test/regress/sql/create_index.sql@@ -703,6 +703,16 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+-- Check that indexes are not scanned for "arms" of an OR with a scan condition inconsistent with the partition constraint+CREATE TABLE bitmapor (i int, j int) PARTITION BY RANGE(i);+CREATE TABLE bitmapor1 PARTITION OF bitmapor FOR VALUES FROM (0) TO (10);+CREATE TABLE bitmapor2 PARTITION OF bitmapor FOR VALUES FROM (10) TO (20);+INSERT INTO bitmapor SELECT i%20, i%2 FROM generate_series(1,55555)i;+VACUUM ANALYZE bitmapor;+CREATE INDEX ON bitmapor(i);+EXPLAIN (COSTS OFF) SELECT * FROM bitmapor WHERE (i=1 OR i=2 OR i=11);+DROP TABLE bitmapor;+
--
-- Check behavior with duplicate index column contents
--
--
2.17.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, k.knizhnik@postgrespro.ru, david.rowley@2ndquadrant.com
Subject: Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
In-Reply-To: <20200704004503.GA23805@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox