| -- Tests for Dynamic Partition Elimination, or partition pruning in |
| -- PostgreSQL terms, based on join quals. |
| -- This test requires autovacuum to be disabled to guarantee a consistent state |
| -- after vacuum. An inopportune autovacuum could cause an explicit vacuum to |
| -- skip. That leads to stale relallvisible stats which may prevent picking index |
| -- only scan plan shapes. |
| -- start_matchsubs |
| -- m/Memory Usage: \d+\w?B/ |
| -- s/Memory Usage: \d+\w?B/Memory Usage: ###B/ |
| -- m/Memory: \d+kB/ |
| -- s/Memory: \d+kB/Memory: ###kB/ |
| -- m/Buckets: \d+/ |
| -- s/Buckets: \d+/Buckets: ###/ |
| -- m/Batches: \d+/ |
| -- s/Batches: \d+/Batches: ###/ |
| -- m/Extra Text: \(seg\d+\)/ |
| -- s/Extra Text: \(seg\d+\)/Extra Text: ###/ |
| -- m/Hash chain length \d+\.\d+ avg, \d+ max/ |
| -- s/Hash chain length \d+\.\d+ avg, \d+ max/Hash chain length ###/ |
| -- m/using \d+ of \d+ buckets/ |
| -- s/using \d+ of \d+ buckets/using ## of ### buckets/ |
| -- end_matchsubs |
| drop schema if exists dpe_single cascade; |
| NOTICE: schema "dpe_single" does not exist, skipping |
| create schema dpe_single; |
| set search_path='dpe_single'; |
| set gp_segments_for_planner=2; |
| set optimizer_segments=2; |
| set enable_incremental_sort=on; |
| drop table if exists pt; |
| NOTICE: table "pt" does not exist, skipping |
| drop table if exists pt1; |
| NOTICE: table "pt1" does not exist, skipping |
| drop table if exists t; |
| NOTICE: table "t" does not exist, skipping |
| drop table if exists t1; |
| NOTICE: table "t1" does not exist, skipping |
| create table pt(dist int, pt1 text, pt2 text, pt3 text, ptid int) |
| DISTRIBUTED BY (dist) |
| PARTITION BY RANGE(ptid) |
| ( |
| START (0) END (5) EVERY (1), |
| DEFAULT PARTITION junk_data |
| ) |
| ; |
| -- pt1 table is originally created distributed randomly |
| -- But a random policy impacts data distribution which |
| -- might lead to unstable stats info. Some test cases |
| -- test plan thus become flaky. We avoid flakiness by |
| -- creating the table distributed hashly and after |
| -- loading all the data, changing policy to randomly without |
| -- data movement. Thus every time we will have a static |
| -- data distribution plus randomly policy. |
| create table pt1(dist int, pt1 text, pt2 text, pt3 text, ptid int) |
| DISTRIBUTED BY (dist) |
| PARTITION BY RANGE(ptid) |
| ( |
| START (0) END (5) EVERY (1), |
| DEFAULT PARTITION junk_data |
| ) |
| ; |
| create table t(dist int, tid int, t1 text, t2 text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dist' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create index pt1_idx on pt using btree (pt1); |
| create index ptid_idx on pt using btree (ptid); |
| create index ptid_pt1_idx on pt using btree (ptid, pt1); |
| insert into pt select i, 'hello' || i, 'world', 'drop this', i % 6 from generate_series(0,53) i; |
| insert into t select i, i % 6, 'hello' || i, 'bar' from generate_series(0,1) i; |
| create table t1(dist int, tid int, t1 text, t2 text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dist' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into t1 select i, i % 6, 'hello' || i, 'bar' from generate_series(1,2) i; |
| insert into pt1 select * from pt; |
| insert into pt1 select dist, pt1, pt2, pt3, ptid-100 from pt; |
| alter table pt1 set with(REORGANIZE=false) DISTRIBUTED RANDOMLY; |
| vacuum analyze pt; |
| analyze pt1; |
| analyze t; |
| analyze t1; |
| -- |
| -- Simple positive cases |
| -- |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (10 rows) |
| |
| select * from t, pt where tid = ptid; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+---------+-------+-----------+------ |
| 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 |
| (18 rows) |
| |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where tid + 1 = ptid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (10 rows) |
| |
| select * from t, pt where tid + 1 = ptid; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+---------+-------+-----------+------ |
| 0 | 0 | hello0 | bar | 1 | hello1 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 7 | hello7 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 13 | hello13 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 19 | hello19 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 25 | hello25 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 31 | hello31 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 37 | hello37 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 43 | hello43 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 49 | hello49 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 2 | hello2 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 8 | hello8 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 14 | hello14 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 20 | hello20 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 26 | hello26 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 32 | hello32 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 38 | hello38 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 44 | hello44 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 50 | hello50 | world | drop this | 2 |
| (18 rows) |
| |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid and t1 = 'hello' || tid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> Seq Scan on t (actual rows=2 loops=1) |
| 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (11 rows) |
| |
| select * from t, pt where tid = ptid and t1 = 'hello' || tid; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+---------+-------+-----------+------ |
| 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 |
| (18 rows) |
| |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where t1 = pt1 and ptid = tid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) |
| -> Nested Loop (actual rows=2 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> Seq Scan on t (actual rows=2 loops=1) |
| -> Dynamic Index Scan on ptid_pt1_idx on pt (actual rows=1 loops=2) |
| Index Cond: ((ptid = t.tid) AND (pt1 = t.t1)) |
| 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) |
| |
| select * from t, pt where t1 = pt1 and ptid = tid; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+--------+-------+-----------+------ |
| 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 |
| 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| (2 rows) |
| |
| -- |
| -- in and exists clauses |
| -- |
| explain (costs off, timing off, summary off, analyze) select * from pt where ptid in (select tid from t where t1 = 'hello' || tid); |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) |
| -> Hash Semi Join (actual rows=8 loops=1) |
| Hash Cond: (pt.ptid = t.tid) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. |
| -> Dynamic Seq Scan on pt (actual rows=8 loops=1) |
| 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: 524288 Batches: 1 Memory Usage: 4097kB |
| -> Partition Selector (selector id: $0) (actual rows=2 loops=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> Seq Scan on t (actual rows=2 loops=1) |
| Filter: (t1 = ('hello'::text || (tid)::text)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (14 rows) |
| |
| select * from pt where ptid in (select tid from t where t1 = 'hello' || tid); |
| dist | pt1 | pt2 | pt3 | ptid |
| ------+---------+-------+-----------+------ |
| 1 | hello1 | world | drop this | 1 |
| 7 | hello7 | world | drop this | 1 |
| 13 | hello13 | world | drop this | 1 |
| 19 | hello19 | world | drop this | 1 |
| 25 | hello25 | world | drop this | 1 |
| 31 | hello31 | world | drop this | 1 |
| 37 | hello37 | world | drop this | 1 |
| 43 | hello43 | world | drop this | 1 |
| 49 | hello49 | world | drop this | 1 |
| 0 | hello0 | world | drop this | 0 |
| 6 | hello6 | world | drop this | 0 |
| 12 | hello12 | world | drop this | 0 |
| 18 | hello18 | world | drop this | 0 |
| 24 | hello24 | world | drop this | 0 |
| 30 | hello30 | world | drop this | 0 |
| 36 | hello36 | world | drop this | 0 |
| 42 | hello42 | world | drop this | 0 |
| 48 | hello48 | world | drop this | 0 |
| (18 rows) |
| |
| explain (costs off, timing off, summary off, analyze) select ptid from pt where ptid in (select tid from t where t1 = 'hello' || tid) and pt1 = 'hello1'; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=1 loops=1) |
| -> Hash Semi Join (actual rows=1 loops=1) |
| Hash Cond: (pt.ptid = t.tid) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. |
| -> Dynamic Index Only Scan on ptid_pt1_idx on pt (actual rows=1 loops=1) |
| Index Cond: (pt1 = 'hello1'::text) |
| Heap Fetches: 0 |
| 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: 524288 Batches: 1 Memory Usage: 4097kB |
| -> Partition Selector (selector id: $0) (actual rows=2 loops=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> Seq Scan on t (actual rows=2 loops=1) |
| Filter: (t1 = ('hello'::text || (tid)::text)) |
| Optimizer: GPORCA |
| (16 rows) |
| |
| select ptid from pt where ptid in (select tid from t where t1 = 'hello' || tid) and pt1 = 'hello1'; |
| ptid |
| ------ |
| 1 |
| (1 row) |
| |
| -- start_ignore |
| -- Known_opt_diff: MPP-21320 |
| -- end_ignore |
| -- Disable 'CXformSelect2DynamicIndexGet' to avoid picking Dynamic Index Scan and use this test |
| -- to showcase dpe alternative |
| select disable_xform('CXformSelect2DynamicIndexGet'); |
| disable_xform |
| ------------------------------------------ |
| CXformSelect2DynamicIndexGet is disabled |
| (1 row) |
| |
| explain (costs off, timing off, summary off, analyze) select * from pt where exists (select 1 from t where tid = ptid and t1 = 'hello' || tid); |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) |
| -> Hash Semi Join (actual rows=8 loops=1) |
| Hash Cond: (pt.ptid = t.tid) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 2 of 524288 buckets. |
| -> Dynamic Seq Scan on pt (actual rows=8 loops=1) |
| 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) |
| Buckets: 524288 Batches: 1 Memory Usage: 4097kB |
| -> Partition Selector (selector id: $0) (actual rows=2 loops=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> Seq Scan on t (actual rows=2 loops=1) |
| Filter: (t1 = ('hello'::text || (tid)::text)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (15 rows) |
| |
| select * from pt where exists (select 1 from t where tid = ptid and t1 = 'hello' || tid); |
| dist | pt1 | pt2 | pt3 | ptid |
| ------+---------+-------+-----------+------ |
| 49 | hello49 | world | drop this | 1 |
| 43 | hello43 | world | drop this | 1 |
| 37 | hello37 | world | drop this | 1 |
| 31 | hello31 | world | drop this | 1 |
| 25 | hello25 | world | drop this | 1 |
| 19 | hello19 | world | drop this | 1 |
| 13 | hello13 | world | drop this | 1 |
| 7 | hello7 | world | drop this | 1 |
| 1 | hello1 | world | drop this | 1 |
| 48 | hello48 | world | drop this | 0 |
| 42 | hello42 | world | drop this | 0 |
| 36 | hello36 | world | drop this | 0 |
| 30 | hello30 | world | drop this | 0 |
| 24 | hello24 | world | drop this | 0 |
| 18 | hello18 | world | drop this | 0 |
| 12 | hello12 | world | drop this | 0 |
| 6 | hello6 | world | drop this | 0 |
| 0 | hello0 | world | drop this | 0 |
| (18 rows) |
| |
| -- enable xform |
| select enable_xform('CXformSelect2DynamicIndexGet'); |
| enable_xform |
| ----------------------------------------- |
| CXformSelect2DynamicIndexGet is enabled |
| (1 row) |
| |
| -- |
| -- group-by on top |
| -- |
| explain (costs off, timing off, summary off, analyze) select count(*) from t, pt where tid = ptid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (actual rows=1 loops=1) |
| -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1) |
| -> Partial Aggregate (actual rows=1 loops=1) |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> Seq Scan on t (actual rows=2 loops=1) |
| -> Dynamic Index Only Scan on ptid_idx on pt (actual rows=4 loops=2) |
| Index Cond: (ptid = t.tid) |
| Heap Fetches: 0 |
| 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: GPORCA |
| (13 rows) |
| |
| select count(*) from t, pt where tid = ptid; |
| count |
| ------- |
| 18 |
| (1 row) |
| |
| -- |
| -- window function on top |
| -- |
| explain (costs off, timing off, summary off, analyze) select *, rank() over (order by ptid,pt1) from t, pt where tid = ptid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------- |
| WindowAgg (actual rows=18 loops=1) |
| Order By: pt.ptid, pt.pt1 |
| -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) |
| Merge Key: pt.ptid, pt.pt1 |
| -> Sort (actual rows=8 loops=1) |
| Sort Key: pt.ptid, pt.pt1 |
| Sort Method: quicksort Memory: 76kB |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (16 rows) |
| |
| select *, rank() over (order by ptid,pt1) from t, pt where tid = ptid; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid | rank |
| ------+-----+--------+-----+------+---------+-------+-----------+------+------ |
| 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 | 1 |
| 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 | 2 |
| 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 | 3 |
| 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 | 4 |
| 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 | 5 |
| 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 | 6 |
| 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 | 7 |
| 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 | 8 |
| 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 | 9 |
| 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 | 10 |
| 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 | 11 |
| 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 | 12 |
| 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 | 13 |
| 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 | 14 |
| 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 | 15 |
| 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 | 16 |
| 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 | 17 |
| 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 | 18 |
| (18 rows) |
| |
| -- |
| -- set ops |
| -- |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid |
| union all |
| select * from t, pt where tid + 2 = ptid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=36 loops=1) |
| -> Append (actual rows=14 loops=1) |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (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 |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (19 rows) |
| |
| select * from t, pt where tid = ptid |
| union all |
| select * from t, pt where tid + 2 = ptid; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+---------+-------+-----------+------ |
| 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 3 | hello3 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 9 | hello9 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 15 | hello15 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 21 | hello21 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 27 | hello27 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 33 | hello33 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 39 | hello39 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 45 | hello45 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 51 | hello51 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 2 | hello2 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 8 | hello8 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 14 | hello14 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 20 | hello20 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 26 | hello26 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 32 | hello32 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 38 | hello38 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 44 | hello44 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 50 | hello50 | world | drop this | 2 |
| (36 rows) |
| |
| -- |
| -- set-ops |
| -- |
| explain (costs off, timing off, summary off, analyze) select count(*) from |
| ( select * from t, pt where tid = ptid |
| union all |
| select * from t, pt where tid + 2 = ptid |
| ) foo; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (actual rows=1 loops=1) |
| -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1) |
| -> Partial Aggregate (actual rows=1 loops=1) |
| -> Append (actual rows=14 loops=1) |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (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 |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (21 rows) |
| |
| select count(*) from |
| ( select * from t, pt where tid = ptid |
| union all |
| select * from t, pt where tid + 2 = ptid |
| ) foo; |
| count |
| ------- |
| 36 |
| (1 row) |
| |
| -- |
| -- other join types (NL) |
| -- |
| set enable_hashjoin=off; |
| set enable_nestloop=on; |
| set enable_mergejoin=off; |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (10 rows) |
| |
| select * from t, pt where tid = ptid; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+---------+-------+-----------+------ |
| 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 |
| (18 rows) |
| |
| -- |
| -- index scan |
| -- |
| set enable_nestloop=on; |
| set enable_seqscan=off; |
| set enable_indexscan=on; |
| set enable_bitmapscan=off; |
| set enable_hashjoin=off; |
| -- start_ignore |
| -- Known_opt_diff: MPP-21322 |
| -- end_ignore |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid and pt1 = 'hello0'; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------ |
| Hash Join (actual rows=1 loops=1) |
| Hash Cond: (t.tid = pt.ptid) |
| Extra Text: Hash chain length 1.0 avg, 1 max, using 1 of 262144 buckets. |
| -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) |
| -> Seq Scan on t (actual rows=2 loops=1) |
| -> Hash (actual rows=1 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 2049kB |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (13 rows) |
| |
| select * from t, pt where tid = ptid and pt1 = 'hello0'; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+--------+-------+-----------+------ |
| 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 |
| (1 row) |
| |
| -- |
| -- NL Index Scan |
| -- |
| set enable_nestloop=on; |
| set enable_indexscan=on; |
| set enable_seqscan=off; |
| set enable_hashjoin=off; |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where tid = ptid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (10 rows) |
| |
| select * from t, pt where tid = ptid; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+---------+-------+-----------+------ |
| 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 6 | hello6 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 12 | hello12 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 18 | hello18 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 24 | hello24 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 30 | hello30 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 36 | hello36 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 42 | hello42 | world | drop this | 0 |
| 0 | 0 | hello0 | bar | 48 | hello48 | world | drop this | 0 |
| (18 rows) |
| |
| -- |
| -- Negative test cases where transform does not apply |
| -- |
| set enable_indexscan=off; |
| set enable_seqscan=on; |
| set enable_hashjoin=on; |
| set enable_nestloop=off; |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where t1 = pt1; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) |
| -> Nested Loop (actual rows=2 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (10 rows) |
| |
| select * from t, pt where t1 = pt1; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+--------+-------+-----------+------ |
| 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 0 | hello0 | world | drop this | 0 |
| (2 rows) |
| |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where tid < ptid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=81 loops=1) |
| -> Nested Loop (actual rows=31 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 2 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (10 rows) |
| |
| select * from t, pt where tid < ptid; |
| dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+---------+-------+-----------+------ |
| 1 | 1 | hello1 | bar | 2 | hello2 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | hello2 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 8 | hello8 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 8 | hello8 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 14 | hello14 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 14 | hello14 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 20 | hello20 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 20 | hello20 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 26 | hello26 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 26 | hello26 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 32 | hello32 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 32 | hello32 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 38 | hello38 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 38 | hello38 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 44 | hello44 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 44 | hello44 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 50 | hello50 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 50 | hello50 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 4 | hello4 | world | drop this | 4 |
| 0 | 0 | hello0 | bar | 4 | hello4 | world | drop this | 4 |
| 1 | 1 | hello1 | bar | 10 | hello10 | world | drop this | 4 |
| 0 | 0 | hello0 | bar | 10 | hello10 | world | drop this | 4 |
| 1 | 1 | hello1 | bar | 16 | hello16 | world | drop this | 4 |
| 0 | 0 | hello0 | bar | 16 | hello16 | world | drop this | 4 |
| 1 | 1 | hello1 | bar | 22 | hello22 | world | drop this | 4 |
| 0 | 0 | hello0 | bar | 22 | hello22 | world | drop this | 4 |
| 1 | 1 | hello1 | bar | 28 | hello28 | world | drop this | 4 |
| 0 | 0 | hello0 | bar | 28 | hello28 | world | drop this | 4 |
| 1 | 1 | hello1 | bar | 34 | hello34 | world | drop this | 4 |
| 0 | 0 | hello0 | bar | 34 | hello34 | world | drop this | 4 |
| 1 | 1 | hello1 | bar | 40 | hello40 | world | drop this | 4 |
| 0 | 0 | hello0 | bar | 40 | hello40 | world | drop this | 4 |
| 1 | 1 | hello1 | bar | 46 | hello46 | world | drop this | 4 |
| 0 | 0 | hello0 | bar | 46 | hello46 | world | drop this | 4 |
| 1 | 1 | hello1 | bar | 52 | hello52 | world | drop this | 4 |
| 0 | 0 | hello0 | bar | 52 | hello52 | world | drop this | 4 |
| 1 | 1 | hello1 | bar | 5 | hello5 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 5 | hello5 | world | drop this | 5 |
| 1 | 1 | hello1 | bar | 11 | hello11 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 11 | hello11 | world | drop this | 5 |
| 1 | 1 | hello1 | bar | 17 | hello17 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 17 | hello17 | world | drop this | 5 |
| 1 | 1 | hello1 | bar | 23 | hello23 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 23 | hello23 | world | drop this | 5 |
| 1 | 1 | hello1 | bar | 29 | hello29 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 29 | hello29 | world | drop this | 5 |
| 1 | 1 | hello1 | bar | 35 | hello35 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 35 | hello35 | world | drop this | 5 |
| 1 | 1 | hello1 | bar | 41 | hello41 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 41 | hello41 | world | drop this | 5 |
| 1 | 1 | hello1 | bar | 47 | hello47 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 47 | hello47 | world | drop this | 5 |
| 1 | 1 | hello1 | bar | 53 | hello53 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 53 | hello53 | world | drop this | 5 |
| 0 | 0 | hello0 | bar | 1 | hello1 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 7 | hello7 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 13 | hello13 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 19 | hello19 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 25 | hello25 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 31 | hello31 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 37 | hello37 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 43 | hello43 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 49 | hello49 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 3 | hello3 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 3 | hello3 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 9 | hello9 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 9 | hello9 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 15 | hello15 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 15 | hello15 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 21 | hello21 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 21 | hello21 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 27 | hello27 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 27 | hello27 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 33 | hello33 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 33 | hello33 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 39 | hello39 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 39 | hello39 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 45 | hello45 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 45 | hello45 | world | drop this | 3 |
| 1 | 1 | hello1 | bar | 51 | hello51 | world | drop this | 3 |
| 0 | 0 | hello0 | bar | 51 | hello51 | world | drop this | 3 |
| (81 rows) |
| |
| reset enable_indexscan; |
| reset enable_seqscan; |
| reset enable_hashjoin; |
| reset enable_nestloop; |
| -- |
| -- multiple joins |
| -- |
| -- one of the joined tables can be used for partition elimination, the other can not |
| explain (costs off, timing off, summary off, analyze) select * from t, t1, pt where t1.t2 = t.t2 and t1.tid = ptid; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------- |
| Hash Join (actual rows=36 loops=1) |
| Hash Cond: (t1.t2 = t.t2) |
| Extra Text: Hash chain length 2.0 avg, 2 max, using 1 of 262144 buckets. |
| -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=18 loops=1) |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| -> 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 (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 |
| -> Gather Motion 3:1 (slice3; segments: 3) (actual rows=2 loops=1) |
| -> Seq Scan on t (actual rows=2 loops=1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (17 rows) |
| |
| select * from t, t1, pt where t1.t2 = t.t2 and t1.tid = ptid; |
| dist | tid | t1 | t2 | dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+-----+--------+-----+------+---------+-------+-----------+------ |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 2 | 2 | hello2 | bar | 2 | hello2 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | 2 | hello2 | bar | 2 | hello2 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 2 | 2 | hello2 | bar | 8 | hello8 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | 2 | hello2 | bar | 8 | hello8 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 2 | 2 | hello2 | bar | 20 | hello20 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | 2 | hello2 | bar | 20 | hello20 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 2 | 2 | hello2 | bar | 26 | hello26 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | 2 | hello2 | bar | 26 | hello26 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 2 | 2 | hello2 | bar | 38 | hello38 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | 2 | hello2 | bar | 38 | hello38 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 2 | 2 | hello2 | bar | 44 | hello44 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | 2 | hello2 | bar | 44 | hello44 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 2 | 2 | hello2 | bar | 50 | hello50 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | 2 | hello2 | bar | 50 | hello50 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 |
| 0 | 0 | hello0 | bar | 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 2 | 2 | hello2 | bar | 14 | hello14 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | 2 | hello2 | bar | 14 | hello14 | world | drop this | 2 |
| 1 | 1 | hello1 | bar | 2 | 2 | hello2 | bar | 32 | hello32 | world | drop this | 2 |
| 0 | 0 | hello0 | bar | 2 | 2 | hello2 | bar | 32 | hello32 | world | drop this | 2 |
| (36 rows) |
| |
| -- Both joined tables can be used for partition elimination. Only partitions |
| -- that contain matching rows for both joins need to be scanned. |
| -- have to do some tricks to coerce the planner to choose the plan we want. |
| begin; |
| insert into t select i, -100, 'dummy' from generate_series(1,10) i; |
| insert into t1 select i, -100, 'dummy' from generate_series(1,10) i; |
| analyze t; |
| analyze t1; |
| explain (costs off, timing off, summary off, analyze) select * from t, t1, pt where t1.tid = ptid and t.tid = ptid; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=9 loops=1) |
| -> Hash Join (actual rows=9 loops=1) |
| Hash Cond: (t.tid = pt.ptid) |
| Extra Text: (seg1) Hash chain length 9.0 avg, 9 max, using 1 of 262144 buckets. |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) |
| Hash Key: t.tid |
| -> Seq Scan on t (actual rows=5 loops=1) |
| -> Hash (actual rows=9 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 2049kB |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (actual rows=9 loops=1) |
| Hash Key: pt.ptid |
| -> Nested Loop (actual rows=8 loops=1) |
| Join Filter: true |
| -> Broadcast Motion 3:3 (slice4; segments: 3) (actual rows=12 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers of 12 scans. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (20 rows) |
| |
| select * from t, t1, pt where t1.tid = ptid and t.tid = ptid; |
| dist | tid | t1 | t2 | dist | tid | t1 | t2 | dist | pt1 | pt2 | pt3 | ptid |
| ------+-----+--------+-----+------+-----+--------+-----+------+---------+-------+-----------+------ |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 13 | hello13 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 25 | hello25 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 43 | hello43 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 7 | hello7 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 19 | hello19 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 37 | hello37 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 1 | hello1 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 31 | hello31 | world | drop this | 1 |
| 1 | 1 | hello1 | bar | 1 | 1 | hello1 | bar | 49 | hello49 | world | drop this | 1 |
| (9 rows) |
| |
| rollback; |
| -- One non-joined table contributing to partition elimination in two different |
| -- partitioned tables |
| begin; |
| -- have to force the planner for it to consider the kind of plan we want |
| -- to test |
| set local from_collapse_limit = 1; |
| set local join_collapse_limit = 1; |
| explain (costs off, timing off, summary off, analyze) select * from t1 inner join (select pt1.*, pt2.ptid as ptid2 from pt as pt1, pt as pt2 WHERE pt1.ptid <= pt2.ptid and pt1.dist = pt2.dist ) as ptx ON t1.dist = ptx.dist and t1.tid = ptx.ptid and t1.tid = ptx.ptid2; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) |
| -> Nested Loop (actual rows=1 loops=1) |
| Join Filter: true |
| -> Nested Loop (actual rows=1 loops=1) |
| Join Filter: true |
| -> Seq Scan on t1 (actual rows=1 loops=1) |
| -> Dynamic Index Scan on ptid_idx on pt pt2 (actual rows=1 loops=1) |
| Index Cond: (ptid = t1.tid) |
| Filter: ((ptid = t1.tid) AND (t1.dist = dist)) |
| 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 pt1 (actual rows=1 loops=1) |
| Index Cond: ((ptid <= pt2.ptid) AND (ptid = pt2.ptid)) |
| Filter: ((ptid <= pt2.ptid) AND (ptid = pt2.ptid) AND (dist = pt2.dist)) |
| 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) |
| |
| rollback; |
| -- |
| -- Partitioned table on both sides of the join. This will create a result node as Append node is |
| -- not projection capable. |
| -- |
| explain (costs off, timing off, summary off, analyze) select * from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0' order by pt1.dist; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=9 loops=1) |
| Merge Key: pt1.dist |
| -> Sort (actual rows=5 loops=1) |
| Sort Key: pt1.dist |
| Sort Method: quicksort Memory: 75kB |
| -> Hash Join (actual rows=5 loops=1) |
| 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 (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 |
| -> Partition Selector (selector id: $0) (actual rows=1 loops=1) |
| -> 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 (out of 6) |
| Partitions scanned: Avg 6.0 x 3 workers. Max 6 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (20 rows) |
| |
| select * from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0' order by pt1.dist; |
| dist | pt1 | pt2 | pt3 | ptid | dist | pt1 | pt2 | pt3 | ptid |
| ------+--------+-------+-----------+------+------+---------+-------+-----------+------ |
| 0 | hello0 | world | drop this | 0 | 0 | hello0 | world | drop this | 0 |
| 0 | hello0 | world | drop this | 0 | 6 | hello6 | world | drop this | 0 |
| 0 | hello0 | world | drop this | 0 | 12 | hello12 | world | drop this | 0 |
| 0 | hello0 | world | drop this | 0 | 18 | hello18 | world | drop this | 0 |
| 0 | hello0 | world | drop this | 0 | 24 | hello24 | world | drop this | 0 |
| 0 | hello0 | world | drop this | 0 | 30 | hello30 | world | drop this | 0 |
| 0 | hello0 | world | drop this | 0 | 36 | hello36 | world | drop this | 0 |
| 0 | hello0 | world | drop this | 0 | 42 | hello42 | world | drop this | 0 |
| 0 | hello0 | world | drop this | 0 | 48 | hello48 | world | drop this | 0 |
| (9 rows) |
| |
| explain (costs off, timing off, summary off, analyze) select count(*) from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0'; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------ |
| Finalize Aggregate (actual rows=1 loops=1) |
| -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1) |
| -> Partial Aggregate (actual rows=1 loops=1) |
| -> Hash Join (actual rows=5 loops=1) |
| 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 (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 |
| -> Partition Selector (selector id: $0) (actual rows=1 loops=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1) |
| -> Dynamic Index Only Scan on ptid_pt1_idx on pt (actual rows=1 loops=1) |
| Index Cond: (pt1 = 'hello0'::text) |
| Heap Fetches: 0 |
| 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) |
| |
| select count(*) from pt, pt1 where pt.ptid = pt1.ptid and pt.pt1 = 'hello0'; |
| count |
| ------- |
| 9 |
| (1 row) |
| |
| -- |
| -- Partition Selector under Material in NestLoopJoin inner side |
| -- |
| drop table if exists pt; |
| drop table if exists t; |
| create table t(id int, a int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table pt(id int, b int) DISTRIBUTED BY (id) PARTITION BY RANGE(b) (START (0) END (5) EVERY (1)); |
| insert into t select i, i from generate_series(0,4) i; |
| insert into pt select i, i from generate_series(0,4) i; |
| analyze t; |
| analyze pt; |
| begin; |
| set enable_hashjoin=off; |
| set enable_seqscan=on; |
| set enable_nestloop=on; |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where a = b; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=5 loops=1) |
| -> Hash Join (actual rows=3 loops=1) |
| Hash Cond: (t.a = pt.b) |
| Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 3 of 524288 buckets. |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (actual rows=3 loops=1) |
| Hash Key: t.a |
| -> Seq Scan on t (actual rows=3 loops=1) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 524288 Batches: 1 Memory Usage: 4097kB |
| -> 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 (out of 5) |
| Partitions scanned: Avg 5.0 x 3 workers. Max 5 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (14 rows) |
| |
| select * from t, pt where a = b; |
| id | a | id | b |
| ----+---+----+--- |
| 0 | 0 | 0 | 0 |
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 |
| (5 rows) |
| |
| rollback; |
| -- |
| -- partition selector with 0 tuples and 0 matched partitions |
| -- |
| drop table if exists t; |
| drop table if exists pt; |
| create table t(a int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table pt(b int) DISTRIBUTED BY (b) PARTITION BY RANGE(b) |
| (START (0) END (5) EVERY (1)); |
| begin; |
| set enable_hashjoin=off; -- foring nestloop join |
| set enable_nestloop=on; |
| set enable_seqscan=on; |
| -- 7 in seg1, 8 in seg2, no data in seg0 |
| insert into t select i from generate_series(7,8) i; |
| -- 0~2 in seg0, 3~4 in seg 1, no data in seg2 |
| insert into pt select i from generate_series(0,4) i; |
| -- Insert some more rows to coerce the planner to put 'pt' on the outer |
| -- side of the join. |
| insert into t select i from generate_series(7,8) i; |
| insert into pt select 0 from generate_series(1,1000) g; |
| analyze t; |
| analyze pt; |
| explain (costs off, timing off, summary off, analyze) select * from t, pt where a = b; |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1) |
| -> 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 (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) |
| -> Seq Scan on t (actual rows=4 loops=1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (10 rows) |
| |
| select * from t, pt where a = b; |
| a | b |
| ---+--- |
| (0 rows) |
| |
| rollback; |
| -- |
| -- Multi-level partitions |
| -- |
| drop schema if exists dpe_multi cascade; |
| NOTICE: schema "dpe_multi" does not exist, skipping |
| create schema dpe_multi; |
| set search_path='dpe_multi'; |
| set gp_segments_for_planner=2; |
| set optimizer_segments=2; |
| create table dim1(dist int, pid int, code text, t1 text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dist' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into dim1 values (1, 0, 'OH', 'world1'); |
| insert into dim1 values (1, 1, 'OH', 'world2'); |
| insert into dim1 values (1, 100, 'GA', 'world2'); -- should not have a match at all |
| analyze dim1; |
| create table fact1(dist int, pid int, code text, u int) |
| partition by range(pid) |
| subpartition by list(code) |
| subpartition template |
| ( |
| subpartition ca values('CA'), |
| subpartition oh values('OH'), |
| subpartition wa values('WA') |
| ) |
| ( |
| start (0) |
| end (4) |
| every (1) |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dist' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into fact1 select 1, i % 4 , 'OH', i from generate_series (1,100) i; |
| insert into fact1 select 1, i % 4 , 'CA', i + 10000 from generate_series (1,100) i; |
| -- |
| -- Join on all partitioning columns |
| -- |
| set gp_dynamic_partition_pruning=off; |
| explain (costs off, timing off, summary off, analyze) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid and dim1.code=fact1.code) order by fact1.u; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=50 loops=1) |
| Merge Key: fact1.u |
| -> Sort (actual rows=50 loops=1) |
| Sort Key: fact1.u |
| Sort Method: quicksort Memory: 158kB |
| -> Hash Join (actual rows=50 loops=1) |
| Hash Cond: ((fact1.pid = dim1.pid) AND (fact1.code = dim1.code)) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 3 of 131072 buckets. |
| -> Append (actual rows=200 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1_1 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1_3 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1_4 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_5 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1_6 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1_7 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_8 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1_9 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1_10 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_11 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1_12 (actual rows=0 loops=1) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 131072 Batches: 1 Memory Usage: 1025kB |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=3 loops=1) |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (26 rows) |
| |
| select * from dim1 inner join fact1 on (dim1.pid=fact1.pid and dim1.code=fact1.code) order by fact1.u; |
| dist | pid | code | t1 | dist | pid | code | u |
| ------+-----+------+--------+------+-----+------+----- |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 1 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 4 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 5 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 8 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 9 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 12 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 13 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 16 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 17 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 20 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 21 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 24 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 25 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 28 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 29 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 32 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 33 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 36 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 37 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 40 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 41 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 44 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 45 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 48 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 49 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 52 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 53 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 56 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 57 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 60 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 61 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 64 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 65 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 68 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 69 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 72 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 73 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 76 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 77 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 80 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 81 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 84 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 85 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 88 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 89 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 92 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 93 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 96 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 97 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 100 |
| (50 rows) |
| |
| set gp_dynamic_partition_pruning=on; |
| explain (costs off, timing off, summary off, analyze) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid and dim1.code=fact1.code) order by fact1.u; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=50 loops=1) |
| Merge Key: fact1.u |
| -> Sort (actual rows=50 loops=1) |
| Sort Key: fact1.u |
| Sort Method: quicksort Memory: 158kB |
| -> Hash Join (actual rows=50 loops=1) |
| Hash Cond: ((fact1.pid = dim1.pid) AND (fact1.code = dim1.code)) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 3 of 131072 buckets. |
| -> Append (actual rows=50 loops=1) |
| Partition Selectors: $0 |
| -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1_1 (never executed) |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1_3 (never executed) |
| -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1_4 (never executed) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_5 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1_6 (never executed) |
| -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1_7 (never executed) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_8 (never executed) |
| -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1_9 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1_10 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_11 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1_12 (never executed) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 131072 Batches: 1 Memory Usage: 1025kB |
| -> Partition Selector (selector id: $0) (actual rows=3 loops=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=3 loops=1) |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (27 rows) |
| |
| select * from dim1 inner join fact1 on (dim1.pid=fact1.pid and dim1.code=fact1.code) order by fact1.u; |
| dist | pid | code | t1 | dist | pid | code | u |
| ------+-----+------+--------+------+-----+------+----- |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 1 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 4 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 5 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 8 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 9 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 12 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 13 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 16 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 17 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 20 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 21 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 24 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 25 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 28 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 29 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 32 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 33 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 36 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 37 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 40 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 41 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 44 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 45 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 48 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 49 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 52 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 53 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 56 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 57 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 60 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 61 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 64 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 65 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 68 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 69 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 72 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 73 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 76 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 77 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 80 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 81 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 84 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 85 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 88 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 89 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 92 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 93 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 96 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 97 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 100 |
| (50 rows) |
| |
| -- |
| -- Join on one of the partitioning columns |
| -- |
| set gp_dynamic_partition_pruning=off; |
| explain (costs off, timing off, summary off, analyze) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) order by fact1.u; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=100 loops=1) |
| Merge Key: fact1.u |
| -> Sort (actual rows=100 loops=1) |
| Sort Key: fact1.u |
| Sort Method: quicksort Memory: 166kB |
| -> Hash Join (actual rows=100 loops=1) |
| Hash Cond: (fact1.pid = dim1.pid) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 3 of 131072 buckets. |
| -> Append (actual rows=200 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1_1 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1_3 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1_4 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_5 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1_6 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1_7 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_8 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1_9 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1_10 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_11 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1_12 (actual rows=0 loops=1) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 131072 Batches: 1 Memory Usage: 1025kB |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=3 loops=1) |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (26 rows) |
| |
| select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) order by fact1.u; |
| dist | pid | code | t1 | dist | pid | code | u |
| ------+-----+------+--------+------+-----+------+------- |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 1 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 4 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 5 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 8 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 9 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 12 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 13 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 16 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 17 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 20 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 21 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 24 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 25 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 28 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 29 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 32 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 33 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 36 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 37 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 40 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 41 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 44 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 45 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 48 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 49 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 52 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 53 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 56 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 57 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 60 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 61 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 64 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 65 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 68 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 69 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 72 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 73 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 76 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 77 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 80 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 81 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 84 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 85 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 88 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 89 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 92 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 93 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 96 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 97 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 100 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10001 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10004 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10005 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10008 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10009 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10012 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10013 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10016 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10017 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10020 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10021 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10024 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10025 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10028 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10029 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10032 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10033 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10036 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10037 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10040 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10041 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10044 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10045 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10048 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10049 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10052 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10053 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10056 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10057 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10060 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10061 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10064 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10065 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10068 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10069 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10072 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10073 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10076 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10077 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10080 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10081 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10084 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10085 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10088 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10089 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10092 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10093 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10096 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10097 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10100 |
| (100 rows) |
| |
| set gp_dynamic_partition_pruning=on; |
| explain (costs off, timing off, summary off, analyze) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) order by fact1.u; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=100 loops=1) |
| Merge Key: fact1.u |
| -> Sort (actual rows=100 loops=1) |
| Sort Key: fact1.u |
| Sort Method: quicksort Memory: 166kB |
| -> Hash Join (actual rows=100 loops=1) |
| Hash Cond: (fact1.pid = dim1.pid) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 3 of 131072 buckets. |
| -> Append (actual rows=100 loops=1) |
| Partition Selectors: $0 |
| -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1_1 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1_3 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1_4 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_5 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1_6 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1_7 (never executed) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_8 (never executed) |
| -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1_9 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1_10 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_11 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1_12 (never executed) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 131072 Batches: 1 Memory Usage: 1025kB |
| -> Partition Selector (selector id: $0) (actual rows=3 loops=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=3 loops=1) |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (28 rows) |
| |
| select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) order by fact1.u; |
| dist | pid | code | t1 | dist | pid | code | u |
| ------+-----+------+--------+------+-----+------+------- |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 1 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 4 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 5 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 8 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 9 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 12 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 13 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 16 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 17 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 20 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 21 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 24 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 25 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 28 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 29 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 32 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 33 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 36 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 37 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 40 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 41 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 44 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 45 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 48 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 49 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 52 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 53 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 56 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 57 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 60 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 61 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 64 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 65 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 68 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 69 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 72 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 73 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 76 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 77 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 80 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 81 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 84 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 85 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 88 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 89 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 92 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 93 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 96 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 97 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 100 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10001 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10004 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10005 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10008 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10009 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10012 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10013 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10016 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10017 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10020 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10021 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10024 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10025 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10028 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10029 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10032 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10033 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10036 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10037 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10040 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10041 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10044 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10045 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10048 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10049 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10052 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10053 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10056 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10057 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10060 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10061 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10064 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10065 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10068 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10069 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10072 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10073 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10076 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10077 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10080 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10081 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10084 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10085 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10088 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10089 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10092 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10093 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10096 |
| 1 | 1 | OH | world2 | 1 | 1 | CA | 10097 |
| 1 | 0 | OH | world1 | 1 | 0 | CA | 10100 |
| (100 rows) |
| |
| -- |
| -- Join on the subpartitioning column only |
| -- |
| set gp_dynamic_partition_pruning=off; |
| explain (costs off, timing off, summary off, analyze) |
| select * from dim1 inner join fact1 on (dim1.dist = fact1.dist and dim1.code=fact1.code); |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=200 loops=1) |
| -> Hash Join (actual rows=200 loops=1) |
| Hash Cond: ((fact1.dist = dim1.dist) AND (fact1.code = dim1.code)) |
| Extra Text: (seg1) Hash chain length 1.5 avg, 2 max, using 2 of 262144 buckets. |
| -> Append (actual rows=200 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1_1 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1_3 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1_4 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_5 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1_6 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1_7 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_8 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1_9 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1_10 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_11 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1_12 (actual rows=0 loops=1) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 2049kB |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (21 rows) |
| |
| select * from dim1 inner join fact1 on (dim1.dist = fact1.dist and dim1.code=fact1.code); |
| dist | pid | code | t1 | dist | pid | code | u |
| ------+-----+------+--------+------+-----+------+----- |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 4 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 4 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 8 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 8 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 12 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 12 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 16 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 16 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 20 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 20 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 24 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 24 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 28 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 28 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 32 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 32 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 36 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 36 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 40 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 40 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 44 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 44 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 48 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 48 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 52 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 52 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 56 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 56 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 60 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 60 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 64 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 64 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 68 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 68 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 72 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 72 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 76 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 76 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 80 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 80 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 84 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 84 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 88 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 88 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 92 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 92 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 96 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 96 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 100 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 100 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 1 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 1 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 5 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 5 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 9 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 9 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 13 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 13 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 17 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 17 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 21 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 21 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 25 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 25 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 29 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 29 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 33 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 33 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 37 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 37 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 41 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 41 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 45 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 45 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 49 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 49 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 53 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 53 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 57 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 57 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 61 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 61 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 65 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 65 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 69 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 69 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 73 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 73 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 77 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 77 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 81 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 81 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 85 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 85 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 89 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 89 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 93 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 93 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 97 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 97 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 2 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 2 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 6 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 6 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 10 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 10 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 14 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 14 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 18 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 18 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 22 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 22 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 26 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 26 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 30 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 30 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 34 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 34 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 38 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 38 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 42 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 42 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 46 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 46 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 50 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 50 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 54 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 54 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 58 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 58 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 62 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 62 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 66 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 66 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 70 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 70 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 74 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 74 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 78 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 78 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 82 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 82 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 86 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 86 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 90 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 90 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 94 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 94 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 98 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 98 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 3 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 3 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 7 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 7 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 11 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 11 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 15 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 15 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 19 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 19 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 23 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 23 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 27 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 27 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 31 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 31 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 35 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 35 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 39 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 39 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 43 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 43 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 47 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 47 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 51 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 51 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 55 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 55 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 59 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 59 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 63 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 63 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 67 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 67 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 71 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 71 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 75 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 75 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 79 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 79 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 83 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 83 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 87 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 87 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 91 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 91 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 95 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 95 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 99 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 99 |
| (200 rows) |
| |
| set gp_dynamic_partition_pruning=on; |
| explain (costs off, timing off, summary off, analyze) |
| select * from dim1 inner join fact1 on (dim1.dist = fact1.dist and dim1.code=fact1.code); |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=200 loops=1) |
| -> Hash Join (actual rows=200 loops=1) |
| Hash Cond: ((fact1.dist = dim1.dist) AND (fact1.code = dim1.code)) |
| Extra Text: (seg1) Hash chain length 1.5 avg, 2 max, using 2 of 262144 buckets. |
| -> Append (actual rows=100 loops=1) |
| Partition Selectors: $0 |
| -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1_1 (never executed) |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1_3 (never executed) |
| -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1_4 (never executed) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_5 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1_6 (never executed) |
| -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1_7 (never executed) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_8 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1_9 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1_10 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_11 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1_12 (never executed) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 2049kB |
| -> Partition Selector (selector id: $0) (actual rows=3 loops=1) |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (22 rows) |
| |
| select * from dim1 inner join fact1 on (dim1.dist = fact1.dist and dim1.code=fact1.code); |
| dist | pid | code | t1 | dist | pid | code | u |
| ------+-----+------+--------+------+-----+------+----- |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 4 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 4 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 8 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 8 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 12 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 12 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 16 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 16 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 20 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 20 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 24 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 24 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 28 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 28 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 32 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 32 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 36 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 36 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 40 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 40 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 44 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 44 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 48 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 48 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 52 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 52 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 56 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 56 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 60 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 60 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 64 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 64 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 68 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 68 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 72 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 72 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 76 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 76 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 80 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 80 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 84 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 84 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 88 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 88 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 92 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 92 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 96 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 96 |
| 1 | 1 | OH | world2 | 1 | 0 | OH | 100 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 100 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 1 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 1 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 5 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 5 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 9 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 9 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 13 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 13 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 17 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 17 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 21 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 21 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 25 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 25 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 29 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 29 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 33 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 33 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 37 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 37 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 41 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 41 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 45 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 45 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 49 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 49 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 53 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 53 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 57 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 57 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 61 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 61 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 65 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 65 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 69 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 69 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 73 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 73 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 77 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 77 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 81 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 81 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 85 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 85 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 89 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 89 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 93 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 93 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 97 |
| 1 | 0 | OH | world1 | 1 | 1 | OH | 97 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 2 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 2 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 6 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 6 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 10 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 10 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 14 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 14 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 18 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 18 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 22 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 22 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 26 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 26 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 30 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 30 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 34 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 34 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 38 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 38 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 42 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 42 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 46 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 46 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 50 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 50 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 54 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 54 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 58 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 58 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 62 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 62 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 66 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 66 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 70 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 70 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 74 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 74 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 78 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 78 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 82 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 82 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 86 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 86 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 90 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 90 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 94 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 94 |
| 1 | 1 | OH | world2 | 1 | 2 | OH | 98 |
| 1 | 0 | OH | world1 | 1 | 2 | OH | 98 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 3 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 3 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 7 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 7 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 11 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 11 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 15 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 15 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 19 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 19 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 23 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 23 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 27 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 27 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 31 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 31 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 35 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 35 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 39 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 39 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 43 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 43 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 47 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 47 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 51 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 51 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 55 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 55 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 59 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 59 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 63 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 63 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 67 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 67 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 71 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 71 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 75 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 75 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 79 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 79 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 83 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 83 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 87 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 87 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 91 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 91 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 95 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 95 |
| 1 | 1 | OH | world2 | 1 | 3 | OH | 99 |
| 1 | 0 | OH | world1 | 1 | 3 | OH | 99 |
| (200 rows) |
| |
| -- |
| -- Join on one of the partitioning columns and static elimination on other |
| -- |
| set gp_dynamic_partition_pruning=off; |
| explain (costs off, timing off, summary off, analyze) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) and fact1.code = 'OH' order by fact1.u; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=50 loops=1) |
| Merge Key: fact1.u |
| -> Sort (actual rows=50 loops=1) |
| Sort Key: fact1.u |
| Sort Method: quicksort Memory: 158kB |
| -> Hash Join (actual rows=50 loops=1) |
| Hash Cond: (fact1.pid = dim1.pid) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 3 of 131072 buckets. |
| -> Append (actual rows=100 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_1 (actual rows=25 loops=1) |
| Filter: (code = 'OH'::text) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| Filter: (code = 'OH'::text) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_3 (actual rows=25 loops=1) |
| Filter: (code = 'OH'::text) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_4 (actual rows=25 loops=1) |
| Filter: (code = 'OH'::text) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 131072 Batches: 1 Memory Usage: 1025kB |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=3 loops=1) |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (21 rows) |
| |
| select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) and fact1.code = 'OH' order by fact1.u; |
| dist | pid | code | t1 | dist | pid | code | u |
| ------+-----+------+--------+------+-----+------+----- |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 1 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 4 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 5 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 8 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 9 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 12 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 13 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 16 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 17 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 20 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 21 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 24 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 25 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 28 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 29 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 32 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 33 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 36 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 37 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 40 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 41 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 44 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 45 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 48 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 49 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 52 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 53 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 56 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 57 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 60 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 61 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 64 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 65 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 68 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 69 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 72 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 73 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 76 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 77 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 80 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 81 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 84 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 85 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 88 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 89 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 92 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 93 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 96 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 97 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 100 |
| (50 rows) |
| |
| set gp_dynamic_partition_pruning=on; |
| explain (costs off, timing off, summary off, analyze) select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) and fact1.code = 'OH' order by fact1.u; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=50 loops=1) |
| Merge Key: fact1.u |
| -> Sort (actual rows=50 loops=1) |
| Sort Key: fact1.u |
| Sort Method: quicksort Memory: 158kB |
| -> Hash Join (actual rows=50 loops=1) |
| Hash Cond: (fact1.pid = dim1.pid) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 3 of 131072 buckets. |
| -> Append (actual rows=50 loops=1) |
| Partition Selectors: $0 |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_1 (actual rows=25 loops=1) |
| Filter: (code = 'OH'::text) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| Filter: (code = 'OH'::text) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_3 (never executed) |
| Filter: (code = 'OH'::text) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_4 (never executed) |
| Filter: (code = 'OH'::text) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 131072 Batches: 1 Memory Usage: 1025kB |
| -> Partition Selector (selector id: $0) (actual rows=3 loops=1) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=3 loops=1) |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (23 rows) |
| |
| select * from dim1 inner join fact1 on (dim1.pid=fact1.pid) and fact1.code = 'OH' order by fact1.u; |
| dist | pid | code | t1 | dist | pid | code | u |
| ------+-----+------+--------+------+-----+------+----- |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 1 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 4 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 5 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 8 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 9 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 12 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 13 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 16 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 17 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 20 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 21 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 24 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 25 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 28 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 29 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 32 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 33 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 36 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 37 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 40 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 41 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 44 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 45 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 48 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 49 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 52 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 53 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 56 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 57 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 60 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 61 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 64 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 65 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 68 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 69 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 72 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 73 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 76 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 77 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 80 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 81 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 84 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 85 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 88 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 89 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 92 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 93 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 96 |
| 1 | 1 | OH | world2 | 1 | 1 | OH | 97 |
| 1 | 0 | OH | world1 | 1 | 0 | OH | 100 |
| (50 rows) |
| |
| -- |
| -- add aggregates |
| -- |
| set gp_dynamic_partition_pruning=off; |
| explain (costs off, timing off, summary off, analyze) select fact1.code, count(*) from dim1 inner join fact1 on (dim1.pid=fact1.pid) group by 1 order by 1; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) |
| Merge Key: fact1.code |
| -> GroupAggregate (actual rows=2 loops=1) |
| Group Key: fact1.code |
| -> Sort (actual rows=100 loops=1) |
| Sort Key: fact1.code |
| Sort Method: quicksort Memory: 160kB |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (actual rows=100 loops=1) |
| Hash Key: fact1.code |
| -> Hash Join (actual rows=100 loops=1) |
| Hash Cond: (fact1.pid = dim1.pid) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 3 of 262144 buckets. |
| -> Append (actual rows=200 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1_1 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1_3 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1_4 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_5 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1_6 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1_7 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_8 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1_9 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1_10 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_11 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1_12 (actual rows=0 loops=1) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 2049kB |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=3 loops=1) |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (29 rows) |
| |
| select fact1.code, count(*) from dim1 inner join fact1 on (dim1.pid=fact1.pid) group by 1 order by 1; |
| code | count |
| ------+------- |
| CA | 50 |
| OH | 50 |
| (2 rows) |
| |
| set gp_dynamic_partition_pruning=on; |
| explain (costs off, timing off, summary off, analyze) select fact1.code, count(*) from dim1 inner join fact1 on (dim1.pid=fact1.pid) group by 1 order by 1; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) |
| Merge Key: fact1.code |
| -> GroupAggregate (actual rows=2 loops=1) |
| Group Key: fact1.code |
| -> Sort (actual rows=100 loops=1) |
| Sort Key: fact1.code |
| Sort Method: quicksort Memory: 160kB |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (actual rows=100 loops=1) |
| Hash Key: fact1.code |
| -> Hash Join (actual rows=100 loops=1) |
| Hash Cond: (fact1.pid = dim1.pid) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 3 of 262144 buckets. |
| -> Append (actual rows=100 loops=1) |
| Partition Selectors: $0 |
| -> Seq Scan on fact1_1_prt_1_2_prt_ca fact1_1 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_oh fact1_2 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_1_2_prt_wa fact1_3 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_ca fact1_4 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_oh fact1_5 (actual rows=25 loops=1) |
| -> Seq Scan on fact1_1_prt_2_2_prt_wa fact1_6 (actual rows=0 loops=1) |
| -> Seq Scan on fact1_1_prt_3_2_prt_ca fact1_7 (never executed) |
| -> Seq Scan on fact1_1_prt_3_2_prt_oh fact1_8 (never executed) |
| -> Seq Scan on fact1_1_prt_3_2_prt_wa fact1_9 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_ca fact1_10 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_oh fact1_11 (never executed) |
| -> Seq Scan on fact1_1_prt_4_2_prt_wa fact1_12 (never executed) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 2049kB |
| -> Partition Selector (selector id: $0) (actual rows=3 loops=1) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=3 loops=1) |
| -> Seq Scan on dim1 (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (31 rows) |
| |
| select fact1.code, count(*) from dim1 inner join fact1 on (dim1.pid=fact1.pid) group by 1 order by 1; |
| code | count |
| ------+------- |
| CA | 50 |
| OH | 50 |
| (2 rows) |
| |
| -- |
| -- multi-attribute list partitioning |
| -- |
| -- Before GPDB 7, we used to support multi-column list partitions natively, |
| -- and these queries did partition elimination. We don't support that anymore, |
| -- but emulate that by using a row expression as the partitioning key. You |
| -- don't get partition elimination with that, however, so these tests are not |
| -- very interesting anymore. |
| -- |
| drop schema if exists dpe_malp cascade; |
| NOTICE: schema "dpe_malp" does not exist, skipping |
| create schema dpe_malp; |
| set search_path='dpe_malp'; |
| set gp_segments_for_planner=2; |
| set optimizer_segments=2; |
| create type malp_key as (i int, j int); |
| create table malp (i int, j int, t text) |
| distributed by (i) |
| partition by list ((row(i, j)::malp_key)); |
| create table malp_p1 partition of malp for values in (row(1, 10)); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table malp_p2 partition of malp for values in (row(2, 20)); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table malp_p3 partition of malp for values in (row(3, 30)); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| insert into malp select 1, 10, 'hello1'; |
| insert into malp select 1, 10, 'hello2'; |
| insert into malp select 1, 10, 'hello3'; |
| insert into malp select 2, 20, 'hello4'; |
| insert into malp select 2, 20, 'hello5'; |
| insert into malp select 3, 30, 'hello6'; |
| create table dim(i int, j int) |
| distributed randomly; |
| insert into dim values(1, 10); |
| analyze malp; |
| analyze dim; |
| -- ORCA doesn't do multi-attribute partitioning currently,so this falls |
| -- back to the Postgres planner |
| explain (costs off, timing off, summary off, analyze) select * from dim inner join malp on (dim.i = malp.i); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1) |
| -> Hash Join (actual rows=3 loops=1) |
| Hash Cond: (malp.i = dim.i) |
| Extra Text: (seg1) Hash chain length 1.0 avg, 1 max, using 1 of 524288 buckets. |
| -> Append (actual rows=3 loops=1) |
| -> Seq Scan on malp_p1 malp_1 (actual rows=3 loops=1) |
| -> Seq Scan on malp_p2 malp_2 (actual rows=0 loops=1) |
| -> Seq Scan on malp_p3 malp_3 (actual rows=0 loops=1) |
| -> Hash (actual rows=1 loops=1) |
| Buckets: 524288 Batches: 1 Memory Usage: 4097kB |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1) |
| Hash Key: dim.i |
| -> Seq Scan on dim (actual rows=1 loops=1) |
| Optimizer: Postgres query optimizer |
| (14 rows) |
| |
| set gp_dynamic_partition_pruning = off; |
| select * from dim inner join malp on (dim.i = malp.i); |
| i | j | i | j | t |
| ---+----+---+----+-------- |
| 1 | 10 | 1 | 10 | hello1 |
| 1 | 10 | 1 | 10 | hello2 |
| 1 | 10 | 1 | 10 | hello3 |
| (3 rows) |
| |
| set gp_dynamic_partition_pruning = on; |
| select * from dim inner join malp on (dim.i = malp.i); |
| i | j | i | j | t |
| ---+----+---+----+-------- |
| 1 | 10 | 1 | 10 | hello1 |
| 1 | 10 | 1 | 10 | hello2 |
| 1 | 10 | 1 | 10 | hello3 |
| (3 rows) |
| |
| set gp_dynamic_partition_pruning = on; |
| -- if only the planner was smart enough, one partition would be chosen |
| select * from dim inner join malp on (dim.i = malp.i and dim.j = malp.j); |
| i | j | i | j | t |
| ---+----+---+----+-------- |
| 1 | 10 | 1 | 10 | hello1 |
| 1 | 10 | 1 | 10 | hello2 |
| 1 | 10 | 1 | 10 | hello3 |
| (3 rows) |
| |
| -- |
| -- Plan where the Append that the PartitionSelector affects is not the immediate child |
| -- of the join. |
| -- |
| create table apart (id int4, t text) partition by range (id) (start (1) end (1000) every (200)); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table b (id int4, t text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table c (id int4, t text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into apart select g, g from generate_series(1, 999) g; |
| insert into b select g, g from generate_series(1, 5) g; |
| insert into c select g, g from generate_series(1, 20) g; |
| analyze apart; |
| analyze b; |
| analyze c; |
| set gp_dynamic_partition_pruning = off; |
| explain (costs off, timing off, summary off, analyze) select * from apart as a, b, c where a.t = b.t and a.id = c.id; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=5 loops=1) |
| -> Hash Join (actual rows=3 loops=1) |
| Hash Cond: (a.id = c.id) |
| Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 8 of 262144 buckets. |
| -> Hash Join (actual rows=3 loops=1) |
| Hash Cond: (a.t = b.t) |
| Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 5 of 262144 buckets. |
| -> Dynamic Seq Scan on apart a (actual rows=70 loops=1) |
| 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 |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=5 loops=1) |
| -> Seq Scan on b (actual rows=3 loops=1) |
| -> Hash (actual rows=8 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 2049kB |
| -> Partition Selector (selector id: $0) (actual rows=8 loops=1) |
| -> Seq Scan on c (actual rows=8 loops=1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (17 rows) |
| |
| select * from apart as a, b, c where a.t = b.t and a.id = c.id; |
| id | t | id | t | id | t |
| ----+---+----+---+----+--- |
| 3 | 3 | 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 | 4 | 4 |
| 5 | 5 | 5 | 5 | 5 | 5 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 |
| (5 rows) |
| |
| set gp_dynamic_partition_pruning = on; |
| explain (costs off, timing off, summary off, analyze) select * from apart as a, b, c where a.t = b.t and a.id = c.id; |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=5 loops=1) |
| -> Hash Join (actual rows=3 loops=1) |
| Hash Cond: (a.id = c.id) |
| Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 8 of 262144 buckets. |
| -> Hash Join (actual rows=3 loops=1) |
| Hash Cond: (a.t = b.t) |
| Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 5 of 262144 buckets. |
| -> Dynamic Seq Scan on apart a (actual rows=70 loops=1) |
| 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 |
| -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=5 loops=1) |
| -> Seq Scan on b (actual rows=3 loops=1) |
| -> Hash (actual rows=8 loops=1) |
| Buckets: 262144 Batches: 1 Memory Usage: 2049kB |
| -> Partition Selector (selector id: $0) (actual rows=8 loops=1) |
| -> Seq Scan on c (actual rows=8 loops=1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (17 rows) |
| |
| select * from apart as a, b, c where a.t = b.t and a.id = c.id; |
| id | t | id | t | id | t |
| ----+---+----+---+----+--- |
| 3 | 3 | 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 | 4 | 4 |
| 5 | 5 | 5 | 5 | 5 | 5 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 |
| (5 rows) |
| |
| -- |
| -- DPE: assertion failed with window function |
| -- |
| drop schema if exists dpe_bugs cascade; |
| NOTICE: schema "dpe_bugs" does not exist, skipping |
| create schema dpe_bugs; |
| set search_path='dpe_bugs'; |
| set gp_segments_for_planner=2; |
| set optimizer_segments=2; |
| create table pat(a int, b date) partition by range (b) (start ('2010-01-01') end ('2010-01-05') every (1), default partition other); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into pat select i,date '2010-01-01' + i from generate_series(1, 10)i; |
| create table jpat(a int, b date); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into jpat values(1, '2010-01-02'); |
| analyze jpat; |
| -- start_ignore |
| -- Known_opt_diff: MPP-21323 |
| -- end_ignore |
| explain (costs off, timing off, summary off, analyze) select * from (select count(*) over (order by a rows between 1 preceding and 1 following), a, b from jpat)jpat inner join pat using(b); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------ |
| Hash Join (actual rows=1 loops=1) |
| Hash Cond: (jpat.b = pat.b) |
| Extra Text: Hash chain length 1.0 avg, 1 max, using 10 of 131072 buckets. |
| -> WindowAgg (actual rows=1 loops=1) |
| Order By: jpat.a |
| -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=1 loops=1) |
| Merge Key: jpat.a |
| -> Sort (actual rows=1 loops=1) |
| Sort Key: jpat.a |
| Sort Method: quicksort Memory: 150kB |
| -> Seq Scan on jpat (actual rows=1 loops=1) |
| -> Hash (actual rows=10 loops=1) |
| 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 (out of 5) |
| Partitions scanned: Avg 5.0 x 3 workers. Max 5 parts (seg0). |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (17 rows) |
| |
| select * from (select count(*) over (order by a rows between 1 preceding and 1 following), a, b from jpat)jpat inner join pat using(b); |
| b | count | a | a |
| ------------+-------+---+--- |
| 01-02-2010 | 1 | 1 | 1 |
| (1 row) |
| |
| -- |
| -- Partitioning on an expression |
| -- |
| drop table if exists t; |
| NOTICE: table "t" does not exist, skipping |
| drop table if exists pt; |
| NOTICE: table "pt" does not exist, skipping |
| create table t(id int, b int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table pt(id int, b int) partition by range (id); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table pt1 partition of pt for values from (1) to (2); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt2 partition of pt for values from (2) to (3); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt3 partition of pt for values from (3) to (4); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table ptx (id int, b int) partition by list (((b) % 2)); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create table ptx_even partition of ptx for values in (0); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table ptx_odd partition of ptx for values in (1); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| alter table pt attach partition ptx for values from (4) to (20); |
| insert into t values (1, 1); |
| insert into t values (2, 2); |
| insert into pt select i, i from generate_series(1,7) i; |
| analyze t; |
| analyze pt; |
| -- Prune on the simple partition columns, but not on the expression |
| explain (analyze, costs off, timing off, summary off) |
| select * from pt, t where t.id = pt.id; |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1) |
| -> Hash Join (actual rows=1 loops=1) |
| Hash Cond: (pt.id = t.id) |
| Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 524288 buckets. |
| -> Append (actual rows=1 loops=1) |
| Partition Selectors: $0 |
| -> Seq Scan on pt1 pt_1 (actual rows=1 loops=1) |
| -> Seq Scan on pt2 pt_2 (actual rows=1 loops=1) |
| -> Seq Scan on pt3 pt_3 (never executed) |
| -> Seq Scan on ptx_even pt_4 (never executed) |
| -> Seq Scan on ptx_odd pt_5 (never executed) |
| -> Hash (actual rows=1 loops=1) |
| Buckets: 524288 Batches: 1 Memory Usage: 4097kB |
| -> Partition Selector (selector id: $0) (actual rows=1 loops=1) |
| -> Seq Scan on t (actual rows=1 loops=1) |
| Optimizer: Postgres query optimizer |
| (15 rows) |
| |
| insert into t values (4, 4), (6, 6), (8, 8), (10, 10); |
| explain (analyze, costs off, timing off, summary off) |
| select * from pt, t where t.id = pt.id; |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=4 loops=1) |
| -> Hash Join (actual rows=2 loops=1) |
| Hash Cond: (pt.id = t.id) |
| Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 3 of 524288 buckets. |
| -> Append (actual rows=3 loops=1) |
| Partition Selectors: $0 |
| -> Seq Scan on pt1 pt_1 (actual rows=1 loops=1) |
| -> Seq Scan on pt2 pt_2 (actual rows=1 loops=1) |
| -> Seq Scan on pt3 pt_3 (never executed) |
| -> Seq Scan on ptx_even pt_4 (actual rows=1 loops=1) |
| -> Seq Scan on ptx_odd pt_5 (actual rows=1 loops=1) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 524288 Batches: 1 Memory Usage: 4097kB |
| -> Partition Selector (selector id: $0) (actual rows=3 loops=1) |
| -> Seq Scan on t (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (15 rows) |
| |
| -- Plan-time pruning based on the 'id' partitioning column, and |
| -- run-time join pruning based on the expression |
| explain (analyze, costs off, timing off, summary off) |
| select * from pt, t where pt.id = 4 and t.id = 4 and (t.b % 2) = (pt.b % 2); |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) (actual rows=1 loops=1) |
| -> Hash Join (actual rows=1 loops=1) |
| Hash Cond: ((pt.b % 2) = (t.b % 2)) |
| Extra Text: Hash chain length 1.0 avg, 1 max, using 1 of 524288 buckets. |
| -> Append (actual rows=1 loops=1) |
| Partition Selectors: $0 |
| -> Seq Scan on ptx_even pt_1 (actual rows=1 loops=1) |
| Filter: (id = 4) |
| -> Seq Scan on ptx_odd pt_2 (never executed) |
| Filter: (id = 4) |
| -> Hash (actual rows=1 loops=1) |
| Buckets: 524288 Batches: 1 Memory Usage: 4097kB |
| -> Partition Selector (selector id: $0) (actual rows=1 loops=1) |
| -> Seq Scan on t (actual rows=1 loops=1) |
| Filter: (id = 4) |
| Rows Removed by Filter: 2 |
| Optimizer: Postgres query optimizer |
| (16 rows) |
| |
| -- Mixed case |
| insert into pt values (4, 5); |
| explain (analyze, costs off, timing off, summary off) |
| select * from pt, t where t.id = pt.id and (t.b % 2) = (pt.b % 2); |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=4 loops=1) |
| -> Hash Join (actual rows=2 loops=1) |
| Hash Cond: ((pt.id = t.id) AND ((pt.b % 2) = (t.b % 2))) |
| Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 3 of 524288 buckets. |
| -> Append (actual rows=2 loops=1) |
| Partition Selectors: $0 |
| -> Seq Scan on pt1 pt_1 (actual rows=1 loops=1) |
| -> Seq Scan on pt2 pt_2 (actual rows=1 loops=1) |
| -> Seq Scan on pt3 pt_3 (never executed) |
| -> Seq Scan on ptx_even pt_4 (actual rows=1 loops=1) |
| -> Seq Scan on ptx_odd pt_5 (never executed) |
| -> Hash (actual rows=3 loops=1) |
| Buckets: 524288 Batches: 1 Memory Usage: 4097kB |
| -> Partition Selector (selector id: $0) (actual rows=3 loops=1) |
| -> Seq Scan on t (actual rows=3 loops=1) |
| Optimizer: Postgres query optimizer |
| (15 rows) |
| |
| -- |
| -- Join pruning on an inequality qual |
| -- |
| drop table if exists t; |
| drop table if exists pt; |
| create table t(dist int, tid int) distributed by (dist); |
| create table pt(dist int, ptid int) distributed by (dist) partition by range (ptid); |
| create table pt1 partition of pt for values from (1) to (2); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt2 partition of pt for values from (2) to (3); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt3 partition of pt for values from (3) to (4); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt4 partition of pt for values from (4) to (5); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt5 partition of pt for values from (5) to (6); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table ptdefault partition of pt default; |
| NOTICE: table has parent, setting distribution columns to match parent table |
| insert into t values (0, 4); |
| insert into t values (0, 3); |
| insert into pt select 0, i from generate_series(1,9) i; |
| analyze t; |
| analyze pt; |
| explain (analyze, costs off, timing off, summary off) |
| select * from pt, t where t.dist = pt.dist and t.tid < pt.ptid; |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (actual rows=11 loops=1) |
| -> Hash Join (actual rows=11 loops=1) |
| Hash Cond: (pt.dist = t.dist) |
| Join Filter: (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 (out of 6) |
| Partitions scanned: 4 (seg1). |
| -> Hash (actual rows=2 loops=1) |
| Buckets: 524288 Batches: 1 Memory Usage: 4097kB |
| -> Partition Selector (selector id: $0) (actual rows=2 loops=1) |
| -> Seq Scan on t (actual rows=2 loops=1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (13 rows) |
| |
| -- |
| -- Test join pruning with a MergeAppend |
| -- |
| drop table if exists t; |
| drop table if exists pt; |
| create table t(dist int, tid int, sk int) distributed by (dist); |
| create table pt(dist int, ptid int, sk int) distributed by (dist) partition by range (ptid); |
| create table pt1 partition of pt for values from (1) to (2); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt2 partition of pt for values from (2) to (3); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt3 partition of pt for values from (3) to (4); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt4 partition of pt for values from (4) to (5); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table pt5 partition of pt for values from (5) to (6); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table ptdefault partition of pt default; |
| NOTICE: table has parent, setting distribution columns to match parent table |
| insert into t values (1, 1, 1); |
| insert into t values (2, 2, 2); |
| insert into t select i, i, i from generate_series(5,100) i; |
| insert into pt select i, i, i from generate_series(1,7) i; |
| insert into pt select i, i, i from generate_series(1000, 1100) i; |
| analyze t; |
| analyze pt; |
| create index on pt (ptid, sk); |
| set enable_mergejoin=on; |
| set enable_seqscan=off; |
| -- force_explain |
| explain (analyze, timing off, summary off) |
| select * from pt, t where t.dist = pt.dist and t.tid = pt.ptid order by t.tid, t.sk; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.10 rows=98 width=24) (actual rows=5 loops=1) |
| Merge Key: t.tid, t.sk |
| -> Sort (cost=0.00..862.09 rows=33 width=24) (actual rows=2 loops=1) |
| Sort Key: t.tid, t.sk |
| Sort Method: quicksort Memory: 75kB |
| -> Hash Join (cost=0.00..862.05 rows=33 width=24) (actual rows=2 loops=1) |
| 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 (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 |
| -> Partition Selector (selector id: $0) (cost=0.00..431.00 rows=33 width=12) (actual rows=37 loops=1) |
| -> Seq Scan on t (cost=0.00..431.00 rows=33 width=12) (actual rows=37 loops=1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (16 rows) |
| |
