On 29.01.2018 16:24, Konstantin Knizhnik wrote:
On 29.01.2018 07:34, Thomas Munro wrote:
On Sat, Jan 20, 2018 at 5:41 AM, Konstantin Knizhnik
<k.knizh...@postgrespro.ru> wrote:
On 19.01.2018 16:14, Antonin Houska wrote:
you should test the operator B-tree strategy: BTLessStrategyNumber,
BTLessEqualStrategyNumber, etc. The operator string alone does not
tell
enough
about the operator semantics.
The strategy can be found in the pg_amop catalog.
get_op_btree_interpretation() function may be useful, but there may be
something better in utils/cache/lsyscache.c.
Thank you very much.
Shame on me that I didn't notice such solution myself - such
checking of
B-tree strategy is done in the same predtest.c file!
Now checking of predicate clauses compatibility is done in much more
elegant
and general way.
Attached please find new version of the patch.
I wonder if you should create a new index and SysCache entry for
looking up range types by subtype. I'll be interested to see what
others have to say about this range type-based technique -- it seems
clever to me, but I'm not familiar enough with this stuff to say if
there is some other approach you should be using instead.
I think that it is good idea to add caching for range type lookup.
If community think that it will be useful I can try to add such
mechanism.
But it seems to be not so trivial, especially properly handle
invalidations.
I have added caching of element_type->range_type mapping to typcache.c.
But it is not invalidated now if pg_range relation is changed.
Also if there are more than one range types for the specified element
type then first of them is used.
--
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..27f317a 100644
--- a/src/backend/optimizer/util/predtest.c
+++ b/src/backend/optimizer/util/predtest.c
@@ -17,6 +17,9 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_range.h"
+#include "catalog/pg_operator.h"
+#include "access/htup_details.h"
#include "executor/executor.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
@@ -26,6 +29,8 @@
#include "utils/inval.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
+#include "utils/typcache.h"
+#include "utils/rangetypes.h"
/*
@@ -1407,6 +1412,67 @@ static const StrategyNumber BT_refute_table[6][6] = {
{none, none, BTEQ, none, none, none} /* NE */
};
+/*
+ * Contruct range type for the comparison operator:
+ * '<=' -> '(,"$value"]'
+ * '>=' -> '["$value",)'
+ * '<' -> '(,"$value")'
+ * '>'. -> '("$value",)'
+ * otherwise: NULL
+ */
+static RangeType*
+operator_to_range(TypeCacheEntry *typcache, Oid oper, Const* literal)
+{
+ RangeBound lower, upper;
+ List* op_infos = get_op_btree_interpretation(oper);
+ ListCell *lcp;
+
+ lower.lower = true;
+ upper.lower = false;
+ lower.infinite = true;
+ upper.infinite = true;
+
+ foreach(lcp, op_infos)
+ {
+ OpBtreeInterpretation *op_info = lfirst(lcp);
+
+ switch (op_info->strategy)
+ {
+ case BTLessStrategyNumber:
+ lower.infinite = true;
+ lower.inclusive = false;
+ upper.infinite = false;
+ upper.inclusive = false;
+ upper.val = literal->constvalue;
+ break;
+ case BTLessEqualStrategyNumber:
+ lower.infinite = true;
+ lower.inclusive = false;
+ upper.infinite = false;
+ upper.inclusive = true;
+ upper.val = literal->constvalue;
+ break;
+ case BTGreaterStrategyNumber:
+ lower.infinite = false;
+ lower.inclusive = false;
+ lower.val = literal->constvalue;
+ upper.infinite = true;
+ upper.inclusive = false;
+ break;
+ case BTGreaterEqualStrategyNumber:
+ lower.infinite = false;
+ lower.inclusive = true;
+ lower.val = literal->constvalue;
+ upper.infinite = true;
+ upper.inclusive = false;
+ break;
+ default:
+ continue;
+ }
+ break;
+ }
+ return lower.infinite && upper.infinite ? NULL : make_range(typcache, &lower, &upper, false);
+}
/*
* operator_predicate_proof
@@ -1600,6 +1666,18 @@ operator_predicate_proof(Expr *predicate, Node *clause, bool refute_it)
if (clause_const->constisnull)
return false;
+ if (!refute_it && pred_const->consttype == clause_const->consttype)
+ {
+ TypeCacheEntry *typcache = lookup_type_cache(clause_const->consttype, TYPECACHE_RANGE_TYPE)->rng_type;
+ if (typcache != NULL)
+ {
+ RangeType* pred_range = operator_to_range(typcache, pred_op, pred_const);
+ RangeType* clause_range = operator_to_range(typcache, clause_op, clause_const);
+ if (pred_range && clause_range && range_eq_internal(typcache, pred_range, clause_range))
+ return true;
+ }
+ }
+
/*
* Lookup the constant-comparison operator using the system catalogs and
* the operator implication tables.
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index f6450c4..445e26c 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -267,6 +267,7 @@ static int32 NextRecordTypmod = 0; /* number of entries used */
static void load_typcache_tupdesc(TypeCacheEntry *typentry);
static void load_rangetype_info(TypeCacheEntry *typentry);
+static void lookup_range_for_type(TypeCacheEntry *typentry);
static void load_domaintype_info(TypeCacheEntry *typentry);
static int dcs_cmp(const void *a, const void *b);
static void decr_dcc_refcount(DomainConstraintCache *dcc);
@@ -745,6 +746,11 @@ lookup_type_cache(Oid type_id, int flags)
{
load_rangetype_info(typentry);
}
+ if ((flags & TYPECACHE_RANGE_TYPE) &&
+ !(typentry->flags & TYPECACHE_RANGE_TYPE))
+ {
+ lookup_range_for_type(typentry);
+ }
/*
* If requested, get information about a domain type
@@ -802,6 +808,39 @@ load_typcache_tupdesc(TypeCacheEntry *typentry)
}
/*
+ * Get range type for the correspondent scalar type.
+ * This function performs sequential scan in pg_range table.
+ */
+static void
+lookup_range_for_type(TypeCacheEntry *typentry)
+{
+ Relation pgRangeRel;
+ Form_pg_range pgRange;
+ SysScanDesc scan;
+ HeapTuple tuple;
+
+ pgRangeRel = heap_open(RangeRelationId, AccessShareLock);
+
+ scan = systable_beginscan(pgRangeRel, InvalidOid, false,
+ NULL, 0, NULL);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ pgRange = (Form_pg_range) GETSTRUCT(tuple);
+ if (pgRange->rngsubtype == typentry->type_id)
+ {
+ typentry->rng_type = lookup_type_cache(pgRange->rngtypid, TYPECACHE_RANGE_INFO);
+ break;
+ }
+ }
+ systable_endscan(scan);
+ heap_close(pgRangeRel, AccessShareLock);
+
+ typentry->flags |= TYPECACHE_RANGE_TYPE;
+}
+
+
+/*
* load_rangetype_info --- helper routine to set up range type information
*/
static void
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/include/utils/typcache.h b/src/include/utils/typcache.h
index c203dab..701d2c4 100644
--- a/src/include/utils/typcache.h
+++ b/src/include/utils/typcache.h
@@ -95,6 +95,12 @@ typedef struct TypeCacheEntry
FmgrInfo rng_subdiff_finfo; /* difference function, if any */
/*
+ * Get range type for the element type when TYPECACHE_RANGE_TYPE is requested.
+ * This infotmation is obtained by scan of pg_range table. First match is taken./
+ */
+ struct TypeCacheEntry *rng_type;
+
+ /*
* Domain's base type and typmod if it's a domain type. Zeroes if not
* domain, or if information hasn't been requested.
*/
@@ -137,6 +143,7 @@ typedef struct TypeCacheEntry
#define TYPECACHE_DOMAIN_CONSTR_INFO 0x2000
#define TYPECACHE_HASH_EXTENDED_PROC 0x4000
#define TYPECACHE_HASH_EXTENDED_PROC_FINFO 0x8000
+#define TYPECACHE_RANGE_TYPE 0x10000
/*
* Callers wishing to maintain a long-lived reference to a domain's constraint
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_opt.out b/src/test/regress/expected/partition_opt.out
new file mode 100644
index 0000000..4daea12
--- /dev/null
+++ b/src/test/regress/expected/partition_opt.out
@@ -0,0 +1,34 @@
+create table bt (k integer, v integer) partition by range (k);
+create table dt1 partition of bt for values from (1) to (10001);
+create table dt2 partition of bt for values from (10001) to (20001);
+create index dti1 on dt1(v);
+create index dti2 on dt2(v);
+insert into bt values (generate_series(1,20000), generate_series(1,20000));
+analyze bt;
+explain select * from bt where k between 1 and 20000 and v = 100;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Append (cost=0.29..16.61 rows=2 width=8)
+ -> Index Scan using dti1 on dt1 (cost=0.29..8.30 rows=1 width=8)
+ Index Cond: (v = 100)
+ -> Index Scan using dti2 on dt2 (cost=0.29..8.30 rows=1 width=8)
+ Index Cond: (v = 100)
+(5 rows)
+
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (logdate);
+CREATE TABLE measurement_y2006m03 PARTITION OF measurement
+ FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
+explain select * from measurement where logdate between '2006-03-01' AND '2006-03-31';
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Append (cost=0.00..28.50 rows=1850 width=16)
+ -> Seq Scan on measurement_y2006m03 (cost=0.00..28.50 rows=1850 width=16)
+(2 rows)
+
+drop table bt;
+drop table measurement;
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
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index e224977..a026561 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid c
# ----------
# Another group of parallel tests
# ----------
-test: identity partition_join partition_prune reloptions hash_part
+test: identity partition_join partition_prune reloptions hash_part partition_opt
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 9fc5f1a..42c1f51 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -182,6 +182,7 @@ test: xml
test: identity
test: partition_join
test: partition_prune
+test: partition_opt
test: reloptions
test: hash_part
test: event_trigger
diff --git a/src/test/regress/sql/partition_opt.sql b/src/test/regress/sql/partition_opt.sql
new file mode 100644
index 0000000..45a8cdc
--- /dev/null
+++ b/src/test/regress/sql/partition_opt.sql
@@ -0,0 +1,24 @@
+create table bt (k integer, v integer) partition by range (k);
+create table dt1 partition of bt for values from (1) to (10001);
+create table dt2 partition of bt for values from (10001) to (20001);
+create index dti1 on dt1(v);
+create index dti2 on dt2(v);
+insert into bt values (generate_series(1,20000), generate_series(1,20000));
+analyze bt;
+explain select * from bt where k between 1 and 20000 and v = 100;
+
+CREATE TABLE measurement (
+ city_id int not null,
+ logdate date not null,
+ peaktemp int,
+ unitsales int
+) PARTITION BY RANGE (logdate);
+
+
+CREATE TABLE measurement_y2006m03 PARTITION OF measurement
+ FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
+
+explain select * from measurement where logdate between '2006-03-01' AND '2006-03-31';
+
+drop table bt;
+drop table measurement;
