The new partitioned tables do not contain any data by themselves. Any
data inserted into a partitioned table is routed to and stored in one of
its partitions. In fact, it is impossible to insert *any* data before a
partition (to be precise, a leaf partition) is created. It seems wasteful
then to allocate physical storage (files) for partitioned tables. If we
do not allocate the storage, then we must make sure that the right thing
happens when a command that is intended to manipulate a table's storage
encounters a partitioned table, the "right thing" here being that the
command's code either throws an error or warning (in some cases) if the
specified table is a partitioned table or ignores any partitioned tables
when it reads the list of relations to process from pg_class. Commands
that need to be taught about this are vacuum, analyze, truncate, and alter
table. Specifically:
- In case of vacuum, specifying a partitioned table causes a warning
- In case of analyze, we do not throw an error or warning but simply
avoid calling do_analyze_rel() *non-recursively*. Further in
acquire_inherited_sample_rows(), any partitioned tables in the list
returned by find_all_inheritors() are skipped.
- In case of truncate, only the part which manipulates table's physical
storage is skipped for partitioned tables.
- ATRewriteTables() skips on the AlteredTableInfo entries for partitioned
tables, because there is nothing to be done.
- Since we cannot create indexes on partitioned tables anyway, there is
no need to handle cluster and reindex (they throw a meaningful error
already due to the lack of indexes.)
Patches 0001 and 0002 of the attached implement the above part. 0001
teaches the above mentioned commands to do the "right thing" as described
above and 0002 teaches heap_create() and heap_create_with_catalog() to not
create any physical storage (none of the forks) for partitioned tables.
Then comes 0003, which concerns inheritance planning. In a regular
inheritance set (ie, the inheritance set corresponding to an inheritance
hierarchy whose root is a regular table), the inheritance parents are
included in their role as child members, because they might contribute
rows to the final result. So AppendRelInfo's are created for each such
table by the planner prep phase, which the later planning steps use to
create a scan plan for those tables as the Append's child plans.
Currently, the partitioned tables are also processed by the optimizer as
inheritance sets. Partitioned table inheritance parents do not own any
storage, so we *must* not create scan plans for them. So we do not need
to process them as child members of the inheritance set. 0003 teaches
expand_inherited_rtentry() to not add partitioned tables as child members.
Also, since the root partitioned table RTE is no longer added to the
Append list as the 1st child member, inheritance_planner() cannot assume
that it can install the 1st child RTE as the nominalRelation of a given
ModifyTable node, instead the original root parent table RTE is installed
as the nominalRelation.
Together the above patches implement the first item listed in "Some
notes:" part of an email [1] on the original declarative partitioning
thread, which says:
"We should try to teach the executor never to scan the parent. That's
never necessary with this system, and it might add significant overhead.
We should also try to get rid of the idea of the parent having storage
(i.e. a relfilenode)."
Thoughts, comments?
Thanks,
Amit
[1]
https://postgr.es/m/CA%2BTgmob6DJEQBd%3DqH2wZG1onYZ9R1Sji3q%2BGqCRUqQi%2Bug28Rw%40mail.gmail.com
>From 1cc123a2f01066ababdc971a3ac9aafe1028be74 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 6 Feb 2017 18:03:28 +0900
Subject: [PATCH 1/3] Partitioned tables are empty themselves
So, there is not much point in trying to do things to them that need
accessing files (a later commit will make it so that there is no file
at all to access.) Things that needed attention are: vacuum, analyze,
truncate, ATRewriteTables.
---
src/backend/commands/analyze.c | 39 +++++++++++++++++++++++++++------------
src/backend/commands/tablecmds.c | 15 ++++++++++++---
src/backend/commands/vacuum.c | 14 +++++++++-----
3 files changed, 48 insertions(+), 20 deletions(-)
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index ed3acb1673..4dc5bd48f8 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -201,8 +201,7 @@ analyze_rel(Oid relid, RangeVar *relation, int options,
* locked the relation.
*/
if (onerel->rd_rel->relkind == RELKIND_RELATION ||
- onerel->rd_rel->relkind == RELKIND_MATVIEW ||
- onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ onerel->rd_rel->relkind == RELKIND_MATVIEW)
{
/* Regular table, so we'll use the regular row acquisition function */
acquirefunc = acquire_sample_rows;
@@ -234,7 +233,11 @@ analyze_rel(Oid relid, RangeVar *relation, int options,
return;
}
}
- else
+ /*
+ * Although we cannot analyze partitioned tables themselves, we are
+ * still be able to do the recursive ANALYZE.
+ */
+ else if (onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
{
/* No need for a WARNING if we already complained during VACUUM */
if (!(options & VACOPT_VACUUM))
@@ -255,8 +258,9 @@ analyze_rel(Oid relid, RangeVar *relation, int options,
/*
* Do the normal non-recursive ANALYZE.
*/
- do_analyze_rel(onerel, options, params, va_cols, acquirefunc, relpages,
- false, in_outer_xact, elevel);
+ if (onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ do_analyze_rel(onerel, options, params, va_cols, acquirefunc,
+ relpages, false, in_outer_xact, elevel);
/*
* If there are child tables, do recursive ANALYZE.
@@ -1318,8 +1322,7 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
/* Check table type (MATVIEW can't happen, but might as well allow) */
if (childrel->rd_rel->relkind == RELKIND_RELATION ||
- childrel->rd_rel->relkind == RELKIND_MATVIEW ||
- childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ childrel->rd_rel->relkind == RELKIND_MATVIEW)
{
/* Regular table, so use the regular row acquisition function */
acquirefunc = acquire_sample_rows;
@@ -1351,9 +1354,18 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
}
else
{
- /* ignore, but release the lock on it */
- Assert(childrel != onerel);
- heap_close(childrel, AccessShareLock);
+ /*
+ * Ignore, but release the lock on it. Partitioned tables are
+ * ignored here.
+ */
+ Assert(childrel != onerel ||
+ childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
+
+ /* Do not try to unlock the passed in rel */
+ if (childrel == onerel)
+ heap_close(childrel, NoLock);
+ else
+ heap_close(childrel, AccessShareLock);
continue;
}
@@ -1366,9 +1378,12 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
}
/*
- * If we don't have at least two tables to consider, fail.
+ * If we don't have at least two tables to consider, fail. In case the
+ * passed in relation is a partitioned table, we could have just one
+ * leaf partition and that'd be fine.
*/
- if (nrels < 2)
+ if ((onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE && nrels < 2) ||
+ nrels < 1)
{
ereport(elevel,
(errmsg("skipping analyze of \"%s.%s\" inheritance tree --- this inheritance tree contains no analyzable child tables",
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 37a4c4a3d6..92dfb46b45 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1354,6 +1354,10 @@ ExecuteTruncate(TruncateStmt *stmt)
{
Relation rel = (Relation) lfirst(cell);
+ /* Skip partitioned tables as there is nothing to do */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ continue;
+
/*
* Normally, we need a transaction-safe truncation here. However, if
* the table was either created in the current (sub)transaction or has
@@ -1464,7 +1468,11 @@ truncate_check_rel(Relation rel)
{
AclResult aclresult;
- /* Only allow truncate on regular tables */
+ /*
+ * Only allow truncate on regular tables and partitioned tables (although,
+ * the latter are only being included here for the following checks; no
+ * physical truncation will occur in their case.)
+ */
if (rel->rd_rel->relkind != RELKIND_RELATION &&
rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
ereport(ERROR,
@@ -4011,8 +4019,9 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode)
{
AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab);
- /* Foreign tables have no storage. */
- if (tab->relkind == RELKIND_FOREIGN_TABLE)
+ /* Foreign tables have no storage, nor do partitioned tables. */
+ if (tab->relkind == RELKIND_FOREIGN_TABLE ||
+ tab->relkind == RELKIND_PARTITIONED_TABLE)
continue;
/*
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 812fb4a48f..cc09effa29 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1322,12 +1322,16 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
*/
if (onerel->rd_rel->relkind != RELKIND_RELATION &&
onerel->rd_rel->relkind != RELKIND_MATVIEW &&
- onerel->rd_rel->relkind != RELKIND_TOASTVALUE &&
- onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ onerel->rd_rel->relkind != RELKIND_TOASTVALUE)
{
- ereport(WARNING,
- (errmsg("skipping \"%s\" --- cannot vacuum non-tables or special system tables",
- RelationGetRelationName(onerel))));
+ if (onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(WARNING,
+ (errmsg("skipping \"%s\" --- cannot vacuum partitioned tables",
+ RelationGetRelationName(onerel))));
+ else
+ ereport(WARNING,
+ (errmsg("skipping \"%s\" --- cannot vacuum non-tables or special system tables",
+ RelationGetRelationName(onerel))));
relation_close(onerel, lmode);
PopActiveSnapshot();
CommitTransactionCommand();
--
2.11.0
>From cdd6776f5bf846933d18e48de1c16bb5545bbfaa Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 24 Jan 2017 14:22:34 +0900
Subject: [PATCH 2/3] Do not allocate storage for partitioned tables.
Currently, it is not possible to insert any data into a partitioned
table. So, they're empty at all times, which means it is wasteful to
allocate relfilenode and related storage objects.
---
src/backend/catalog/heap.c | 8 +++++++-
1 file changed, 7 insertions(+), 1 deletion(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 41c0056556..b08af1205d 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -291,6 +291,7 @@ heap_create(const char *relname,
case RELKIND_VIEW:
case RELKIND_COMPOSITE_TYPE:
case RELKIND_FOREIGN_TABLE:
+ case RELKIND_PARTITIONED_TABLE:
create_storage = false;
/*
@@ -1351,7 +1352,12 @@ heap_create_with_catalog(const char *relname,
relkind == RELKIND_TOASTVALUE ||
relkind == RELKIND_PARTITIONED_TABLE);
- heap_create_init_fork(new_rel_desc);
+ /*
+ * We do not want to create any storage objects for a partitioned
+ * table.
+ */
+ if (relkind != RELKIND_PARTITIONED_TABLE)
+ heap_create_init_fork(new_rel_desc);
}
/*
--
2.11.0
>From 72569808dce0e584e6138ff0086a6dddc7957cae Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 6 Feb 2017 17:26:48 +0900
Subject: [PATCH 3/3] Always plan partitioned tables as inheritance sets
...even if empty. Currently, we create scan plans for inheritance
parents in their role as an inheritance set member. Partitioned tables
do not contain any data, so it's not right to create scan plans for them.
So we need not create AppendRelInfo's for them in the planner prep phase.
That means the Append set for a partitioned table may contain zero members
if it does not have leaf partitions Currently, such a case is treated by
the current code as a non-inheritance situation and a scan plan is created
for just the parent. It is done by turning off the inh flag of the parent
RTE so that later planner phases create a simple scan plan for it. Since
we cannot create scan plans for partitioned tables, don't turn off its inh
flag even if we found that there are no Append members. Let this empty
Append member list cause later code create a constant-false Result plan.
Also, inheritance_planner() must not always assume that the 1st child
member of an inheritance set is the nominal relation. For partitioned
tables, we use the original parent RTE as the nominal relation in that
case, because we no longer create the duplicate RTE representing a
partitioned table as a child member of the inheritance set.
---
src/backend/optimizer/plan/planner.c | 15 +++++
src/backend/optimizer/prep/prepunion.c | 34 +++++++++--
src/test/regress/expected/inherit.out | 108 +++++++--------------------------
3 files changed, 66 insertions(+), 91 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 881742f46b..271d73acca 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -996,10 +996,20 @@ inheritance_planner(PlannerInfo *root)
RelOptInfo *final_rel;
ListCell *lc;
Index rti;
+ RangeTblEntry *parent_rte;
Assert(parse->commandType != CMD_INSERT);
/*
+ * If the parent RTE is a partitioned table, we should use that as the
+ * nominal relation, because we do not have the duplicate parent RTE,
+ * unlike in the case of a non-partitioned inheritance parent.
+ */
+ parent_rte = rt_fetch(parentRTindex, root->parse->rtable);
+ if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE)
+ nominalRelation = parentRTindex;
+
+ /*
* We generate a modified instance of the original Query for each target
* relation, plan that, and put all the plans into a list that will be
* controlled by a single ModifyTable node. All the instances share the
@@ -1214,6 +1224,11 @@ inheritance_planner(PlannerInfo *root)
* will not be otherwise referenced in the plan, doing so would give
* rise to confusing use of multiple aliases in EXPLAIN output for
* what the user will think is the "same" table.)
+ *
+ * If the parent is a partitioned table, we do not have a separate RTE
+ * representing it as a member of the inheritance set, because we do
+ * not create a scan plan for it. As mentioned at the top of this
+ * function, we make the parent RTE itself the nominal relation.
*/
if (nominalRelation < 0)
nominalRelation = appinfo->child_relid;
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index 06e843dff0..781bf149a7 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -1374,9 +1374,16 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
Assert(rte->rtekind == RTE_SUBQUERY);
return;
}
- /* Fast path for common case of childless table */
+ /*
+ * Fast path for common case of childless table. However, a partitioned
+ * table RTE should *always* be processed by the later code as an
+ * inheritance set, possibly an empty one in the absence of any leaf
+ * partitions. It is wrong to try to create a scan plan for a partitioned
+ * table, since it does not own storage.
+ */
parentOID = rte->relid;
- if (!has_subclass(parentOID))
+ if (rte->relkind != RELKIND_PARTITIONED_TABLE &&
+ !has_subclass(parentOID))
{
/* Clear flag before returning */
rte->inh = false;
@@ -1410,9 +1417,11 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
/*
* Check that there's at least one descendant, else treat as no-child
* case. This could happen despite above has_subclass() check, if table
- * once had a child but no longer does.
+ * once had a child but no longer does. Again, do not disable inheritance
+ * for a partitioned table as described above.
*/
- if (list_length(inhOIDs) < 2)
+ if (rte->relkind != RELKIND_PARTITIONED_TABLE &&
+ list_length(inhOIDs) < 2)
{
/* Clear flag before returning */
rte->inh = false;
@@ -1450,6 +1459,17 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
newrelation = oldrelation;
/*
+ * Do not add partitioned tables as Append members, because we should
+ * not create scan plans for partitioned tables.
+ */
+ if (newrelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ if (newrelation != oldrelation)
+ heap_close(newrelation, lockmode);
+ continue;
+ }
+
+ /*
* It is possible that the parent table has children that are temp
* tables of other backends. We cannot safely access such tables
* (because of buffering issues), and the best thing to do seems to be
@@ -1548,9 +1568,11 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
* If all the children were temp tables, pretend it's a non-inheritance
* situation. The duplicate RTE we added for the parent table is
* harmless, so we don't bother to get rid of it; ditto for the useless
- * PlanRowMark node.
+ * PlanRowMark node. Do not pretend non-inheritance for partitioned
+ * tables as described above.
*/
- if (list_length(appinfos) < 2)
+ if (rte->relkind != RELKIND_PARTITIONED_TABLE &&
+ list_length(appinfos) < 2)
{
/* Clear flag before returning */
rte->inh = false;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index a8c8b28a75..ef4e049047 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1605,71 +1605,60 @@ explain (costs off) select * from list_parted;
QUERY PLAN
--------------------------------
Append
- -> Seq Scan on list_parted
-> Seq Scan on part_ab_cd
-> Seq Scan on part_ef_gh
-> Seq Scan on part_null_xy
-(5 rows)
+(4 rows)
explain (costs off) select * from list_parted where a is null;
QUERY PLAN
--------------------------------
Append
- -> Seq Scan on list_parted
- Filter: (a IS NULL)
-> Seq Scan on part_null_xy
Filter: (a IS NULL)
-(5 rows)
+(3 rows)
explain (costs off) select * from list_parted where a is not null;
QUERY PLAN
---------------------------------
Append
- -> Seq Scan on list_parted
- Filter: (a IS NOT NULL)
-> 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)
-(9 rows)
+(7 rows)
explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
QUERY PLAN
----------------------------------------------------------
Append
- -> Seq Scan on list_parted
- Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-> 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[]))
-(7 rows)
+(5 rows)
explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
QUERY PLAN
---------------------------------------------------------------------------------------
Append
- -> Seq Scan on list_parted
- Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-> 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[])))
-(9 rows)
+(7 rows)
explain (costs off) select * from list_parted where a = 'ab';
QUERY PLAN
------------------------------------------
Append
- -> Seq Scan on list_parted
- Filter: ((a)::text = 'ab'::text)
-> Seq Scan on part_ab_cd
Filter: ((a)::text = 'ab'::text)
-(5 rows)
+(3 rows)
create table range_list_parted (
a int,
@@ -1689,14 +1678,9 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab');
create table part_40_inf_cd partition of part_40_inf for values in ('cd');
create table part_40_inf_null partition of part_40_inf for values in (null);
explain (costs off) select * from range_list_parted;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Append
- -> Seq Scan on range_list_parted
- -> Seq Scan on part_1_10
- -> Seq Scan on part_10_20
- -> Seq Scan on part_21_30
- -> Seq Scan on part_40_inf
-> Seq Scan on part_1_10_ab
-> Seq Scan on part_1_10_cd
-> Seq Scan on part_10_20_ab
@@ -1706,36 +1690,22 @@ explain (costs off) select * from range_list_parted;
-> Seq Scan on part_40_inf_ab
-> Seq Scan on part_40_inf_cd
-> Seq Scan on part_40_inf_null
-(15 rows)
+(10 rows)
explain (costs off) select * from range_list_parted where a = 5;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+--------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: (a = 5)
- -> Seq Scan on part_1_10
- Filter: (a = 5)
-> Seq Scan on part_1_10_ab
Filter: (a = 5)
-> Seq Scan on part_1_10_cd
Filter: (a = 5)
-(9 rows)
+(5 rows)
explain (costs off) select * from range_list_parted where b = 'ab';
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: (b = 'ab'::bpchar)
- -> Seq Scan on part_1_10
- Filter: (b = 'ab'::bpchar)
- -> Seq Scan on part_10_20
- Filter: (b = 'ab'::bpchar)
- -> Seq Scan on part_21_30
- Filter: (b = 'ab'::bpchar)
- -> Seq Scan on part_40_inf
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_1_10_ab
Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_10_20_ab
@@ -1744,27 +1714,19 @@ explain (costs off) select * from range_list_parted where b = 'ab';
Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_40_inf_ab
Filter: (b = 'ab'::bpchar)
-(19 rows)
+(9 rows)
explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab');
QUERY PLAN
-----------------------------------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
- -> Seq Scan on part_1_10
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
- -> Seq Scan on part_10_20
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
- -> Seq Scan on part_21_30
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-> Seq Scan on part_1_10_ab
Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-> 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))
-(15 rows)
+(7 rows)
/* Should select no rows because range partition key cannot be null */
explain (costs off) select * from range_list_parted where a is null;
@@ -1776,37 +1738,17 @@ explain (costs off) select * from range_list_parted where a is null;
/* Should only select rows from the null-accepting partition */
explain (costs off) select * from range_list_parted where b is null;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: (b IS NULL)
- -> Seq Scan on part_1_10
- Filter: (b IS NULL)
- -> Seq Scan on part_10_20
- Filter: (b IS NULL)
- -> Seq Scan on part_21_30
- Filter: (b IS NULL)
- -> Seq Scan on part_40_inf
- Filter: (b IS NULL)
-> Seq Scan on part_40_inf_null
Filter: (b IS NULL)
-(13 rows)
+(3 rows)
explain (costs off) select * from range_list_parted where a is not null and a < 67;
QUERY PLAN
------------------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: ((a IS NOT NULL) AND (a < 67))
- -> Seq Scan on part_1_10
- Filter: ((a IS NOT NULL) AND (a < 67))
- -> Seq Scan on part_10_20
- Filter: ((a IS NOT NULL) AND (a < 67))
- -> Seq Scan on part_21_30
- Filter: ((a IS NOT NULL) AND (a < 67))
- -> Seq Scan on part_40_inf
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_1_10_ab
Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_1_10_cd
@@ -1825,23 +1767,19 @@ explain (costs off) select * from range_list_parted where a is not null and a <
Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_40_inf_null
Filter: ((a IS NOT NULL) AND (a < 67))
-(29 rows)
+(19 rows)
explain (costs off) select * from range_list_parted where a >= 30;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: (a >= 30)
- -> Seq Scan on part_40_inf
- Filter: (a >= 30)
-> 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)
-(11 rows)
+(7 rows)
drop table list_parted cascade;
NOTICE: drop cascades to 3 other objects
--
2.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
