On 11/06/2017 04:27 AM, Thomas Munro wrote:
On Fri, Sep 8, 2017 at 3:58 AM, Konstantin Knizhnik
<k.knizh...@postgrespro.ru> wrote:
Updated version of the patch is attached to this mail.
Also I added support of date type to operator_predicate_proof to be able to
imply (logdate <= '2017-03-31') from (logdate < '2017-04-01') .
Hi Konstantin,
Is there any reason why you don't want to split this into two separate
proposals? One for remove_restrictions_implied_by_constraints() and
one for the operator_predicate_proof() changes.
Your v3 patch breaks the new partition_join test (the recently
committed partition-wise join stuff), as far as I can tell in a good
way. Can you please double check those changes and post an updated
patch?
Hi Thomas.
The primary idea of this patch was to provide more efficient plans for queries
on partitioned tables.
So remove_restrictions_implied_by_constraints() removes redundant predicate
checks.
But it doesn't work for standard Postgres 10 partitioning, because here
constraints are set using intervals with open high boundary and original
version of
operator_predicate_proof() is not able to handle this case.
I have explained this problem in my previous e-mails in this thread.
This is why I have changed operator_predicate_proof() to correctly handle this
case.
If you think this patch should be splitted into two, ok: I can do it.
I just want to notice that without patching operator_predicate_proof() it may
give not positive effect for standard partitioning,
which I expect to be the most popular use case where this optimization may have
an effect.
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.
--
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 4339bbf..0931af1 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 ddfec79..878bfc7 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 a5c1b68..082d1cc 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -346,6 +346,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 */
@@ -1312,6 +1313,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 9d35a41..4eb9548 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1456,6 +1456,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 536d24b..f373d92 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 c698faf..43b4b20 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;
@@ -1882,7 +1863,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)
(5 rows)
explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions
@@ -1908,23 +1889,18 @@ 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)
-(13 rows)
+(8 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
@@ -1934,7 +1910,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))
(7 rows)
@@ -1955,13 +1931,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 adf6aed..a4cfe33 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/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index de2ee4d..dee7443 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
