This is an automated email from the ASF dual-hosted git repository. maxyang pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit a153b4a3f2735f2d21b4f9cc8da332964bf1665c Author: hari krishna <[email protected]> AuthorDate: Mon Sep 12 11:25:05 2022 +0530 FIXME : Allow SPE plans to show "Partitions selected: 1 (out of 5)" ""GPDB_12_MERGE_FIXME: we used to show something along the lines of "Partitions selected: 1 (out of 5)" under the partition selector. By eleminating the (static) partition selector during translation, we only get the survivor count, and lose the size of the universe temporarily. However, if we manage to shift the static pruning information sufficiently adjacent to (or better, into) a DXL Dynamic Table Scan, we should be able to get that information back."" Through this PR I am trying to pass the total partition information through ORCA and into the plan node used in explain. --- src/backend/commands/explain.c | 70 +++++++++---- src/test/regress/expected/dpe_optimizer.out | 64 ++++++------ src/test/regress/expected/gporca_optimizer.out | 56 +++++----- src/test/regress/expected/inherit_optimizer.out | 20 ++-- .../expected/orca_static_pruning_optimizer.out | 16 +-- .../regress/expected/partition_prune_optimizer.out | 116 ++++++++++----------- 6 files changed, 187 insertions(+), 155 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 0fe1478635..1ab18ae821 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -18,6 +18,7 @@ #include "access/xact.h" #include "catalog/pg_type.h" +#include "catalog/pg_inherits.h" #include "commands/createas.h" #include "commands/defrem.h" #include "commands/prepare.h" @@ -184,6 +185,7 @@ static void ExplainIndentText(ExplainState *es); static void ExplainJSONLineEnding(ExplainState *es); static void ExplainYAMLLineStarting(ExplainState *es); static void escape_yaml(StringInfo buf, const char *str); +static int countLeafPartTables(Oid relId); static void Explainlocus(ExplainState *es, CdbLocusType locustype, int parallel); @@ -2312,10 +2314,18 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); - if (IsA(plan, DynamicIndexScan)) + if (IsA(plan, DynamicIndexScan)) { + char *buf; + Oid relid; + relid = rt_fetch(((DynamicIndexScan *)plan) + ->indexscan.scan.scanrelid, + es->rtable)->relid; + buf = psprintf("(out of %d)", countLeafPartTables(relid)); ExplainPropertyInteger( - "Number of partitions to scan", "", - list_length(((DynamicIndexScan *) plan)->partOids), es); + "Number of partitions to scan", buf, + list_length(((DynamicIndexScan *)plan)->partOids), + es); + } break; case T_IndexOnlyScan: show_scan_qual(((IndexOnlyScan *) plan)->indexqual, @@ -2343,11 +2353,19 @@ ExplainNode(PlanState *planstate, List *ancestors, { List *bitmapqualorig; - if (IsA(plan, DynamicBitmapHeapScan)) + if (IsA(plan, DynamicBitmapHeapScan)) { + char *buf; + Oid relid; + relid = rt_fetch(((DynamicBitmapHeapScan *)plan) + ->bitmapheapscan.scan.scanrelid, + es->rtable)->relid; + buf = psprintf("(out of %d)", countLeafPartTables(relid)); ExplainPropertyInteger( - "Number of partitions to scan", "", - list_length(((DynamicBitmapHeapScan *) plan)->partOids), es); - + "Number of partitions to scan", buf, + list_length( + ((DynamicBitmapHeapScan *)plan)->partOids), + es); + } bitmapqualorig = ((BitmapHeapScan *) plan)->bitmapqualorig; show_scan_qual(bitmapqualorig, @@ -2376,19 +2394,17 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_NamedTuplestoreScan: case T_WorkTableScan: case T_SubqueryScan: - /* - * GPDB_12_MERGE_FIXME: we used to show something along the lines of - * "Partitions selected: 1 (out of 5)" under the partition selector. - * By eleminating the (static) partition selector during translation, - * we only get the survivor count, and lose the size of the universe - * temporarily. However, if we manage to shift the static pruning - * information sufficiently adjacent to (or better, into) a DXL Dynamic - * Table Scan, we should be able to get that information back. - */ - if (IsA(plan, DynamicSeqScan)) + if (IsA(plan, DynamicSeqScan)) { + char *buf; + Oid relid; + relid = rt_fetch(((DynamicSeqScan *)plan) + ->seqscan.scanrelid, + es->rtable)->relid; + buf = psprintf("(out of %d)", countLeafPartTables(relid)); ExplainPropertyInteger( - "Number of partitions to scan", "", - list_length(((DynamicSeqScan *) plan)->partOids), es); + "Number of partitions to scan", buf, + list_length(((DynamicSeqScan *)plan)->partOids),es); + } show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, @@ -5919,3 +5935,19 @@ Explainlocus(ExplainState *es, CdbLocusType locustype, int parallel) if (parallel > 1) ExplainPropertyInteger("Parallel Workers", NULL, parallel, es); } + +/* + * Return the number of leaf parts of the partitioned table with the given oid + */ +static int +countLeafPartTables(Oid relid) { + List *partitions; + partitions = find_all_inheritors(relid, NoLock, NULL); + Assert(list_length(partitions) > 0); + + /* find_all_inheritors returns a list of relation OIDs including the + * parent relId, so length of the list minus one gives total leaf + * partitions. + */ + return (list_length(partitions) -1); +} diff --git a/src/test/regress/expected/dpe_optimizer.out b/src/test/regress/expected/dpe_optimizer.out index 46ee8c65e5..73b84d7f6a 100644 --- a/src/test/regress/expected/dpe_optimizer.out +++ b/src/test/regress/expected/dpe_optimizer.out @@ -86,7 +86,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (10 rows) @@ -124,7 +124,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = (t.tid + 1)) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (10 rows) @@ -163,7 +163,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where Filter: (t1 = ('hello'::text || (tid)::text)) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (11 rows) @@ -202,7 +202,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Dynamic Index Scan on pt1_idx on pt (actual rows=1 loops=2) Index Cond: (pt1 = t.t1) Filter: ((pt1 = t.t1) AND (ptid = t.tid)) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (11 rows) @@ -225,7 +225,7 @@ explain (costs off, timing off, summary off, analyze) select * from pt where pti Hash Cond: (pt.ptid = t.tid) Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. -> Dynamic Seq Scan on pt (actual rows=8 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). -> Hash (actual rows=2 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2049kB @@ -277,7 +277,7 @@ explain (costs off, timing off, summary off, analyze) select * from pt where exi Hash Cond: (pt.ptid = t.tid) Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 262144 buckets. -> Dynamic Seq Scan on pt (actual rows=8 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Filter: (NOT (ptid IS NULL)) Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). -> Hash (actual rows=2 loops=1) @@ -334,7 +334,7 @@ explain (costs off, timing off, summary off, analyze) select count(*) from t, pt -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (12 rows) @@ -364,7 +364,7 @@ explain (costs off, timing off, summary off, analyze) select *, rank() over (ord -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (16 rows) @@ -408,7 +408,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). -> Nested Loop (actual rows=7 loops=1) Join Filter: true @@ -416,7 +416,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Seq Scan on t t_1 (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt pt_1 (actual rows=4 loops=2) Index Cond: (ptid = (t_1.tid + 2)) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (19 rows) @@ -484,7 +484,7 @@ explain (costs off, timing off, summary off, analyze) select count(*) from -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). -> Nested Loop (actual rows=7 loops=1) Join Filter: true @@ -492,7 +492,7 @@ explain (costs off, timing off, summary off, analyze) select count(*) from -> Seq Scan on t t_1 (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt pt_1 (actual rows=4 loops=2) Index Cond: (ptid = (t_1.tid + 2)) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (21 rows) @@ -523,7 +523,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (10 rows) @@ -575,7 +575,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Gather Motion 3:1 (slice2; segments: 3) (actual rows=1 loops=1) -> Dynamic Index Scan on pt1_idx on pt (actual rows=1 loops=1) Index Cond: (pt1 = 'hello0'::text) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (13 rows) @@ -603,7 +603,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (10 rows) @@ -648,7 +648,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on pt1_idx on pt (actual rows=1 loops=2) Index Cond: (pt1 = t.t1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (10 rows) @@ -670,7 +670,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Seq Scan on t (actual rows=2 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=16 loops=2) Index Cond: (ptid > t.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (10 rows) @@ -782,7 +782,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, t1, pt wh -> Seq Scan on t1 (actual rows=1 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=4 loops=2) Index Cond: (ptid = t1.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). -> Hash (actual rows=2 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2049kB @@ -860,7 +860,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, t1, pt wh -> Seq Scan on t1 (actual rows=6 loops=1) -> Dynamic Index Scan on ptid_idx on pt (actual rows=1 loops=12) Index Cond: (ptid = t1.tid) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers of 12 scans. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (20 rows) @@ -899,12 +899,12 @@ explain (costs off, timing off, summary off, analyze) select * from t1 inner joi -> Dynamic Index Scan on ptid_idx on pt (actual rows=1 loops=1) Index Cond: (ptid = t1.tid) Filter: ((ptid = t1.tid) AND (t1.dist = dist)) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 2 workers. Max 6 parts (seg0). -> Dynamic Index Scan on ptid_idx on pt pt_1 (actual rows=1 loops=1) Index Cond: ((ptid <= pt.ptid) AND (ptid = pt.ptid)) Filter: ((ptid <= pt.ptid) AND (ptid = pt.ptid) AND (dist = pt.dist)) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 2 workers. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (17 rows) @@ -926,7 +926,7 @@ explain (costs off, timing off, summary off, analyze) select * from pt, pt1 wher Hash Cond: (pt1.ptid = pt.ptid) Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 1 of 131072 buckets. -> Dynamic Seq Scan on pt1 (actual rows=5 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). -> Hash (actual rows=1 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 1025kB @@ -934,7 +934,7 @@ explain (costs off, timing off, summary off, analyze) select * from pt, pt1 wher -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1) -> Dynamic Index Scan on pt1_idx on pt (actual rows=1 loops=1) Index Cond: (pt1 = 'hello0'::text) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (20 rows) @@ -963,7 +963,7 @@ explain (costs off, timing off, summary off, analyze) select count(*) from pt, p Hash Cond: (pt1.ptid = pt.ptid) Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 1 of 524288 buckets. -> Dynamic Seq Scan on pt1 (actual rows=5 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). -> Hash (actual rows=1 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB @@ -971,7 +971,7 @@ explain (costs off, timing off, summary off, analyze) select count(*) from pt, p -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1) -> Dynamic Index Scan on pt1_idx on pt (actual rows=1 loops=1) Index Cond: (pt1 = 'hello0'::text) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (18 rows) @@ -1014,7 +1014,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Redistribute Motion 3:3 (slice3; segments: 3) (actual rows=3 loops=1) Hash Key: pt.b -> Dynamic Seq Scan on pt (actual rows=3 loops=1) - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 5) Partitions scanned: Avg 5.0 x 3 workers. Max 5 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (14 rows) @@ -1061,7 +1061,7 @@ explain (costs off, timing off, summary off, analyze) select * from t, pt where -> Hash Join (actual rows=0 loops=1) Hash Cond: (pt.b = t.a) -> Dynamic Seq Scan on pt (actual rows=0 loops=1) - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 5) -> Hash (actual rows=4 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB -> Partition Selector (selector id: $0) (actual rows=4 loops=1) @@ -2416,7 +2416,7 @@ explain (costs off, timing off, summary off, analyze) select * from apart as a, Hash Cond: (apart.t = b.t) Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 5 of 262144 buckets. -> Dynamic Seq Scan on apart (actual rows=70 loops=1) - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 5) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). -> Hash (actual rows=5 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2049kB @@ -2451,7 +2451,7 @@ explain (costs off, timing off, summary off, analyze) select * from apart as a, Hash Cond: (apart.t = b.t) Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 5 of 262144 buckets. -> Dynamic Seq Scan on apart (actual rows=70 loops=1) - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 5) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). -> Hash (actual rows=5 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2049kB @@ -2513,7 +2513,7 @@ explain (costs off, timing off, summary off, analyze) select * from (select coun Buckets: 131072 Batches: 1 Memory Usage: 1025kB -> Gather Motion 3:1 (slice2; segments: 3) (actual rows=10 loops=1) -> Dynamic Seq Scan on pat (actual rows=5 loops=1) - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 5) Partitions scanned: Avg 5.0 x 3 workers. Max 5 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (17 rows) @@ -2686,7 +2686,7 @@ select * from pt, t where t.dist = pt.dist and t.tid < pt.ptid; Rows Removed by Join Filter: 3 Extra Text: (seg1) Hash chain length 2.0 avg, 2 max, using 1 of 524288 buckets. -> Dynamic Seq Scan on pt (actual rows=7 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: 4 (seg1). -> Hash (actual rows=2 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB @@ -2738,7 +2738,7 @@ select * from pt, t where t.dist = pt.dist and t.tid = pt.ptid order by t.tid, t Hash Cond: ((pt.dist = t.dist) AND (pt.ptid = t.tid)) Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 36 of 262144 buckets. -> Dynamic Seq Scan on pt (cost=0.00..431.00 rows=33 width=12) (actual rows=36 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). -> Hash (cost=431.00..431.00 rows=33 width=12) (actual rows=37 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 2050kB diff --git a/src/test/regress/expected/gporca_optimizer.out b/src/test/regress/expected/gporca_optimizer.out index 138bbaadf2..9f49d24cec 100644 --- a/src/test/regress/expected/gporca_optimizer.out +++ b/src/test/regress/expected/gporca_optimizer.out @@ -8294,7 +8294,7 @@ explain select * from orca.t order by 1,2; -> Sort (cost=0.00..431.00 rows=1 width=24) Sort Key: timest, user_id -> Dynamic Seq Scan on t (cost=0.00..431.00 rows=1 width=24) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Optimizer: Pivotal Optimizer (GPORCA) (7 rows) @@ -8368,7 +8368,7 @@ explain select * from orca.t_date where user_id=9; ------------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.00 rows=2 width=20) -> Dynamic Seq Scan on t_date (cost=0.00..431.00 rows=1 width=20) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Filter: (user_id = '9'::numeric) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -8411,7 +8411,7 @@ explain select * from orca.t_text where user_id=9; ------------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.00 rows=2 width=20) -> Dynamic Seq Scan on t_text (cost=0.00..431.00 rows=1 width=20) - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (user_id = '9'::numeric) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -8446,7 +8446,7 @@ explain select * from orca.t_ceeval_ints where user_id=4; ------------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.00 rows=1 width=21) -> Dynamic Seq Scan on t_ceeval_ints (cost=0.00..431.00 rows=1 width=21) - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (user_id = '4'::numeric) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -9978,7 +9978,7 @@ explain select * from orca.bm_dyn_test where i=2 and t='2'; -------------------------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..398.03 rows=2 width=10) -> Dynamic Bitmap Heap Scan on bm_dyn_test (cost=0.00..398.03 rows=1 width=10) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Recheck Cond: (i = 2) Filter: ((i = 2) AND (t = '2'::text)) -> Dynamic Bitmap Index Scan on bm_dyn_test_idx (cost=0.00..0.00 rows=0 width=0) @@ -11534,7 +11534,7 @@ explain analyze SELECT * FROM ds_part, non_part2 WHERE ds_part.c = non_part2.e A -> Hash Join (cost=0.00..1324481.18 rows=1 width=20) Hash Cond: (ds_part.c = non_part2.e) -> Dynamic Seq Scan on ds_part (cost=0.00..1324050.11 rows=334 width=12) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Filter: ((a = (b + 1)) AND (SubPlan 1)) SubPlan 1 -> Materialize (cost=0.00..431.00 rows=1 width=4) @@ -11961,12 +11961,12 @@ explain select * from part1, part2 where part1.b = part2.b limit 5; -> Hash Join (cost=0.00..862.13 rows=334 width=16) Hash Cond: (part1.b = part2.b) -> Dynamic Seq Scan on part1 (cost=0.00..431.01 rows=334 width=8) - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 4) -> Hash (cost=431.02..431.02 rows=100 width=8) -> Partition Selector (selector id: $0) (cost=0.00..431.02 rows=100 width=8) -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.02 rows=100 width=8) -> Dynamic Seq Scan on part2 (cost=0.00..431.00 rows=34 width=8) - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 4) Optimizer: Pivotal Optimizer (GPORCA) (13 rows) @@ -12299,7 +12299,7 @@ explain select * from lossycastrangepart where b::int = 10; ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=16) -> Dynamic Seq Scan on lossycastrangepart (cost=0.00..431.00 rows=1 width=16) - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 4) Filter: (int4(b) = 10) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -12316,7 +12316,7 @@ explain select * from lossycastrangepart where b::int = 11; ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=16) -> Dynamic Seq Scan on lossycastrangepart (cost=0.00..431.00 rows=1 width=16) - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 4) Filter: (int4(b) = 11) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -12333,7 +12333,7 @@ explain select * from lossycastrangepart where b::int < 10; ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=16) -> Dynamic Seq Scan on lossycastrangepart (cost=0.00..431.00 rows=1 width=16) - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 4) Filter: (int4(b) < 10) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -12350,7 +12350,7 @@ explain select * from lossycastrangepart where b::int < 11; ----------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=16) -> Dynamic Seq Scan on lossycastrangepart (cost=0.00..431.00 rows=1 width=16) - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 4) Filter: (int4(b) < 11) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -12376,7 +12376,7 @@ explain select * from lossycastlistpart where b::int < 2; ---------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=12) -> Dynamic Seq Scan on lossycastlistpart (cost=0.00..431.00 rows=1 width=12) - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (int4(b) < 2) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -12392,7 +12392,7 @@ explain select * from lossycastlistpart where b::int = 2; ---------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=12) -> Dynamic Seq Scan on lossycastlistpart (cost=0.00..431.00 rows=1 width=12) - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (int4(b) = 2) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -13368,7 +13368,7 @@ explain (costs off) select * from tpart_dim d join tpart_ao_btree f on d.a=f.a w -> Seq Scan on tpart_dim Filter: (b = 1) -> Dynamic Bitmap Heap Scan on tpart_ao_btree - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Recheck Cond: (a = tpart_dim.a) Filter: (a = tpart_dim.a) -> Dynamic Bitmap Index Scan on tpart_ao_btree_ix @@ -13420,7 +13420,7 @@ explain (costs off) select * from tpart_ao_btree where a = 3 and b = 3; ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on tpart_ao_btree - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: ((a = 3) AND (b = 3)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -13516,7 +13516,7 @@ group by asset_records.uid, asset_records.hostname, asset_records.asset_type, as -> Hash Join Hash Cond: ((upper((asset_records.hostname)::text) = upper((coverage.hostname)::text)) AND (asset_records.create_ts = coverage.date)) -> Dynamic Seq Scan on asset_records - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 5) Filter: ((upper((COALESCE(vendor, 'none'::character varying))::text) <> 'some_vendor'::text) AND (((asset_type)::text = 'xx'::text) OR ((asset_type)::text = 'yy'::text)) AND active) -> Hash -> Partition Selector (selector id: $0) @@ -13550,7 +13550,7 @@ explain (costs off) select count(*) from x, y where (x.i > y.j AND x.j <= y.i); -> Dynamic Index Scan on y_idx on y Index Cond: (j < x.i) Filter: ((j < x.i) AND (x.j <= i)) - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 5) Optimizer: Pivotal Optimizer (GPORCA) (11 rows) @@ -13577,7 +13577,7 @@ explain (costs off) select * from infer_part_vc inner join infer_txt on (infer_p -> Hash Join Hash Cond: ((infer_part_vc.gender)::text = infer_txt.a) -> Dynamic Seq Scan on infer_part_vc - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: ((gender)::text = 'M'::text) -> Hash -> Partition Selector (selector id: $0) @@ -13646,7 +13646,7 @@ explain (costs off) select * from pt where gender in ( 'F', 'FM'); ----------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on pt - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((gender)::text = ANY ('{F,FM}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -13664,16 +13664,16 @@ select * from pt where gender is null; Gather Motion 3:1 (slice1; segments: 3) -> Append -> Dynamic Seq Scan on pt - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: ((gender)::text = 'F'::text) -> Dynamic Seq Scan on pt pt_1 - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((gender)::text <= 'M'::text) -> Dynamic Seq Scan on pt pt_2 - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((gender)::text = ANY ('{F,FM}'::text[])) -> Dynamic Seq Scan on pt pt_3 - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (gender IS NULL) Optimizer: Pivotal Optimizer (GPORCA) (15 rows) @@ -13717,7 +13717,7 @@ where d.msisdn=f.subscriberaddress and -> Hash -> Broadcast Motion 3:3 (slice2; segments: 3) -> Dynamic Seq Scan on stg_xdr_crce_cdr - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Optimizer: Pivotal Optimizer (GPORCA) (11 rows) @@ -13738,7 +13738,7 @@ where month_id::text = 'Apr'; -------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on ds_4 - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: ((month_id)::text = 'Apr'::text) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -13755,7 +13755,7 @@ where month_id::text >= 'Feb' and month_id::text < 'Mar'; ------------------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on ds_4 - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Filter: (((month_id)::text >= 'Feb'::text) AND ((month_id)::text < 'Mar'::text)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -13799,7 +13799,7 @@ ORDER BY to_char(order_datetime,'YYYY-Q') -> Sort Sort Key: (to_char(order_datetime, 'YYYY-Q'::text)), item_shipment_status_code, order_id -> Dynamic Seq Scan on order_lineitems - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 12) Filter: ((order_datetime >= 'Thu Apr 01 00:00:00 2010'::timestamp without time zone) AND (order_datetime <= '06-30-2010'::date)) Optimizer: Pivotal Optimizer (GPORCA) (18 rows) diff --git a/src/test/regress/expected/inherit_optimizer.out b/src/test/regress/expected/inherit_optimizer.out index 3efa7a8e25..6177feb3b5 100644 --- a/src/test/regress/expected/inherit_optimizer.out +++ b/src/test/regress/expected/inherit_optimizer.out @@ -1882,7 +1882,7 @@ explain (costs off) select * from list_parted; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on list_parted - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Optimizer: Pivotal Optimizer (GPORCA) (4 rows) @@ -1891,7 +1891,7 @@ explain (costs off) select * from list_parted where a is null; ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on list_parted - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (a IS NULL) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1901,7 +1901,7 @@ explain (costs off) select * from list_parted where a is not null; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on list_parted - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (NOT (a IS NULL)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1911,7 +1911,7 @@ explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); ---------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on list_parted - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1921,7 +1921,7 @@ explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd' --------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on list_parted - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1931,7 +1931,7 @@ explain (costs off) select * from list_parted where a = 'ab'; ---------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on list_parted - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: ((a)::text = 'ab'::text) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -2202,7 +2202,7 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; -> Gather Motion 3:1 (slice1; segments: 3) -> Partial Aggregate -> Dynamic Seq Scan on parted_minmax - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 1) Filter: ((b)::text = '12345'::text) Optimizer: Pivotal Optimizer (GPORCA) (7 rows) @@ -2344,7 +2344,7 @@ explain (costs off) select * from mclparted order by a; -> Sort Sort Key: a -> Dynamic Seq Scan on mclparted - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Optimizer: Pivotal Optimizer (GPORCA) (7 rows) @@ -2360,7 +2360,7 @@ explain (costs off) select * from mclparted order by a; -> Sort Sort Key: a -> Dynamic Seq Scan on mclparted - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 4) Optimizer: Pivotal Optimizer (GPORCA) (7 rows) @@ -2424,7 +2424,7 @@ explain (costs off) select * from bool_lp order by b; -> Sort Sort Key: b -> Dynamic Seq Scan on bool_lp - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Optimizer: Pivotal Optimizer (GPORCA) (7 rows) diff --git a/src/test/regress/expected/orca_static_pruning_optimizer.out b/src/test/regress/expected/orca_static_pruning_optimizer.out index 46bc05e117..7991d42d9b 100644 --- a/src/test/regress/expected/orca_static_pruning_optimizer.out +++ b/src/test/regress/expected/orca_static_pruning_optimizer.out @@ -22,7 +22,7 @@ EXPLAIN (COSTS OFF, VERBOSE) Output: a, b, c -> Dynamic Seq Scan on orca_static_pruning.rp Output: a, b, c - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (rp.b > 4200) Optimizer: Pivotal Optimizer (GPORCA) Settings: optimizer=on @@ -47,7 +47,7 @@ EXPLAIN (COSTS OFF, VERBOSE) Output: a, b, c -> Dynamic Seq Scan on orca_static_pruning.rp Output: a, b, c - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (rp.b = 4201) Optimizer: Pivotal Optimizer (GPORCA) Settings: optimizer=on @@ -72,7 +72,7 @@ EXPLAIN (COSTS OFF, VERBOSE) Output: a, b, c -> Dynamic Seq Scan on orca_static_pruning.rp Output: a, b, c - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (rp.b = ANY ('{4201,4200}'::integer[])) Optimizer: Pivotal Optimizer (GPORCA) Settings: optimizer=on @@ -107,7 +107,7 @@ EXPLAIN (COSTS OFF, VERBOSE) Output: a, b -> Dynamic Seq Scan on orca_static_pruning.lp Output: a, b - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (lp.b > 42) Optimizer: Pivotal Optimizer (GPORCA) Settings: optimizer=on @@ -131,7 +131,7 @@ EXPLAIN (COSTS OFF, VERBOSE) Output: a, b -> Dynamic Seq Scan on orca_static_pruning.lp Output: a, b - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (lp.b = 42) Optimizer: Pivotal Optimizer (GPORCA) Settings: optimizer=on @@ -204,7 +204,7 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM rp_multi_inds WHERE b = 11 AND (c = 1 Output: a, b, c -> Dynamic Bitmap Heap Scan on orca_static_pruning.rp_multi_inds Output: a, b, c - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Recheck Cond: (((rp_multi_inds.c = 11) OR (rp_multi_inds.c = 4201)) AND (rp_multi_inds.b = 11)) Filter: (((rp_multi_inds.c = 11) OR (rp_multi_inds.c = 4201)) AND (rp_multi_inds.b = 11)) -> BitmapAnd @@ -258,7 +258,7 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM foo JOIN bar on foo.a = bar.a AND foo Output: foo.a, foo.b Index Cond: (foo.a = bar.a) Filter: ((foo.a = bar.a) AND (foo.b = 11)) - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Settings: enable_hashjoin = 'off', enable_mergejoin = 'off', enable_nestloop = 'on', enable_parallel = 'off', optimizer = 'on' Optimizer: Pivotal Optimizer (GPORCA) (14 rows) @@ -301,7 +301,7 @@ EXPLAIN (COSTS OFF, VERBOSE) INSERT INTO rp_insert SELECT * FROM rp_insert; Insert on orca_static_pruning.rp_insert -> Dynamic Seq Scan on orca_static_pruning.rp_insert rp_insert_1 Output: rp_insert_1.a, rp_insert_1.b - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Optimizer: Pivotal Optimizer (GPORCA) Settings: optimizer=on (6 rows) diff --git a/src/test/regress/expected/partition_prune_optimizer.out b/src/test/regress/expected/partition_prune_optimizer.out index f254fed1cd..4f0fb12ca1 100644 --- a/src/test/regress/expected/partition_prune_optimizer.out +++ b/src/test/regress/expected/partition_prune_optimizer.out @@ -38,7 +38,7 @@ explain (costs off) select * from lp; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Optimizer: Pivotal Optimizer (GPORCA) (4 rows) @@ -47,7 +47,7 @@ explain (costs off) select * from lp where a > 'a' and a < 'd'; ----------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 6) Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -57,7 +57,7 @@ explain (costs off) select * from lp where a > 'a' and a <= 'd'; ------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 6) Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -67,7 +67,7 @@ explain (costs off) select * from lp where a = 'a'; ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on lp - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 6) Filter: (a = 'a'::bpchar) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -77,7 +77,7 @@ explain (costs off) select * from lp where 'a' = a; /* commuted */ ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on lp - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 6) Filter: (a = 'a'::bpchar) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -87,7 +87,7 @@ explain (costs off) select * from lp where a is not null; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 6) Filter: (NOT (a IS NULL)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -97,7 +97,7 @@ explain (costs off) select * from lp where a is null; ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on lp - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 6) Filter: (a IS NULL) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -107,7 +107,7 @@ explain (costs off) select * from lp where a = 'a' or a = 'c'; ---------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on lp - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 6) Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -117,7 +117,7 @@ explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c' ---------------------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on lp - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 6) Filter: ((NOT (a IS NULL)) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar))) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -127,7 +127,7 @@ explain (costs off) select * from lp where a <> 'g'; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 6) Filter: (a <> 'g'::bpchar) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -137,7 +137,7 @@ explain (costs off) select * from lp where a <> 'a' and a <> 'd'; ------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 6) Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -147,7 +147,7 @@ explain (costs off) select * from lp where a not in ('a', 'd'); ------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 6) Filter: (a <> ALL ('{a,d}'::bpchar[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1180,7 +1180,7 @@ explain (costs off) select * from boolpart where a in (true, false); ------------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: (a = ANY ('{t,f}'::boolean[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1190,7 +1190,7 @@ explain (costs off) select * from boolpart where a = false; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (NOT a) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1200,7 +1200,7 @@ explain (costs off) select * from boolpart where not a = false; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: a Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1210,7 +1210,7 @@ explain (costs off) select * from boolpart where a is true or a is not true; -------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((a IS TRUE) OR (a IS NOT TRUE)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1220,7 +1220,7 @@ explain (costs off) select * from boolpart where a is not true; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (a IS NOT TRUE) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1230,7 +1230,7 @@ explain (costs off) select * from boolpart where a is not true and a is not fals -------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1240,7 +1240,7 @@ explain (costs off) select * from boolpart where a is unknown; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (a IS UNKNOWN) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1250,7 +1250,7 @@ explain (costs off) select * from boolpart where a is not unknown; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on boolpart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (a IS NOT UNKNOWN) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1286,7 +1286,7 @@ explain (costs off) select * from coercepart where a in ('ab', to_char(125, '999 ----------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: ((a)::text = ANY ('{ab," 125"}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1296,7 +1296,7 @@ explain (costs off) select * from coercepart where a ~ any ('{ab}'); ---------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((a)::text ~ ANY ('{ab}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1306,7 +1306,7 @@ explain (costs off) select * from coercepart where a !~ all ('{ab}'); ----------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((a)::text !~ ALL ('{ab}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1316,7 +1316,7 @@ explain (costs off) select * from coercepart where a ~ any ('{ab,bc}'); ------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((a)::text ~ ANY ('{ab,bc}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1326,7 +1326,7 @@ explain (costs off) select * from coercepart where a !~ all ('{ab,bc}'); -------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on coercepart - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((a)::text !~ ALL ('{ab,bc}'::text[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1559,7 +1559,7 @@ explain (costs off) select * from rp where a <> 1; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on rp - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: (a <> 1) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1569,7 +1569,7 @@ explain (costs off) select * from rp where a <> 1 and a <> 2; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on rp - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Filter: ((a <> 1) AND (a <> 2)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1580,7 +1580,7 @@ explain (costs off) select * from lp where a <> 'a'; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 6) Filter: (a <> 'a'::bpchar) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1599,7 +1599,7 @@ explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null; ------------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on lp - Number of partitions to scan: 5 + Number of partitions to scan: 5 (out of 6) Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL)) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1675,7 +1675,7 @@ explain (costs off) select * from like_op_noprune where a like '%BC'; ------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on like_op_noprune - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Filter: (a ~~ '%BC'::text) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -1713,7 +1713,7 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000; ------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on rparted_by_int2 - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (a > '100000000000000'::bigint) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -2123,7 +2123,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh ------------------------------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) (actual rows=1 loops=1) -> Dynamic Seq Scan on list_part (actual rows=1 loops=1) - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 4) Filter: (a = 1) Partitions scanned: 1 . Optimizer: Pivotal Optimizer (GPORCA) @@ -2135,7 +2135,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (actual rows=4 loops=1) -> Dynamic Seq Scan on list_part (actual rows=3 loops=1) - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 4) Filter: (a = list_part_fn(a)) Partitions scanned: Avg 4.0 x 3 workers. Max 4 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) @@ -2147,7 +2147,7 @@ explain (analyze, costs off, summary off, timing off) select * from list_part wh ------------------------------------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on list_part (actual rows=0 loops=1) - Number of partitions to scan: 4 + Number of partitions to scan: 4 (out of 4) Filter: (a = (1 + a)) Partitions scanned: Avg 4.0 x 3 workers. Max 4 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) @@ -2828,7 +2828,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; Rows Removed by Join Filter: 3 -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=7 loops=1) -> Dynamic Seq Scan on tprt (actual rows=3 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). -> Seq Scan on tbl1 (actual rows=1 loops=8) Optimizer: Pivotal Optimizer (GPORCA) @@ -2846,7 +2846,7 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; -> Hash (actual rows=3 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB -> Dynamic Seq Scan on tprt (actual rows=3 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (10 rows) @@ -2885,7 +2885,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; Rows Removed by Join Filter: 6 -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=7 loops=1) -> Dynamic Seq Scan on tprt (actual rows=3 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). -> Seq Scan on tbl1 (actual rows=3 loops=8) Optimizer: Pivotal Optimizer (GPORCA) @@ -2903,7 +2903,7 @@ select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; -> Hash (actual rows=3 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB -> Dynamic Seq Scan on tprt (actual rows=3 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (10 rows) @@ -2961,7 +2961,7 @@ select * from tbl1 join tprt on tbl1.col1 < tprt.col1; Rows Removed by Join Filter: 6 -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=7 loops=1) -> Dynamic Seq Scan on tprt (actual rows=3 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). -> Seq Scan on tbl1 (actual rows=1 loops=8) Optimizer: Pivotal Optimizer (GPORCA) @@ -2990,7 +2990,7 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; -> Hash (actual rows=3 loops=1) Buckets: 524288 Batches: 1 Memory Usage: 4097kB -> Dynamic Seq Scan on tprt (actual rows=3 loops=1) - Number of partitions to scan: 6 + Number of partitions to scan: 6 (out of 6) Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (10 rows) @@ -3138,7 +3138,7 @@ select * from stable_qual_pruning where a < localtimestamp; -------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 3) Filter: (a < 'Thu Dec 19 19:04:45.779097 2024'::timestamp without time zone) Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) @@ -3151,7 +3151,7 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; -------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (a < 'Tue Feb 01 00:00:00 2000 PST'::timestamp with time zone) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) @@ -3175,7 +3175,7 @@ select * from stable_qual_pruning ---------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[])) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) @@ -3188,7 +3188,7 @@ select * from stable_qual_pruning ----------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Thu Dec 19 19:04:45.852371 2024"}'::timestamp without time zone[])) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) @@ -3211,7 +3211,7 @@ select * from stable_qual_pruning --------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on stable_qual_pruning (actual rows=0 loops=1) - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 3) Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[])) Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) @@ -3326,7 +3326,7 @@ select * from boolp where a = (select value from boolvalues where value); Buckets: 262144 Batches: 1 Memory Usage: 2048kB -> Broadcast Motion 3:3 (slice4; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on boolp (actual rows=0 loops=1) - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (18 rows) @@ -3350,7 +3350,7 @@ select * from boolp where a = (select value from boolvalues where not value); Buckets: 262144 Batches: 1 Memory Usage: 2048kB -> Broadcast Motion 3:3 (slice4; segments: 3) (actual rows=0 loops=1) -> Dynamic Seq Scan on boolp (actual rows=0 loops=1) - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Partitions scanned: Avg 2.0 x 3 workers. Max 2 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (18 rows) @@ -3469,7 +3469,7 @@ explain (analyze, costs off, summary off, timing off) select * from ma_test wher -> Partial Aggregate (actual rows=1 loops=1) -> Seq Scan on ma_test_p2 (actual rows=5 loops=1) -> Dynamic Seq Scan on ma_test (actual rows=6 loops=2) - Number of partitions to scan: 3 + Number of partitions to scan: 3 (out of 3) Partitions scanned: Avg 2.0 x 3 workers of 2 scans. Max 2 parts (seg0). Optimizer: Pivotal Optimizer (GPORCA) (17 rows) @@ -3491,7 +3491,7 @@ explain (costs off) select * from pp_arrpart where a = '{1}'; ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on pp_arrpart - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: (a = '{1}'::integer[]) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -3509,7 +3509,7 @@ explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); ---------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on pp_arrpart - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -3591,7 +3591,7 @@ explain (costs off) select * from pp_enumpart where a = 'blue'; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on pp_enumpart - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: (a = 'blue'::pp_colors) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -3616,7 +3616,7 @@ explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; ------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on pp_recpart - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: (a = '(1,1)'::pp_rectype) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -3640,7 +3640,7 @@ explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; ------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on pp_intrangepart - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: (a = '[1,3)'::int4range) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -3665,7 +3665,7 @@ explain (costs off) select * from pp_lp where a = 1; ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on pp_lp - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: (a = 1) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -3697,7 +3697,7 @@ explain (costs off) select * from pp_lp where a = 1; ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on pp_lp - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: (a = 1) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -3736,7 +3736,7 @@ explain (costs off) select * from pp_lp where a = 1; ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on pp_lp - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: (a = 1) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) @@ -3849,7 +3849,7 @@ explain (costs off) select * from pp_temp_parent where true; ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) -> Dynamic Seq Scan on pp_temp_parent - Number of partitions to scan: 2 + Number of partitions to scan: 2 (out of 2) Optimizer: Pivotal Optimizer (GPORCA) (4 rows) @@ -3858,7 +3858,7 @@ explain (costs off) select * from pp_temp_parent where a = 2; ------------------------------------------ Gather Motion 1:1 (slice1; segments: 1) -> Dynamic Seq Scan on pp_temp_parent - Number of partitions to scan: 1 + Number of partitions to scan: 1 (out of 2) Filter: (a = 2) Optimizer: Pivotal Optimizer (GPORCA) (5 rows) --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
