Thread: Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
From
Justin Pryzby
Date:
Added here: https://commitfest.postgresql.org/29/2644/ And updated tests to pass following: |commit 689696c7110f148ede8004aae50d7543d05b5587 |Author: Tom Lane <tgl@sss.pgh.pa.us> |Date: Tue Jul 14 18:56:49 2020 -0400 | | Fix bitmap AND/OR scans on the inside of a nestloop partition-wise join.
Attachment
Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
From
Justin Pryzby
Date:
Rebased and updated for tests added in 13838740f. -- Justin Pryzby System Administrator Telsasoft +1-952-707-8581
Attachment
Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
From
Soumyadeep Chakraborty
Date:
Hi Justin, Attached is a minimal patch that is rebased and removes the dependency on Konstantin's earlier patch[1], making it enough to remove the extraneous index scans as Justin brought up. Is this the direction we want to head in? Tagging Konstantin in the email to hear his input on his old patch. Since Justin's attached patch [1] does not include the work that was done on the operator_predicate_proof() and as discussed here in [2], that work is important to see real benefits? Just wanted to check before reviewing [1]. Regards, Soumyadeep (VMware) [1] https://www.postgresql.org/message-id/attachment/112074/0001-Secondary-index-access-optimizations.patch [2] https://www.postgresql.org/message-id/5A006016.1010009%40postgrespro.ru
Attachment
Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
From
Justin Pryzby
Date:
On Wed, Sep 30, 2020 at 04:52:02PM -0700, Soumyadeep Chakraborty wrote: > Hi Justin, > > Attached is a minimal patch that is rebased and removes the > dependency on Konstantin's earlier patch[1], making it enough to remove > the extraneous index scans as Justin brought up. Is this the direction we > want to head in? Yes, thanks for doing that. I hadn't dug into it yet to figure out what to put where to separate the patches. It seems like my patch handles a different goal than Konstantin's, but they both depend on having the constraints populated. -- Justin
Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
From
Konstantin Knizhnik
Date:
The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: tested, passed Documentation: not tested I think that work on improving operator_predicate_proof should really be done in separate patch. And this minimal patch is doing it's work well. The new status of this patch is: Ready for Committer
Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
From
Tom Lane
Date:
I started looking through this patch. I really quite dislike solving
this via a kluge in indxpath.c. There are multiple disadvantages
to that:
* It only helps for the very specific problem of redundant bitmap
index scans, whereas the problem of applying redundant qual checks
in partition scans seems pretty general.
* It's not unlikely that this will end up trying to make the same
proof multiple times (and the lack of any way to turn that off,
through constraint_exclusion or some other knob, isn't too cool).
* It does nothing to fix rowcount estimates in the light of the
knowledge that some of the restriction clauses are no-ops. Now,
if we have up-to-date stats we'll probably manage to come out with
an appropriate 0 or 1 selectivity anyway, but we might not have those.
In any case, spending significant effort to estimate a selectivity
when some other part of the code has taken the trouble to *prove* the
clause true or false seems very undesirable.
* I'm not even convinced that the logic is correct, specifically that
it's okay to just "continue" if we refute the OR clause. That seems
likely to break generate_bitmap_or_paths' surrounding loop logic about
"We must be able to match at least one index to each of the arms of
the OR". At least, if that still works it requires more than zero
commentary about why.
So I like much better the idea of Konstantin's old patch, that we modify
the rel's baserestrictinfo list by removing quals that we can prove
true. We could extend that to solve the bitmapscan problem by removing
OR arms that we can prove false. So I started to review that patch more
carefully, and after awhile realized that it has a really fundamental
problem: it is trying to use CHECK predicates to prove WHERE clauses.
But we don't know that CHECK predicates are true, only that they are
not-false, and there is no proof mode in predtest.c that will allow
proving some clauses true based only on other ones being not-false.
We can salvage something by restricting the input quals to be only
partition quals, since those are built to be guaranteed-true-or-false;
we can assume they don't yield NULL. There's a hole in that for
hashing, as I noted elsewhere, but we'll fail to prove anything anyway
from a satisfies_hash_partition() qual. (In principle we could also use
attnotnull quals, which also have that property. But I'm dubious that
that will help often enough to be worth the extra cycles for predtest.c
to process them.)
So after a bit of coding I had the attached. This follows Konstantin's
original patch in letting relation_excluded_by_constraints() change
the baserestrictinfo list. I read the comments in the older thread
about people not liking that, and I can see the point. But I'm not
convinced that the later iterations of the patch were an improvement,
because (a) the call locations for
remove_restrictions_implied_by_constraints() seemed pretty random, and
(b) it seems necessary to have relation_excluded_by_constraints() and
remove_restrictions_implied_by_constraints() pretty much in bed with
each other if we don't want to duplicate constraint-fetching work.
Note the comment on get_relation_constraints() that it's called at
most once per relation; that's not something I particularly desire
to give up, because a relcache open isn't terribly cheap. Also
(c) I think it's important that there be a way to suppress this
overhead when it's not useful. In the patch as attached, turning off
constraint_exclusion does that since relation_excluded_by_constraints()
falls out before getting to the new code. If we make
remove_restrictions_implied_by_constraints() independent then it
will need some possibly-quite-duplicative logic to check
constraint_exclusion. (Of course, if we'd rather condition this
on some other GUC then that argument falls down. But I think we
need something.) So, I'm not dead set on this code structure, but
I haven't seen one I like better.
Anyway, this seems to work, and if the regression test changes are
any guide then it may fire often enough in the real world to be useful.
Nonetheless, I'm concerned about performance, because predtest.c is a
pretty expensive thing and there will be a lot of cases where the work
is useless. I did a quick check using pgbench's option to partition
the tables, and observed that the -S (select only) test case seemed to
get about 2.5% slower with the patch than without. That's not far
outside the noise floor, so maybe it's not real, but if it is real then
it seems pretty disastrous. Perhaps we could avoid that problem by
removing the "predicate_implied_by" cases and only trying the
"predicate_refuted_by" case, so that no significant time is added
unless you've got an OR restriction clause on a partitioned table.
That seems like it'd lose a lot of the benefit though :-(.
So I'm not sure where to go from here. Thoughts? Anyone else
care to run some performance tests?
regards, tom lane
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2d88d06358..01151cef8d 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -7836,18 +7836,17 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.b, utrtest_2.ctid
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
-(9 rows)
+(8 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 1 or a = 2 returning *;
@@ -7868,8 +7867,7 @@ update utrtest set a = 1 where a = 2 returning *;
Update on public.locp utrtest_1
-> Seq Scan on public.locp utrtest_1
Output: 1, utrtest_1.b, utrtest_1.ctid
- Filter: (utrtest_1.a = 2)
-(6 rows)
+(5 rows)
-- The new values are concatenated with ' triggered !'
update utrtest set a = 1 where a = 2 returning *;
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 52c01eb86b..15c50b9522 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -35,12 +35,14 @@
#include "foreign/fdwapi.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
#include "nodes/supportnodes.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/plancat.h"
#include "optimizer/prep.h"
+#include "optimizer/restrictinfo.h"
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
@@ -70,6 +72,8 @@ static List *get_relation_constraints(PlannerInfo *root,
bool include_noinherit,
bool include_notnull,
bool include_partition);
+static void remove_restrictions_implied_by_constraints(RelOptInfo *rel,
+ List *given_clauses);
static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
Relation heapRelation);
static List *get_relation_statistics(RelOptInfo *rel, Relation relation);
@@ -1276,13 +1280,16 @@ get_relation_constraints(PlannerInfo *root,
}
/*
- * Add partitioning constraints, if requested.
+ * If the table is a partition, make sure we have the partition qual, and
+ * add it to the result if requested. (But even if it's not requested,
+ * fetch it while we have the relcache entry open.)
*/
- if (include_partition && relation->rd_rel->relispartition)
+ if (relation->rd_rel->relispartition)
{
/* make sure rel->partition_qual is set */
set_baserel_partition_constraint(relation, rel);
- result = list_concat(result, rel->partition_qual);
+ if (include_partition)
+ result = list_concat(result, rel->partition_qual);
}
table_close(relation, NoLock);
@@ -1387,6 +1394,14 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
* self-inconsistent restrictions, or restrictions inconsistent with the
* relation's applicable constraints.
*
+ * Even if the relation does need to be scanned, we may be able to remove
+ * or simplify some of its baserestrictinfo clauses on the basis that
+ * they can be proven true, or some OR arms can be proven false, using
+ * the relation's applicable constraints. Hence, this function may also
+ * update rel->baserestrictinfo. (It's a bit hackish to include that in
+ * this function's responsibilities, but it saves fetching the constraint
+ * expressions twice.)
+ *
* Note: this examines only rel->relid, rel->reloptkind, and
* rel->baserestrictinfo; therefore it can be called before filling in
* other fields of the RelOptInfo.
@@ -1562,9 +1577,171 @@ relation_excluded_by_constraints(PlannerInfo *root,
if (predicate_refuted_by(safe_constraints, rel->baserestrictinfo, false))
return true;
+ /*
+ * OK, we do need to scan the relation, but we might be able to simplify
+ * its baserestrictinfo list. For this purpose, we may use its partition
+ * constraint even if that was deemed duplicative above.
+ * (get_relation_constraints() will have set rel->partition_qual for us.)
+ *
+ * Unfortunately, we *cannot* use regular CHECK constraints here, because
+ * we can only assume that those are not-FALSE, not that they are surely
+ * TRUE. (Partitioning constraints are constructed to be either TRUE or
+ * FALSE, so we can make the needed proofs with them.) We could use
+ * attnotnull constraints, but it's not clear that those would add enough
+ * to be worth another relcache visit.
+ */
+ if (rel->partition_qual)
+ remove_restrictions_implied_by_constraints(rel, rel->partition_qual);
+
return false;
}
+/*
+ * Remove or simplify any rel->baserestrictinfo clauses that are implied
+ * or refuted by the given_clauses. Note that we assume the given
+ * clauses are known TRUE, not just that they are known not-FALSE.
+ */
+static void
+remove_restrictions_implied_by_constraints(RelOptInfo *rel,
+ List *given_clauses)
+{
+ List *new_restrictions = NIL;
+ ListCell *lc;
+
+ foreach(lc, rel->baserestrictinfo)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+
+ if (!restriction_is_or_clause(rinfo))
+ {
+ /*
+ * Simple clause: see if we can prove it redundant. There's no
+ * need to try to refute it though; we should not have got here if
+ * that were possible. Note: predicate_implied_by expects us to
+ * verify that its first argument contains no volatile functions.
+ */
+ if (contain_mutable_functions((Node *) rinfo->clause) ||
+ !predicate_implied_by(list_make1(rinfo->clause),
+ given_clauses,
+ false))
+ new_restrictions = lappend(new_restrictions, rinfo);
+ }
+ else
+ {
+ /*
+ * OR clause: try to prove or refute each OR arm individually.
+ * Refuted arms can be dropped. If we prove any one arm true, the
+ * whole OR is true and can be dropped in toto.
+ */
+ List *or_args = ((BoolExpr *) rinfo->clause)->args;
+ List *new_or_args = NIL;
+ bool or_is_true = false;
+ ListCell *lc2;
+
+ foreach(lc2, or_args)
+ {
+ Node *orarg = (Node *) lfirst(lc2);
+
+ if (contain_mutable_functions(orarg))
+ {
+ /* mutable, so no proof possible */
+ new_or_args = lappend(new_or_args, orarg);
+ }
+ else if (predicate_implied_by(list_make1(orarg),
+ given_clauses,
+ false))
+ {
+ /* orarg is provably true, therefore the OR is as well */
+ or_is_true = true;
+ break;
+ }
+ else if (predicate_refuted_by(list_make1(orarg),
+ given_clauses,
+ true))
+ {
+ /* orarg is provably false-or-null, drop it */
+ continue;
+ }
+ else
+ new_or_args = lappend(new_or_args, orarg);
+ }
+
+ /* Drop constant-true OR clauses from baserestrictinfo */
+ if (or_is_true)
+ continue;
+
+ if (list_length(new_or_args) == list_length(or_args))
+ {
+ /* Needn't rebuild the RestrictInfo if we eliminated nothing */
+ new_restrictions = lappend(new_restrictions, rinfo);
+ }
+ else if (new_or_args == NIL)
+ {
+ /*
+ * Hmm, we refuted all the arms, making the OR constant-false.
+ * This case should be unreachable, because if this is
+ * provable then relation_excluded_by_constraints should have
+ * excluded the rel altogether. Hence, don't work hard here;
+ * if we do somehow get here, just re-use the RestrictInfo.
+ */
+ new_restrictions = lappend(new_restrictions, rinfo);
+ }
+ else
+ {
+ /* Otherwise, build RestrictInfo(s) from the surviving arms */
+ Expr *newclause;
+ RestrictInfo *newrinfo;
+
+ /* Build valid clause, with OR only if needed */
+ if (list_length(new_or_args) == 1)
+ newclause = (Expr *) linitial(new_or_args);
+ else
+ newclause = make_orclause(new_or_args);
+
+ if (is_andclause(newclause))
+ {
+ /*
+ * We must manually flatten this AND-below-OR, now that
+ * we've removed all its sibling OR arms. In principle,
+ * we could now try to prune these clauses individually,
+ * but it doesn't seem likely to be worth the effort.
+ */
+ ListCell *lc3;
+
+ foreach(lc3, ((BoolExpr *) newclause)->args)
+ {
+ Expr *andarm = (Expr *) lfirst(lc3);
+
+ newrinfo = make_restrictinfo(andarm,
+ rinfo->is_pushed_down,
+ rinfo->outerjoin_delayed,
+ false,
+ rinfo->security_level,
+ NULL,
+ rinfo->outer_relids,
+ rinfo->nullable_relids);
+ new_restrictions = lappend(new_restrictions, newrinfo);
+ }
+ }
+ else
+ {
+ newrinfo = make_restrictinfo(newclause,
+ rinfo->is_pushed_down,
+ rinfo->outerjoin_delayed,
+ false,
+ rinfo->security_level,
+ NULL,
+ rinfo->outer_relids,
+ rinfo->nullable_relids);
+ new_restrictions = lappend(new_restrictions, newrinfo);
+ }
+ }
+ }
+ }
+
+ rel->baserestrictinfo = new_restrictions;
+}
+
/*
* build_physical_tlist
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index ed8c01b8de..0c9be92af6 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.out
index 2b68aef654..25eafe12c1 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1800,22 +1800,19 @@ 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
@@ -1878,26 +1875,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 +1904,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 +1972,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 +2004,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 +2026,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 +2046,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 +2150,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 +2200,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 +2216,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 +2246,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.out
index 45c698daf4..ebfdf15fb0 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.out
index 0057f41caa..2c748aa780 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)
t2ON 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)
t2ON 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.bWHERE t1.b = 0 ORDER BY t1.a, t2.b;
a | b
@@ -2197,7 +2193,7 @@ where not exists (select 1 from prtx2
Append
-> Nested Loop Anti Join
-> Seq Scan on prtx1_1
- Filter: ((a < 20) AND (c = 120))
+ Filter: (c = 120)
-> Bitmap Heap Scan on prtx2_1
Recheck Cond: ((b = prtx1_1.b) AND (c = 123))
Filter: (a = prtx1_1.a)
@@ -2238,7 +2234,7 @@ where not exists (select 1 from prtx2
Append
-> Nested Loop Anti Join
-> Seq Scan on prtx1_1
- Filter: ((a < 20) AND (c = 91))
+ Filter: (c = 91)
-> Bitmap Heap Scan on prtx2_1
Recheck Cond: ((b = (prtx1_1.b + 1)) OR (c = 99))
Filter: (a = prtx1_1.a)
@@ -3102,8 +3098,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
ORDERBY t1.a, t2.b;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
@@ -3112,13 +3108,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
ORDERBY t1.a, t2.b;
@@ -3141,8 +3137,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 <
300AND t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
@@ -3151,13 +3147,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 <
300AND t1.b = 0 ORDER BY t1.a, t2.b;
@@ -4456,7 +4452,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
@@ -4509,7 +4505,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
@@ -4699,7 +4695,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
@@ -4707,7 +4703,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
@@ -4771,8 +4767,8 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
EXPLAIN (COSTS OFF)
SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE ((t1.b >= 100 AND t1.b <
110)OR (t1.b >= 200 AND t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210)) AND t1.c IN
('0004','0009') ORDER BY t1.a, t1.b, t2.b;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.a, t1.b, t2.b
-> Append
@@ -4780,15 +4776,15 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.c = t2
Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
-> Append
-> Seq Scan on alpha_neg_p1 t1_2
- Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200)
AND(b < 210))))
+ Filter: ((b >= 100) AND (b < 110) AND (c = ANY ('{0004,0009}'::text[])))
-> Seq Scan on alpha_neg_p2 t1_3
- Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200)
AND(b < 210))))
+ Filter: ((b >= 200) AND (b < 210) AND (c = ANY ('{0004,0009}'::text[])))
-> Hash
-> Append
-> Seq Scan on beta_neg_p1 t2_2
- Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
+ Filter: ((b >= 100) AND (b < 110))
-> Seq Scan on beta_neg_p2 t2_3
- Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
+ Filter: ((b >= 200) AND (b < 210))
-> Nested Loop
Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
-> Seq Scan on alpha_pos_p2 t1_4
@@ -4834,17 +4830,17 @@ 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) AND (t1_1.c = t2_1.c))
-> Seq Scan on alpha_neg_p1 t1_1
- Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b
<210))))
+ Filter: ((b >= 100) AND (b < 110) AND (c = ANY ('{0004,0009}'::text[])))
-> Hash
-> Seq Scan on beta_neg_p1 t2_1
- Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
+ Filter: ((b >= 100) AND (b < 110))
-> Hash Join
Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
-> Seq Scan on alpha_neg_p2 t1_2
- Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b
<210))))
+ Filter: ((b >= 200) AND (b < 210) AND (c = ANY ('{0004,0009}'::text[])))
-> Hash
-> Seq Scan on beta_neg_p2 t2_2
- Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
+ Filter: ((b >= 200) AND (b < 210))
-> Nested Loop
Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
-> Seq Scan on alpha_pos_p2 t1_3
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index c72a6d051f..85f536b143 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,85 +61,67 @@ 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
-----------------------------------------------------------
+ QUERY PLAN
+-----------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = 'a'::bpchar)
-> Seq Scan on lp_bc lp_2
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = 'c'::bpchar)
(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)
-> Seq Scan on lp_bc lp_2
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: (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
@@ -526,39 +454,39 @@ explain (costs off) select * from rlp where a = 1 or b = 'ab';
-------------------------------------------------------
Append
-> Seq Scan on rlp1 rlp_1
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp2 rlp_2
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp3abcd rlp_3
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp4_1 rlp_4
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp4_2 rlp_5
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp4_default rlp_6
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp5_1 rlp_7
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp5_default rlp_8
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_10 rlp_9
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_30 rlp_10
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_null rlp_11
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_default rlp_12
- Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
(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,34 +503,30 @@ 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
-------------------------------------------------------
+ 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)
+ Filter: ((a > 20) AND (a < 25))
+(4 rows)
-- where clause contradicts sub-partition's constraint
explain (costs off) select * from rlp where a = 20 or a = 40;
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+--------------------------------------
Append
-> Seq Scan on rlp4_1 rlp_1
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = 20)
-> Seq Scan on rlp5_default rlp_2
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = 40)
(5 rows)
explain (costs off) select * from rlp3 where a = 20; /* empty */
@@ -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
@@ -656,19 +569,19 @@ explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
(2 rows)
explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------
Append
-> Seq Scan on rlp2 rlp_1
- Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ Filter: ((a = 1) AND (a = 3))
-> Seq Scan on rlp3abcd rlp_2
- Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ Filter: ((a > 1) AND (a = 15))
-> Seq Scan on rlp3efgh rlp_3
- Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ Filter: ((a > 1) AND (a = 15))
-> Seq Scan on rlp3nullxy rlp_4
- Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ Filter: ((a > 1) AND (a = 15))
-> Seq Scan on rlp3_default rlp_5
- Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ Filter: ((a > 1) AND (a = 15))
(11 rows)
-- multi-column keys
@@ -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,23 +736,21 @@ 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
---------------------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on mc3p1 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)))
+ Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p2 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)))
+ Filter: ((a = 10) AND (c = 10) AND (abs(b) = 5))
-> Seq Scan on mc3p5 mc3p_3
- 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)))
+ Filter: ((a > 11) AND (a < 20))
-> Seq Scan on mc3p_default mc3p_4
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)))
(9 rows)
@@ -861,13 +760,13 @@ 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))
+ Filter: (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))
+ Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p2 mc3p_3
- 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))
+ Filter: ((a = 10) AND (c = 10) AND (abs(b) = 5))
-> Seq Scan on mc3p5 mc3p_4
- 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))
+ Filter: ((a > 11) AND (a < 20))
-> 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))
(11 rows)
@@ -877,13 +776,13 @@ 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))
+ Filter: ((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))
+ Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR (a = 1))
-> Seq Scan on mc3p2 mc3p_3
- 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))
+ Filter: ((a = 10) AND (c = 10) AND (abs(b) = 5))
-> Seq Scan on mc3p5 mc3p_4
- 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))
+ Filter: ((a > 11) AND (a < 20))
-> 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)
@@ -897,17 +796,17 @@ explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
-> Seq Scan on mc3p1 mc3p_2
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p2 mc3p_3
- Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ Filter: (c = 1)
-> Seq Scan on mc3p3 mc3p_4
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p4 mc3p_5
- Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ Filter: (c = 1)
-> Seq Scan on mc3p5 mc3p_6
- Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ Filter: ((abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p6 mc3p_7
- Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ Filter: (c = 1)
-> Seq Scan on mc3p7 mc3p_8
- Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ Filter: ((abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p_default mc3p_9
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
(19 rows)
@@ -917,29 +816,28 @@ explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 a
------------------------------------------------------------------------------
Append
-> Seq Scan on mc3p0 mc3p_1
- Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
+ Filter: ((a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p1 mc3p_2
- Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
+ Filter: ((a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p2 mc3p_3
- Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
+ Filter: ((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)))
+ Filter: ((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
-----------------------------------------------------------------------------
Append
-> Seq Scan on mc3p0 mc3p_1
- Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
+ Filter: ((a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p1 mc3p_2
- Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
+ Filter: ((a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p2 mc3p_3
- Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
+ Filter: ((a = 10) AND (abs(b) = 9))
-> Seq Scan on mc3p_default mc3p_4
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
(9 rows)
@@ -958,21 +856,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 +875,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 +943,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
@@ -1069,7 +958,7 @@ explain (costs off) select * from boolpart where a is true or a is not true;
-> Seq Scan on boolpart_f boolpart_1
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
-> Seq Scan on boolpart_t boolpart_2
- Filter: ((a IS TRUE) OR (a IS NOT TRUE))
+ Filter: (a IS TRUE)
(5 rows)
explain (costs off) select * from boolpart where a is not true;
@@ -1117,10 +1006,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 +1078,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 +1099,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 +1174,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 +1198,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 +1208,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 +1220,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 +1232,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 +1251,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 +1275,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 +1289,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 +1650,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 +1661,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 +1822,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 +1843,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 +1861,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 +1889,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 +1900,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 +2162,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 +2192,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 +2229,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 +2288,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 +2826,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 +3023,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
@@ -3221,14 +3033,13 @@ explain (costs off) select * from pp_arrpart where a = '{1, 2}';
(2 rows)
explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------
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)
+ Filter: (a = '{4,5}'::integer[])
+(4 rows)
explain (costs off) update pp_arrpart set a = a where a = '{1}';
QUERY PLAN
@@ -3236,8 +3047,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 +3055,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 +3105,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 +3125,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 +3144,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 +3164,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 +3172,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 +3180,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 +3189,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 +3200,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 +3211,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;
@@ -3553,15 +3353,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)
@@ -3782,10 +3582,10 @@ create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2,
-- Don't call get_steps_using_prefix() with the last partition key b plus
-- an empty prefix
explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a';
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Seq Scan on rp_prefix_test1_p1 rp_prefix_test1
- Filter: ((a <= 1) AND ((b)::text = 'a'::text))
+ Filter: ((b)::text = 'a'::text)
(2 rows)
create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c);
@@ -3797,8 +3597,7 @@ explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >
QUERY PLAN
------------------------------------------------
Seq Scan on rp_prefix_test2_p1 rp_prefix_test2
- Filter: ((a <= 1) AND (c >= 0) AND (b = 1))
-(2 rows)
+(1 row)
create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d);
create table rp_prefix_test3_p1 partition of rp_prefix_test3 for values from (1, 1, 1, 0) to (1, 1, 1, 10);
@@ -3806,21 +3605,19 @@ create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2,
-- Test that get_steps_using_prefix() handles a prefix that contains multiple
-- clauses for the partition key b (ie, b >= 1 and b >= 2)
explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b >= 2 and c >= 2 and d >= 0;
- QUERY PLAN
---------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Seq Scan on rp_prefix_test3_p2 rp_prefix_test3
- Filter: ((a >= 1) AND (b >= 1) AND (b >= 2) AND (c >= 2) AND (d >= 0))
-(2 rows)
+(1 row)
-- Test that get_steps_using_prefix() handles a prefix that contains multiple
-- clauses for the partition key b (ie, b >= 1 and b = 2) (This also tests
-- that the caller arranges clauses in that prefix in the required order)
explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b = 2 and c = 2 and d >= 0;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Seq Scan on rp_prefix_test3_p2 rp_prefix_test3
- Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (b = 2) AND (c = 2))
-(2 rows)
+(1 row)
create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a part_test_int4_ops, b
part_test_int4_ops,c part_test_int4_ops, d part_test_int4_ops);
create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 2, remainder 0);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 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.out
index 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;
Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
From
Masahiko Sawada
Date:
Hi, On Fri, Nov 13, 2020 at 5:14 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > I started looking through this patch. I really quite dislike solving > this via a kluge in indxpath.c. There are multiple disadvantages > to that: > > * It only helps for the very specific problem of redundant bitmap > index scans, whereas the problem of applying redundant qual checks > in partition scans seems pretty general. > > * It's not unlikely that this will end up trying to make the same > proof multiple times (and the lack of any way to turn that off, > through constraint_exclusion or some other knob, isn't too cool). > > * It does nothing to fix rowcount estimates in the light of the > knowledge that some of the restriction clauses are no-ops. Now, > if we have up-to-date stats we'll probably manage to come out with > an appropriate 0 or 1 selectivity anyway, but we might not have those. > In any case, spending significant effort to estimate a selectivity > when some other part of the code has taken the trouble to *prove* the > clause true or false seems very undesirable. > > * I'm not even convinced that the logic is correct, specifically that > it's okay to just "continue" if we refute the OR clause. That seems > likely to break generate_bitmap_or_paths' surrounding loop logic about > "We must be able to match at least one index to each of the arms of > the OR". At least, if that still works it requires more than zero > commentary about why. > > > So I like much better the idea of Konstantin's old patch, that we modify > the rel's baserestrictinfo list by removing quals that we can prove > true. We could extend that to solve the bitmapscan problem by removing > OR arms that we can prove false. So I started to review that patch more > carefully, and after awhile realized that it has a really fundamental > problem: it is trying to use CHECK predicates to prove WHERE clauses. > But we don't know that CHECK predicates are true, only that they are > not-false, and there is no proof mode in predtest.c that will allow > proving some clauses true based only on other ones being not-false. > > We can salvage something by restricting the input quals to be only > partition quals, since those are built to be guaranteed-true-or-false; > we can assume they don't yield NULL. There's a hole in that for > hashing, as I noted elsewhere, but we'll fail to prove anything anyway > from a satisfies_hash_partition() qual. (In principle we could also use > attnotnull quals, which also have that property. But I'm dubious that > that will help often enough to be worth the extra cycles for predtest.c > to process them.) > > So after a bit of coding I had the attached. This follows Konstantin's > original patch in letting relation_excluded_by_constraints() change > the baserestrictinfo list. I read the comments in the older thread > about people not liking that, and I can see the point. But I'm not > convinced that the later iterations of the patch were an improvement, > because (a) the call locations for > remove_restrictions_implied_by_constraints() seemed pretty random, and > (b) it seems necessary to have relation_excluded_by_constraints() and > remove_restrictions_implied_by_constraints() pretty much in bed with > each other if we don't want to duplicate constraint-fetching work. > Note the comment on get_relation_constraints() that it's called at > most once per relation; that's not something I particularly desire > to give up, because a relcache open isn't terribly cheap. Also > (c) I think it's important that there be a way to suppress this > overhead when it's not useful. In the patch as attached, turning off > constraint_exclusion does that since relation_excluded_by_constraints() > falls out before getting to the new code. If we make > remove_restrictions_implied_by_constraints() independent then it > will need some possibly-quite-duplicative logic to check > constraint_exclusion. (Of course, if we'd rather condition this > on some other GUC then that argument falls down. But I think we > need something.) So, I'm not dead set on this code structure, but > I haven't seen one I like better. > > Anyway, this seems to work, and if the regression test changes are > any guide then it may fire often enough in the real world to be useful. > Nonetheless, I'm concerned about performance, because predtest.c is a > pretty expensive thing and there will be a lot of cases where the work > is useless. I did a quick check using pgbench's option to partition > the tables, and observed that the -S (select only) test case seemed to > get about 2.5% slower with the patch than without. That's not far > outside the noise floor, so maybe it's not real, but if it is real then > it seems pretty disastrous. Perhaps we could avoid that problem by > removing the "predicate_implied_by" cases and only trying the > "predicate_refuted_by" case, so that no significant time is added > unless you've got an OR restriction clause on a partitioned table. > That seems like it'd lose a lot of the benefit though :-(. > > So I'm not sure where to go from here. Thoughts? Anyone else > care to run some performance tests? Status update for a commitfest entry. Reading through the discussion, several patches have been proposed and it has been inactive for almost 2 months. Does anyone listed as the author plan to work on this patch? It looks like we're waiting for some reviews on the patch including from the performance perspective but this patch entry has been set to "Waiting on Author" since 2021-01-12. If no one works on this and it's really waiting on the author, I'm going to set it to "Returned with Feedback", barring objections. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/
Re: avoid bitmapOR-ing indexes with scan condition inconsistent with partition constraint
From
Masahiko Sawada
Date:
On Mon, Feb 1, 2021 at 12:09 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote: > > Hi, > > On Fri, Nov 13, 2020 at 5:14 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > > I started looking through this patch. I really quite dislike solving > > this via a kluge in indxpath.c. There are multiple disadvantages > > to that: > > > > * It only helps for the very specific problem of redundant bitmap > > index scans, whereas the problem of applying redundant qual checks > > in partition scans seems pretty general. > > > > * It's not unlikely that this will end up trying to make the same > > proof multiple times (and the lack of any way to turn that off, > > through constraint_exclusion or some other knob, isn't too cool). > > > > * It does nothing to fix rowcount estimates in the light of the > > knowledge that some of the restriction clauses are no-ops. Now, > > if we have up-to-date stats we'll probably manage to come out with > > an appropriate 0 or 1 selectivity anyway, but we might not have those. > > In any case, spending significant effort to estimate a selectivity > > when some other part of the code has taken the trouble to *prove* the > > clause true or false seems very undesirable. > > > > * I'm not even convinced that the logic is correct, specifically that > > it's okay to just "continue" if we refute the OR clause. That seems > > likely to break generate_bitmap_or_paths' surrounding loop logic about > > "We must be able to match at least one index to each of the arms of > > the OR". At least, if that still works it requires more than zero > > commentary about why. > > > > > > So I like much better the idea of Konstantin's old patch, that we modify > > the rel's baserestrictinfo list by removing quals that we can prove > > true. We could extend that to solve the bitmapscan problem by removing > > OR arms that we can prove false. So I started to review that patch more > > carefully, and after awhile realized that it has a really fundamental > > problem: it is trying to use CHECK predicates to prove WHERE clauses. > > But we don't know that CHECK predicates are true, only that they are > > not-false, and there is no proof mode in predtest.c that will allow > > proving some clauses true based only on other ones being not-false. > > > > We can salvage something by restricting the input quals to be only > > partition quals, since those are built to be guaranteed-true-or-false; > > we can assume they don't yield NULL. There's a hole in that for > > hashing, as I noted elsewhere, but we'll fail to prove anything anyway > > from a satisfies_hash_partition() qual. (In principle we could also use > > attnotnull quals, which also have that property. But I'm dubious that > > that will help often enough to be worth the extra cycles for predtest.c > > to process them.) > > > > So after a bit of coding I had the attached. This follows Konstantin's > > original patch in letting relation_excluded_by_constraints() change > > the baserestrictinfo list. I read the comments in the older thread > > about people not liking that, and I can see the point. But I'm not > > convinced that the later iterations of the patch were an improvement, > > because (a) the call locations for > > remove_restrictions_implied_by_constraints() seemed pretty random, and > > (b) it seems necessary to have relation_excluded_by_constraints() and > > remove_restrictions_implied_by_constraints() pretty much in bed with > > each other if we don't want to duplicate constraint-fetching work. > > Note the comment on get_relation_constraints() that it's called at > > most once per relation; that's not something I particularly desire > > to give up, because a relcache open isn't terribly cheap. Also > > (c) I think it's important that there be a way to suppress this > > overhead when it's not useful. In the patch as attached, turning off > > constraint_exclusion does that since relation_excluded_by_constraints() > > falls out before getting to the new code. If we make > > remove_restrictions_implied_by_constraints() independent then it > > will need some possibly-quite-duplicative logic to check > > constraint_exclusion. (Of course, if we'd rather condition this > > on some other GUC then that argument falls down. But I think we > > need something.) So, I'm not dead set on this code structure, but > > I haven't seen one I like better. > > > > Anyway, this seems to work, and if the regression test changes are > > any guide then it may fire often enough in the real world to be useful. > > Nonetheless, I'm concerned about performance, because predtest.c is a > > pretty expensive thing and there will be a lot of cases where the work > > is useless. I did a quick check using pgbench's option to partition > > the tables, and observed that the -S (select only) test case seemed to > > get about 2.5% slower with the patch than without. That's not far > > outside the noise floor, so maybe it's not real, but if it is real then > > it seems pretty disastrous. Perhaps we could avoid that problem by > > removing the "predicate_implied_by" cases and only trying the > > "predicate_refuted_by" case, so that no significant time is added > > unless you've got an OR restriction clause on a partitioned table. > > That seems like it'd lose a lot of the benefit though :-(. > > > > So I'm not sure where to go from here. Thoughts? Anyone else > > care to run some performance tests? > > Status update for a commitfest entry. > > Reading through the discussion, several patches have been proposed and > it has been inactive for almost 2 months. Does anyone listed as the > author plan to work on this patch? It looks like we're waiting for > some reviews on the patch including from the performance perspective > but this patch entry has been set to "Waiting on Author" since > 2021-01-12. If no one works on this and it's really waiting on the > author, I'm going to set it to "Returned with Feedback", barring > objections. I've moved this patch to "Returned with Feedback". Depending on timing, this may be reversable, so let us know if there are extenuating circumstances. In any case, you are welcome to address the feedback you have received, and resubmit the patch to the next CommitFest. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/