On 04.12.2017 19:44, Alvaro Herrera wrote:
Konstantin Knizhnik wrote:
On 30.11.2017 05:16, Michael Paquier wrote:
On Mon, Nov 6, 2017 at 10:13 PM, Konstantin Knizhnik
<k.knizh...@postgrespro.ru> wrote:
Concerning broken partition_join test: it is "expected" failure: my patch
removes from the plans redundant checks.
So the only required action is to update expected file with results.
Attached please find updated patch.
The last patch version has conflicts in regression tests and did not
get any reviews. Please provide a rebased version. I am moving the
patch to next CF with waiting on author as status. Thanks!
Rebased patch is attached.
I don't think this is a rebase on the previously posted patch ... it's
about 10x as big and appears to be a thorough rewrite of the entire
optimizer.
Or, sorry. I really occasionally committed in this branch patch for
aggregate push down.
Correct reabased patch is attached.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bce3348..6a7e7fb 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -626,12 +626,12 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- Nu
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NULL))
(3 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL and c3 is not null; -- NullTest
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NOT NULL))
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c3 IS NOT NULL))
(3 rows)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 1df1e3a..c421530 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -292,7 +292,7 @@ RESET enable_nestloop;
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL and c3 is not null; -- NullTest
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE 1 = c1!; -- OpExpr(r)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 44f6b03..c7bf118 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -345,6 +345,7 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
switch (rel->rtekind)
{
case RTE_RELATION:
+ remove_restrictions_implied_by_constraints(root, rel, rte);
if (rte->relkind == RELKIND_FOREIGN_TABLE)
{
/* Foreign table */
@@ -1130,6 +1131,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
set_dummy_rel_pathlist(childrel);
continue;
}
+ remove_restrictions_implied_by_constraints(root, childrel, childRTE);
/* CE failed, so finish copying/modifying join quals. */
childrel->joininfo = (List *)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index f743871..f763a97 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1466,6 +1466,51 @@ relation_excluded_by_constraints(PlannerInfo *root,
return false;
}
+/*
+ * Remove from restrictions list items implied by table constraints
+ */
+void remove_restrictions_implied_by_constraints(PlannerInfo *root,
+ RelOptInfo *rel, RangeTblEntry *rte)
+{
+ List *constraint_pred;
+ List *safe_constraints = NIL;
+ List *safe_restrictions = NIL;
+ ListCell *lc;
+
+ if (rte->rtekind != RTE_RELATION || rte->inh)
+ return;
+
+ /*
+ * OK to fetch the constraint expressions. Include "col IS NOT NULL"
+ * expressions for attnotnull columns, in case we can refute those.
+ */
+ constraint_pred = get_relation_constraints(root, rte->relid, rel, true);
+
+ /*
+ * We do not currently enforce that CHECK constraints contain only
+ * immutable functions, so it's necessary to check here. We daren't draw
+ * conclusions from plan-time evaluation of non-immutable functions. Since
+ * they're ANDed, we can just ignore any mutable constraints in the list,
+ * and reason about the rest.
+ */
+ foreach(lc, constraint_pred)
+ {
+ Node *pred = (Node*) lfirst(lc);
+
+ if (!contain_mutable_functions(pred))
+ safe_constraints = lappend(safe_constraints, pred);
+ }
+
+ foreach(lc, rel->baserestrictinfo)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+ if (!predicate_implied_by(list_make1(rinfo->clause), safe_constraints, false)) {
+ safe_restrictions = lappend(safe_restrictions, rinfo);
+ }
+ }
+ rel->baserestrictinfo = safe_restrictions;
+}
+
/*
* build_physical_tlist
diff --git a/src/backend/optimizer/util/predtest.c b/src/backend/optimizer/util/predtest.c
index 134460c..47cd4c9 100644
--- a/src/backend/optimizer/util/predtest.c
+++ b/src/backend/optimizer/util/predtest.c
@@ -17,6 +17,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_operator.h"
#include "executor/executor.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
@@ -1407,6 +1408,13 @@ static const StrategyNumber BT_refute_table[6][6] = {
{none, none, BTEQ, none, none, none} /* NE */
};
+#define Int2LessOperator 95
+#define Int2LessOrEqualOperator 522
+#define Int4LessOrEqualOperator 523
+#define Int8LessOrEqualOperator 414
+#define DateLessOrEqualOperator 1096
+#define DateLessOperator 1095
+
/*
* operator_predicate_proof
@@ -1600,6 +1608,17 @@ operator_predicate_proof(Expr *predicate, Node *clause, bool refute_it)
if (clause_const->constisnull)
return false;
+ if (!refute_it
+ && ((pred_op == Int4LessOrEqualOperator && clause_op == Int4LessOperator)
+ || (pred_op == Int8LessOrEqualOperator && clause_op == Int8LessOperator)
+ || (pred_op == Int2LessOrEqualOperator && clause_op == Int2LessOperator)
+ || (pred_op == DateLessOrEqualOperator && clause_op == DateLessOperator))
+ && pred_const->constbyval && clause_const->constbyval
+ && pred_const->constvalue + 1 == clause_const->constvalue)
+ {
+ return true;
+ }
+
/*
* Lookup the constant-comparison operator using the system catalogs and
* the operator implication tables.
diff --git a/src/include/optimizer/plancat.h b/src/include/optimizer/plancat.h
index 71f0faf..09e8927 100644
--- a/src/include/optimizer/plancat.h
+++ b/src/include/optimizer/plancat.h
@@ -38,6 +38,9 @@ extern int32 get_relation_data_width(Oid relid, int32 *attr_widths);
extern bool relation_excluded_by_constraints(PlannerInfo *root,
RelOptInfo *rel, RangeTblEntry *rte);
+extern void remove_restrictions_implied_by_constraints(PlannerInfo *root,
+ RelOptInfo *rel, RangeTblEntry *rte);
+
extern List *build_physical_tlist(PlannerInfo *root, RelOptInfo *rel);
extern bool has_unique_index(RelOptInfo *rel, AttrNumber attno);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index fac7b62..f450209 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1690,34 +1690,30 @@ explain (costs off) select * from list_parted where a is not null;
---------------------------------
Append
-> Seq Scan on part_ab_cd
- Filter: (a IS NOT NULL)
-> Seq Scan on part_ef_gh
- Filter: (a IS NOT NULL)
-> Seq Scan on part_null_xy
Filter: (a IS NOT NULL)
-(7 rows)
+(5 rows)
explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
QUERY PLAN
----------------------------------------------------------
Append
-> Seq Scan on part_ab_cd
- Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-> Seq Scan on part_ef_gh
Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-(5 rows)
+(4 rows)
explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
QUERY PLAN
---------------------------------------------------------------------------------------
Append
-> Seq Scan on part_ab_cd
- Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-> Seq Scan on part_ef_gh
Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-> Seq Scan on part_null_xy
Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-(7 rows)
+(6 rows)
explain (costs off) select * from list_parted where a = 'ab';
QUERY PLAN
@@ -1770,30 +1766,25 @@ explain (costs off) select * from range_list_parted where a = 5;
(5 rows)
explain (costs off) select * from range_list_parted where b = 'ab';
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+----------------------------------
Append
-> Seq Scan on part_1_10_ab
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_10_20_ab
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_21_30_ab
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_40_inf_ab
- Filter: (b = 'ab'::bpchar)
-(9 rows)
+(5 rows)
explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab');
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------
Append
-> Seq Scan on part_1_10_ab
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
+ Filter: (a >= 3)
-> Seq Scan on part_10_20_ab
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-> Seq Scan on part_21_30_ab
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-(7 rows)
+ Filter: (a <= 23)
+(6 rows)
/* Should select no rows because range partition key cannot be null */
explain (costs off) select * from range_list_parted where a is null;
@@ -1809,44 +1800,34 @@ explain (costs off) select * from range_list_parted where b is null;
------------------------------------
Append
-> Seq Scan on part_40_inf_null
- Filter: (b IS NULL)
-(3 rows)
+(2 rows)
explain (costs off) select * from range_list_parted where a is not null and a < 67;
- QUERY PLAN
-------------------------------------------------
+ QUERY PLAN
+------------------------------------
Append
-> Seq Scan on part_1_10_ab
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_1_10_cd
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_10_20_ab
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_10_20_cd
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_21_30_ab
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_21_30_cd
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_40_inf_ab
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Filter: (a < 67)
-> Seq Scan on part_40_inf_cd
- Filter: ((a IS NOT NULL) AND (a < 67))
+ Filter: (a < 67)
-> Seq Scan on part_40_inf_null
- Filter: ((a IS NOT NULL) AND (a < 67))
-(19 rows)
+ Filter: (a < 67)
+(13 rows)
explain (costs off) select * from range_list_parted where a >= 30;
QUERY PLAN
------------------------------------
Append
-> Seq Scan on part_40_inf_ab
- Filter: (a >= 30)
-> Seq Scan on part_40_inf_cd
- Filter: (a >= 30)
-> Seq Scan on part_40_inf_null
- Filter: (a >= 30)
-(7 rows)
+(4 rows)
drop table list_parted;
drop table range_list_parted;
@@ -1887,7 +1868,7 @@ explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scan
-> Seq Scan on mcrparted1
Filter: ((a = 10) AND (abs(b) = 5))
-> Seq Scan on mcrparted2
- Filter: ((a = 10) AND (abs(b) = 5))
+ Filter: (abs(b) = 5)
-> Seq Scan on mcrparted_def
Filter: ((a = 10) AND (abs(b) = 5))
(7 rows)
@@ -1917,25 +1898,20 @@ explain (costs off) select * from mcrparted where a > -1; -- scans all partition
-> Seq Scan on mcrparted0
Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted1
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted2
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted3
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted4
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted5
- Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted_def
Filter: (a > '-1'::integer)
-(15 rows)
+(10 rows)
explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------
Append
-> Seq Scan on mcrparted4
- Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
+ Filter: ((c > 10) AND (abs(b) = 10))
(3 rows)
explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def
@@ -1945,7 +1921,7 @@ explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mc
-> Seq Scan on mcrparted3
Filter: ((c > 20) AND (a = 20))
-> Seq Scan on mcrparted4
- Filter: ((c > 20) AND (a = 20))
+ Filter: (c > 20)
-> Seq Scan on mcrparted5
Filter: ((c > 20) AND (a = 20))
-> Seq Scan on mcrparted_def
@@ -1968,13 +1944,13 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
-> Merge Append
Sort Key: parted_minmax1.a
-> Index Only Scan using parted_minmax1i on parted_minmax1
- Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
+ Index Cond: (b = '12345'::text)
InitPlan 2 (returns $1)
-> Limit
-> Merge Append
Sort Key: parted_minmax1_1.a DESC
-> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax1_1
- Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
+ Index Cond: (b = '12345'::text)
(13 rows)
select min(a), max(a) from parted_minmax where b = '12345';
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 27ab852..9ed3c5e 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -223,7 +223,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JO
-> Hash Left Join
Hash Cond: (prt1_p1.a = b)
-> Seq Scan on prt1_p1
- Filter: ((a < 450) AND (b = 0))
+ Filter: (b = 0)
-> Hash
-> Result
One-Time Filter: false
@@ -261,7 +261,6 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JO
Hash Cond: (prt1_p1.a = b)
Filter: ((prt1_p1.b = 0) OR (a = 0))
-> Seq Scan on prt1_p1
- Filter: (a < 450)
-> Hash
-> Result
One-Time Filter: false
@@ -277,11 +276,10 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JO
Hash Cond: (prt2_p3.b = a)
Filter: ((b = 0) OR (prt2_p3.a = 0))
-> Seq Scan on prt2_p3
- Filter: (b > 250)
-> Hash
-> Result
One-Time Filter: false
-(27 rows)
+(25 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b;
a | c | b | c
@@ -1019,7 +1017,7 @@ SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
-> Sort
Sort Key: prt1_p1.a
-> Seq Scan on prt1_p1
- Filter: ((a < 450) AND (b = 0))
+ Filter: (b = 0)
-> Sort
Sort Key: b
-> Result
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index aabb024..5a1c40a 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -25,22 +25,20 @@ explain (costs off) select * from lp where a > 'a' and a < 'd';
-----------------------------------------------------------
Append
-> Seq Scan on lp_bc
- Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
-> Seq Scan on lp_default
Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
-(5 rows)
+(4 rows)
explain (costs off) select * from lp where a > 'a' and a <= 'd';
QUERY PLAN
------------------------------------------------------------
Append
-> Seq Scan on lp_ad
- Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
+ Filter: (a > 'a'::bpchar)
-> Seq Scan on lp_bc
- Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
-> Seq Scan on lp_default
Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
-(7 rows)
+(6 rows)
explain (costs off) select * from lp where a = 'a';
QUERY PLAN
@@ -59,28 +57,22 @@ explain (costs off) select * from lp where 'a' = a; /* commuted */
(3 rows)
explain (costs off) select * from lp where a is not null;
- QUERY PLAN
----------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on lp_ad
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_bc
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_ef
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_g
- Filter: (a IS NOT NULL)
-> Seq Scan on lp_default
- Filter: (a IS NOT NULL)
-(11 rows)
+(6 rows)
explain (costs off) select * from lp where a is null;
- QUERY PLAN
------------------------------
+ QUERY PLAN
+---------------------------
Append
-> Seq Scan on lp_null
- Filter: (a IS NULL)
-(3 rows)
+(2 rows)
explain (costs off) select * from lp where a = 'a' or a = 'c';
QUERY PLAN
@@ -93,56 +85,44 @@ explain (costs off) select * from lp where a = 'a' or a = 'c';
(5 rows)
explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------
Append
-> Seq Scan on lp_ad
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
-> Seq Scan on lp_bc
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
(5 rows)
explain (costs off) select * from lp where a <> 'g';
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on lp_ad
- Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_bc
- Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_ef
- Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_default
- Filter: (a <> 'g'::bpchar)
-(9 rows)
+(5 rows)
explain (costs off) select * from lp where a <> 'a' and a <> 'd';
- QUERY PLAN
--------------------------------------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on lp_bc
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_ef
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_g
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_default
- Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-(9 rows)
+(5 rows)
explain (costs off) select * from lp where a not in ('a', 'd');
- QUERY PLAN
-------------------------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on lp_bc
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_ef
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_g
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_default
- Filter: (a <> ALL ('{a,d}'::bpchar[]))
-(9 rows)
+(5 rows)
-- collation matches the partitioning collation, pruning works
create table coll_pruning (a text collate "C") partition by list (a);
@@ -150,12 +130,11 @@ create table coll_pruning_a partition of coll_pruning for values in ('a');
create table coll_pruning_b partition of coll_pruning for values in ('b');
create table coll_pruning_def partition of coll_pruning default;
explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C";
- QUERY PLAN
----------------------------------------------
+ QUERY PLAN
+----------------------------------
Append
-> Seq Scan on coll_pruning_a
- Filter: (a = 'a'::text COLLATE "C")
-(3 rows)
+(2 rows)
-- collation doesn't match the partitioning collation, no pruning occurs
explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX";
@@ -192,32 +171,29 @@ create table rlp5 partition of rlp for values from (31) to (maxvalue) partition
create table rlp5_default partition of rlp5 default;
create table rlp5_1 partition of rlp5 for values from (31) to (40);
explain (costs off) select * from rlp where a < 1;
- QUERY PLAN
--------------------------
+ QUERY PLAN
+------------------------
Append
-> Seq Scan on rlp1
- Filter: (a < 1)
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where 1 > a; /* commuted */
- QUERY PLAN
--------------------------
+ QUERY PLAN
+------------------------
Append
-> Seq Scan on rlp1
- Filter: (1 > a)
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where a <= 1;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a <= 1)
-> Seq Scan on rlp2
Filter: (a <= 1)
-> Seq Scan on rlp_default_default
Filter: (a <= 1)
-(7 rows)
+(6 rows)
explain (costs off) select * from rlp where a = 1;
QUERY PLAN
@@ -276,65 +252,47 @@ explain (costs off) select * from rlp where a <= 10;
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a <= 10)
-> Seq Scan on rlp2
- Filter: (a <= 10)
-> Seq Scan on rlp_default_10
- Filter: (a <= 10)
-> Seq Scan on rlp_default_default
Filter: (a <= 10)
-(9 rows)
+(6 rows)
explain (costs off) select * from rlp where a > 10;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp3abcd
- Filter: (a > 10)
-> Seq Scan on rlp3efgh
- Filter: (a > 10)
-> Seq Scan on rlp3nullxy
- Filter: (a > 10)
-> Seq Scan on rlp3_default
- Filter: (a > 10)
-> Seq Scan on rlp4_1
- Filter: (a > 10)
-> Seq Scan on rlp4_2
- Filter: (a > 10)
-> Seq Scan on rlp4_default
- Filter: (a > 10)
-> Seq Scan on rlp5_1
- Filter: (a > 10)
-> Seq Scan on rlp5_default
- Filter: (a > 10)
-> Seq Scan on rlp_default_30
- Filter: (a > 10)
-> Seq Scan on rlp_default_default
Filter: (a > 10)
-(23 rows)
+(13 rows)
explain (costs off) select * from rlp where a < 15;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a < 15)
-> Seq Scan on rlp2
- Filter: (a < 15)
-> Seq Scan on rlp_default_10
- Filter: (a < 15)
-> Seq Scan on rlp_default_default
Filter: (a < 15)
-(9 rows)
+(6 rows)
explain (costs off) select * from rlp where a <= 15;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a <= 15)
-> Seq Scan on rlp2
- Filter: (a <= 15)
-> Seq Scan on rlp3abcd
Filter: (a <= 15)
-> Seq Scan on rlp3efgh
@@ -344,10 +302,9 @@ explain (costs off) select * from rlp where a <= 15;
-> Seq Scan on rlp3_default
Filter: (a <= 15)
-> Seq Scan on rlp_default_10
- Filter: (a <= 15)
-> Seq Scan on rlp_default_default
Filter: (a <= 15)
-(17 rows)
+(14 rows)
explain (costs off) select * from rlp where a > 15 and b = 'ab';
QUERY PLAN
@@ -356,17 +313,17 @@ explain (costs off) select * from rlp where a > 15 and b = 'ab';
-> Seq Scan on rlp3abcd
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp4_1
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp4_2
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp4_default
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp5_1
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp5_default
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_30
- Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ Filter: ((b)::text = 'ab'::text)
-> Seq Scan on rlp_default_default
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
(17 rows)
@@ -424,13 +381,13 @@ explain (costs off) select * from rlp where a = 16 and b is not null;
------------------------------------------------
Append
-> Seq Scan on rlp3abcd
- Filter: ((b IS NOT NULL) AND (a = 16))
+ Filter: (a = 16)
-> Seq Scan on rlp3efgh
- Filter: ((b IS NOT NULL) AND (a = 16))
+ Filter: (a = 16)
-> Seq Scan on rlp3nullxy
Filter: ((b IS NOT NULL) AND (a = 16))
-> Seq Scan on rlp3_default
- Filter: ((b IS NOT NULL) AND (a = 16))
+ Filter: (a = 16)
(9 rows)
explain (costs off) select * from rlp where a is null;
@@ -438,96 +395,67 @@ explain (costs off) select * from rlp where a is null;
------------------------------------
Append
-> Seq Scan on rlp_default_null
- Filter: (a IS NULL)
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where a is not null;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp2
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3abcd
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3efgh
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3nullxy
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp3_default
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_1
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_2
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_default
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp5_1
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp5_default
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_10
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_30
- Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_default
- Filter: (a IS NOT NULL)
-(29 rows)
+(15 rows)
explain (costs off) select * from rlp where a > 30;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp5_1
- Filter: (a > 30)
-> Seq Scan on rlp5_default
- Filter: (a > 30)
-> Seq Scan on rlp_default_default
Filter: (a > 30)
-(7 rows)
+(5 rows)
explain (costs off) select * from rlp where a = 30; /* only default is scanned */
QUERY PLAN
----------------------------------
Append
-> Seq Scan on rlp_default_30
- Filter: (a = 30)
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where a <= 31;
QUERY PLAN
---------------------------------------
Append
-> Seq Scan on rlp1
- Filter: (a <= 31)
-> Seq Scan on rlp2
- Filter: (a <= 31)
-> Seq Scan on rlp3abcd
- Filter: (a <= 31)
-> Seq Scan on rlp3efgh
- Filter: (a <= 31)
-> Seq Scan on rlp3nullxy
- Filter: (a <= 31)
-> Seq Scan on rlp3_default
- Filter: (a <= 31)
-> Seq Scan on rlp4_1
- Filter: (a <= 31)
-> Seq Scan on rlp4_2
- Filter: (a <= 31)
-> Seq Scan on rlp4_default
- Filter: (a <= 31)
-> Seq Scan on rlp5_1
Filter: (a <= 31)
-> Seq Scan on rlp5_default
Filter: (a <= 31)
-> Seq Scan on rlp_default_10
- Filter: (a <= 31)
-> Seq Scan on rlp_default_30
- Filter: (a <= 31)
-> Seq Scan on rlp_default_default
Filter: (a <= 31)
-(29 rows)
+(18 rows)
explain (costs off) select * from rlp where a = 1 or a = 7;
QUERY PLAN
@@ -572,9 +500,9 @@ explain (costs off) select * from rlp where a > 20 and a < 27;
-----------------------------------------
Append
-> Seq Scan on rlp4_1
- Filter: ((a > 20) AND (a < 27))
+ Filter: (a > 20)
-> Seq Scan on rlp4_2
- Filter: ((a > 20) AND (a < 27))
+ Filter: (a < 27)
-> Seq Scan on rlp4_default
Filter: ((a > 20) AND (a < 27))
(7 rows)
@@ -594,51 +522,37 @@ explain (costs off) select * from rlp where a >= 29;
-> Seq Scan on rlp4_default
Filter: (a >= 29)
-> Seq Scan on rlp5_1
- Filter: (a >= 29)
-> Seq Scan on rlp5_default
- Filter: (a >= 29)
-> Seq Scan on rlp_default_30
- Filter: (a >= 29)
-> Seq Scan on rlp_default_default
Filter: (a >= 29)
-(11 rows)
+(8 rows)
-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+----------------------------------
Append
-> Seq Scan on rlp_default_10
- Filter: ((a > 1) AND (a = 10))
-(3 rows)
+(2 rows)
explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
QUERY PLAN
-----------------------------------------
Append
-> Seq Scan on rlp3abcd
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3efgh
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3nullxy
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3_default
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_1
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_2
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_default
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp5_1
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp5_default
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp_default_30
- Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp_default_default
Filter: ((a > 1) AND (a >= 15))
-(23 rows)
+(13 rows)
explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
QUERY PLAN
@@ -727,28 +641,23 @@ explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
-> Seq Scan on mc3p1
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p2
- Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p3
- Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p4
- Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
+ Filter: (abs(b) <= 35)
-> Seq Scan on mc3p_default
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-(11 rows)
+(9 rows)
explain (costs off) select * from mc3p where a > 10;
QUERY PLAN
--------------------------------
Append
-> Seq Scan on mc3p5
- Filter: (a > 10)
-> Seq Scan on mc3p6
- Filter: (a > 10)
-> Seq Scan on mc3p7
- Filter: (a > 10)
-> Seq Scan on mc3p_default
Filter: (a > 10)
-(9 rows)
+(6 rows)
explain (costs off) select * from mc3p where a >= 10;
QUERY PLAN
@@ -757,43 +666,36 @@ explain (costs off) select * from mc3p where a >= 10;
-> Seq Scan on mc3p1
Filter: (a >= 10)
-> Seq Scan on mc3p2
- Filter: (a >= 10)
-> Seq Scan on mc3p3
- Filter: (a >= 10)
-> Seq Scan on mc3p4
- Filter: (a >= 10)
-> Seq Scan on mc3p5
- Filter: (a >= 10)
-> Seq Scan on mc3p6
- Filter: (a >= 10)
-> Seq Scan on mc3p7
- Filter: (a >= 10)
-> Seq Scan on mc3p_default
Filter: (a >= 10)
-(17 rows)
+(11 rows)
explain (costs off) select * from mc3p where a < 10;
QUERY PLAN
--------------------------------
Append
-> Seq Scan on mc3p0
- Filter: (a < 10)
-> Seq Scan on mc3p1
Filter: (a < 10)
-> Seq Scan on mc3p_default
Filter: (a < 10)
-(7 rows)
+(6 rows)
explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10;
QUERY PLAN
-----------------------------------------------
Append
-> Seq Scan on mc3p0
- Filter: ((a <= 10) AND (abs(b) < 10))
+ Filter: (abs(b) < 10)
-> Seq Scan on mc3p1
- Filter: ((a <= 10) AND (abs(b) < 10))
+ Filter: (abs(b) < 10)
-> Seq Scan on mc3p2
- Filter: ((a <= 10) AND (abs(b) < 10))
+ Filter: (abs(b) < 10)
-> Seq Scan on mc3p_default
Filter: ((a <= 10) AND (abs(b) < 10))
(9 rows)
@@ -807,11 +709,11 @@ explain (costs off) select * from mc3p where a = 11 and abs(b) = 0;
(3 rows)
explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100;
- QUERY PLAN
-------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Append
-> Seq Scan on mc3p6
- Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10))
+ Filter: ((c = 100) AND (abs(b) = 10))
(3 rows)
explain (costs off) select * from mc3p where a > 20;
@@ -831,12 +733,10 @@ explain (costs off) select * from mc3p where a >= 20;
-> Seq Scan on mc3p5
Filter: (a >= 20)
-> Seq Scan on mc3p6
- Filter: (a >= 20)
-> Seq Scan on mc3p7
- Filter: (a >= 20)
-> Seq Scan on mc3p_default
Filter: (a >= 20)
-(9 rows)
+(7 rows)
explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20);
QUERY PLAN
@@ -873,7 +773,6 @@ explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or
-------------------------------------------------------------------------------------------------------------------------------------------------------
Append
-> Seq Scan on mc3p0
- Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p1
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p2
@@ -882,7 +781,7 @@ explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p_default
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-(11 rows)
+(10 rows)
explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
QUERY PLAN
@@ -917,12 +816,11 @@ explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 a
-> Seq Scan on mc3p2
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p3
- Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p4
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p_default
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-(13 rows)
+(12 rows)
explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9);
QUERY PLAN
@@ -952,22 +850,18 @@ explain (costs off) select * from mc2p where a < 2;
--------------------------------
Append
-> Seq Scan on mc2p0
- Filter: (a < 2)
-> Seq Scan on mc2p1
- Filter: (a < 2)
-> Seq Scan on mc2p2
- Filter: (a < 2)
-> Seq Scan on mc2p_default
Filter: (a < 2)
-(9 rows)
+(6 rows)
explain (costs off) select * from mc2p where a = 2 and b < 1;
- QUERY PLAN
----------------------------------------
+ QUERY PLAN
+-------------------------
Append
-> Seq Scan on mc2p3
- Filter: ((b < 1) AND (a = 2))
-(3 rows)
+(2 rows)
explain (costs off) select * from mc2p where a > 1;
QUERY PLAN
@@ -976,14 +870,11 @@ explain (costs off) select * from mc2p where a > 1;
-> Seq Scan on mc2p2
Filter: (a > 1)
-> Seq Scan on mc2p3
- Filter: (a > 1)
-> Seq Scan on mc2p4
- Filter: (a > 1)
-> Seq Scan on mc2p5
- Filter: (a > 1)
-> Seq Scan on mc2p_default
Filter: (a > 1)
-(11 rows)
+(8 rows)
explain (costs off) select * from mc2p where a = 1 and b > 1;
QUERY PLAN
@@ -999,14 +890,12 @@ create table boolpart_default partition of boolpart default;
create table boolpart_t partition of boolpart for values in ('true');
create table boolpart_f partition of boolpart for values in ('false');
explain (costs off) select * from boolpart where a in (true, false);
- QUERY PLAN
-------------------------------------------------
+ QUERY PLAN
+------------------------------
Append
-> Seq Scan on boolpart_f
- Filter: (a = ANY ('{t,f}'::boolean[]))
-> Seq Scan on boolpart_t
- Filter: (a = ANY ('{t,f}'::boolean[]))
-(5 rows)
+(3 rows)
explain (costs off) select * from boolpart where a = false;
QUERY PLAN
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index b8dcf51..cf0ef42 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -1057,14 +1057,14 @@ NOTICE: f_leak => awesome science fiction
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Append
InitPlan 1 (returns $0)
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction
- Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
(6 rows)
-- pp1 ERROR
@@ -1136,14 +1136,14 @@ NOTICE: f_leak => awesome science fiction
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Append
InitPlan 1 (returns $0)
-> Index Scan using uaccount_pkey on uaccount
Index Cond: (pguser = CURRENT_USER)
-> Seq Scan on part_document_fiction
- Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
+ Filter: ((dlevel <= $0) AND f_leak(dtitle))
(6 rows)
-- viewpoint from regress_rls_carol
