| -- start_matchsubs |
| -- m/\(actual time=\d+\.\d+..\d+\.\d+ rows=\d+ loops=\d+\)/ |
| -- s/\(actual time=\d+\.\d+..\d+\.\d+ rows=\d+ loops=\d+\)/(actual time=##.###..##.### rows=# loops=#)/ |
| -- m/\(slice\d+\) Executor memory: (\d+)\w bytes\./ |
| -- s/Executor memory: (\d+)\w bytes\./Executor memory: (#####)K bytes./ |
| -- m/\(slice\d+\) Executor memory: (\d+)\w bytes avg x \d+(x\(\d+\))* workers, \d+\w bytes max \(seg\d+\)\./ |
| -- s/Executor memory: (\d+)\w bytes avg x \d+(x\(\d+\))* workers, \d+\w bytes max \(seg\d+\)\./Executor memory: ####K bytes avg x #### workers, ####K bytes max (seg#)./ |
| -- m/Work_mem: \d+\w bytes max\./ |
| -- s/Work_mem: \d+\w bytes max\. */Work_mem: ###K bytes max./ |
| -- m/Execution Time: \d+\.\d+ ms/ |
| -- s/Execution Time: \d+\.\d+ ms/Execution Time: ##.### ms/ |
| -- m/Planning Time: \d+\.\d+ ms/ |
| -- s/Planning Time: \d+\.\d+ ms/Planning Time: ##.### ms/ |
| -- m/cost=\d+\.\d+\.\.\d+\.\d+ rows=\d+ width=\d+/ |
| -- s/\(cost=\d+\.\d+\.\.\d+\.\d+ rows=\d+ width=\d+\)/(cost=##.###..##.### rows=### width=###)/ |
| -- m/Memory used: \d+\w?B/ |
| -- s/Memory used: \d+\w?B/Memory used: ###B/ |
| -- m/Memory Usage: \d+\w?B/ |
| -- s/Memory Usage: \d+\w?B/Memory Usage: ###B/ |
| -- m/Peak Memory Usage: \d+/ |
| -- s/Peak Memory Usage: \d+/Peak Memory Usage: ###/ |
| -- m/Buckets: \d+/ |
| -- s/Buckets: \d+/Buckets: ###/ |
| -- m/Batches: \d+/ |
| -- s/Batches: \d+/Batches: ###/ |
| -- end_matchsubs |
| -- |
| -- DEFAULT syntax |
| CREATE TABLE apples(id int PRIMARY KEY, type text); |
| INSERT INTO apples(id) SELECT generate_series(1, 100000); |
| CREATE TABLE box_locations(id int PRIMARY KEY, address text); |
| CREATE TABLE boxes(id int PRIMARY KEY, apple_id int REFERENCES apples(id), location_id int REFERENCES box_locations(id)); |
| WARNING: referential integrity (FOREIGN KEY) constraints are not supported in Apache Cloudberry, will not be enforced |
| WARNING: referential integrity (FOREIGN KEY) constraints are not supported in Apache Cloudberry, will not be enforced |
| --- Check Explain Text format output |
| -- explain_processing_off |
| EXPLAIN SELECT * from boxes LEFT JOIN apples ON apples.id = boxes.apple_id LEFT JOIN box_locations ON box_locations.id = boxes.location_id; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..449.00 rows=3 width=36) |
| -> Nested Loop Left Join (cost=0.00..449.00 rows=1 width=36) |
| Join Filter: true |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..437.00 rows=1 width=24) |
| Hash Key: boxes.apple_id |
| -> Nested Loop Left Join (cost=0.00..437.00 rows=1 width=24) |
| Join Filter: true |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=12) |
| Hash Key: boxes.location_id |
| -> Seq Scan on boxes (cost=0.00..431.00 rows=1 width=12) |
| -> Index Scan using box_locations_pkey on box_locations (cost=0.00..6.00 rows=1 width=12) |
| Index Cond: (id = boxes.location_id) |
| -> Index Scan using apples_pkey on apples (cost=0.00..12.00 rows=1 width=12) |
| Index Cond: (id = boxes.apple_id) |
| Optimizer: GPORCA |
| (15 rows) |
| |
| -- explain_processing_on |
| --- Check Explain Analyze Text output that include the slices information |
| -- explain_processing_off |
| EXPLAIN (ANALYZE) SELECT * from boxes LEFT JOIN apples ON apples.id = boxes.apple_id LEFT JOIN box_locations ON box_locations.id = boxes.location_id; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..449.00 rows=3 width=36) (actual time=10.979..10.983 rows=0 loops=1) |
| -> Nested Loop Left Join (cost=0.00..449.00 rows=1 width=36) (actual time=10.110..10.113 rows=0 loops=1) |
| Join Filter: true |
| -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..437.00 rows=1 width=24) (actual time=10.102..10.102 rows=0 loops=1) |
| Hash Key: boxes.apple_id |
| -> Nested Loop Left Join (cost=0.00..437.00 rows=1 width=24) (actual time=8.715..8.719 rows=0 loops=1) |
| Join Filter: true |
| -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=12) (actual time=8.699..8.699 rows=0 loops=1) |
| Hash Key: boxes.location_id |
| -> Seq Scan on boxes (cost=0.00..431.00 rows=1 width=12) (actual time=0.015..0.018 rows=0 loops=1) |
| -> Index Scan using box_locations_pkey on box_locations (cost=0.00..6.00 rows=1 width=12) (never executed) |
| Index Cond: (id = boxes.location_id) |
| -> Index Scan using apples_pkey on apples (cost=0.00..12.00 rows=1 width=12) (never executed) |
| Index Cond: (id = boxes.apple_id) |
| Optimizer: GPORCA |
| Planning Time: 40.380 ms |
| (slice0) Executor memory: 56K bytes. |
| (slice1) Executor memory: 43K bytes avg x 3 workers, 43K bytes max (seg0). |
| (slice2) Executor memory: 42K bytes avg x 3 workers, 42K bytes max (seg0). |
| (slice3) Executor memory: 39K bytes avg x 3 workers, 39K bytes max (seg0). |
| Memory used: 256000kB |
| Optimizer: Pivotal Optimizer (GPORCA) |
| Execution Time: 29.557 ms |
| (22 rows) |
| |
| -- explain_processing_on |
| -- Unaligned output format is better for the YAML / XML / JSON outputs. |
| -- In aligned format, you have end-of-line markers at the end of each line, |
| -- and its position depends on the longest line. If the width changes, all |
| -- lines need to be adjusted for the moved end-of-line-marker. |
| \a |
| -- YAML Required replaces for costs and time changes |
| -- start_matchsubs |
| -- m/ Loops: \d+/ |
| -- s/ Loops: \d+/ Loops: #/ |
| -- m/ Cost: \d+\.\d+/ |
| -- s/ Cost: \d+\.\d+/ Cost: ###.##/ |
| -- m/ Rows: \d+/ |
| -- s/ Rows: \d+/ Rows: #####/ |
| -- m/ Plan Width: \d+/ |
| -- s/ Plan Width: \d+/ Plan Width: ##/ |
| -- m/ Time: \d+\.\d+/ |
| -- s/ Time: \d+\.\d+/ Time: ##.###/ |
| -- m/Execution Time: \d+\.\d+/ |
| -- s/Execution Time: \d+\.\d+/Execution Time: ##.###/ |
| -- m/Segments: \d+/ |
| -- s/Segments: \d+/Segments: #/ |
| -- m/Pivotal Optimizer \(GPORCA\) version \d+\.\d+\.\d+",?/ |
| -- s/Pivotal Optimizer \(GPORCA\) version \d+\.\d+\.\d+",?/Pivotal Optimizer \(GPORCA\)"/ |
| -- m/ Memory: \d+/ |
| -- s/ Memory: \d+/ Memory: ###/ |
| -- m/Maximum Memory Used: \d+/ |
| -- s/Maximum Memory Used: \d+/Maximum Memory Used: ###/ |
| -- m/Workers: \d+/ |
| -- s/Workers: \d+/Workers: ##/ |
| -- m/Subworkers: \d+/ |
| -- s/Subworkers: \d+/Subworkers: ##/ |
| -- m/Average: \d+/ |
| -- s/Average: \d+/Average: ##/ |
| -- m/Total memory used across slices: \d+/ |
| -- s/Total memory used across slices: \d+\s*/Total memory used across slices: ###/ |
| -- m/Memory used: \d+/ |
| -- s/Memory used: \d+/Memory used: ###/ |
| -- end_matchsubs |
| -- Check Explain YAML output |
| EXPLAIN (FORMAT YAML) SELECT * from boxes LEFT JOIN apples ON apples.id = boxes.apple_id LEFT JOIN box_locations ON box_locations.id = boxes.location_id; |
| QUERY PLAN |
| - Plan: |
| Node Type: "Gather Motion" |
| Senders: 3 |
| Receivers: 1 |
| Slice: 1 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Startup Cost: 0.00 |
| Total Cost: 449.00 |
| Plan Rows: 3 |
| Plan Width: 36 |
| Plans: |
| - Node Type: "Nested Loop" |
| Parent Relationship: "Outer" |
| Slice: 1 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Join Type: "Left" |
| Startup Cost: 0.00 |
| Total Cost: 449.00 |
| Plan Rows: 1 |
| Plan Width: 36 |
| Inner Unique: false |
| Join Filter: "true" |
| Plans: |
| - Node Type: "Redistribute Motion" |
| Senders: 3 |
| Receivers: 3 |
| Parent Relationship: "Outer" |
| Slice: 2 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Startup Cost: 0.00 |
| Total Cost: 437.00 |
| Plan Rows: 1 |
| Plan Width: 24 |
| Hash Key: "boxes.apple_id" |
| Plans: |
| - Node Type: "Nested Loop" |
| Parent Relationship: "Outer" |
| Slice: 2 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Join Type: "Left" |
| Startup Cost: 0.00 |
| Total Cost: 437.00 |
| Plan Rows: 1 |
| Plan Width: 24 |
| Inner Unique: false |
| Join Filter: "true" |
| Plans: |
| - Node Type: "Redistribute Motion" |
| Senders: 3 |
| Receivers: 3 |
| Parent Relationship: "Outer" |
| Slice: 3 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Startup Cost: 0.00 |
| Total Cost: 431.00 |
| Plan Rows: 1 |
| Plan Width: 12 |
| Hash Key: "boxes.location_id" |
| Plans: |
| - Node Type: "Seq Scan" |
| Parent Relationship: "Outer" |
| Slice: 3 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Relation Name: "boxes" |
| Alias: "boxes" |
| Startup Cost: 0.00 |
| Total Cost: 431.00 |
| Plan Rows: 1 |
| Plan Width: 12 |
| - Node Type: "Index Scan" |
| Parent Relationship: "Inner" |
| Slice: 2 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Scan Direction: "Forward" |
| Index Name: "box_locations_pkey" |
| Relation Name: "box_locations" |
| Alias: "box_locations" |
| Startup Cost: 0.00 |
| Total Cost: 6.00 |
| Plan Rows: 1 |
| Plan Width: 12 |
| Index Cond: "(id = boxes.location_id)" |
| - Node Type: "Index Scan" |
| Parent Relationship: "Inner" |
| Slice: 1 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Scan Direction: "Forward" |
| Index Name: "apples_pkey" |
| Relation Name: "apples" |
| Alias: "apples" |
| Startup Cost: 0.00 |
| Total Cost: 12.00 |
| Plan Rows: 1 |
| Plan Width: 12 |
| Index Cond: "(id = boxes.apple_id)" |
| Settings: |
| Optimizer: "GPORCA" |
| (1 row) |
| --- Check Explain Analyze YAML output that include the slices information |
| -- explain_processing_off |
| EXPLAIN (ANALYZE, FORMAT YAML) SELECT * from boxes LEFT JOIN apples ON apples.id = boxes.apple_id LEFT JOIN box_locations ON box_locations.id = boxes.location_id; |
| QUERY PLAN |
| - Plan: |
| Node Type: "Gather Motion" |
| Senders: 3 |
| Receivers: 1 |
| Slice: 1 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Startup Cost: 0.00 |
| Total Cost: 449.00 |
| Plan Rows: 3 |
| Plan Width: 36 |
| Actual Startup Time: 15.617 |
| Actual Total Time: 15.617 |
| Actual Rows: 0 |
| Actual Loops: 1 |
| Plans: |
| - Node Type: "Nested Loop" |
| Parent Relationship: "Outer" |
| Slice: 1 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Join Type: "Left" |
| Startup Cost: 0.00 |
| Total Cost: 449.00 |
| Plan Rows: 1 |
| Plan Width: 36 |
| Actual Startup Time: 0.000 |
| Actual Total Time: 0.000 |
| Actual Rows: 0 |
| Actual Loops: 0 |
| Inner Unique: false |
| Join Filter: "true" |
| Rows Removed by Join Filter: 0 |
| Plans: |
| - Node Type: "Redistribute Motion" |
| Senders: 3 |
| Receivers: 3 |
| Parent Relationship: "Outer" |
| Slice: 2 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Startup Cost: 0.00 |
| Total Cost: 437.00 |
| Plan Rows: 1 |
| Plan Width: 24 |
| Actual Startup Time: 0.000 |
| Actual Total Time: 0.000 |
| Actual Rows: 0 |
| Actual Loops: 0 |
| Hash Key: "boxes.apple_id" |
| Plans: |
| - Node Type: "Nested Loop" |
| Parent Relationship: "Outer" |
| Slice: 2 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Join Type: "Left" |
| Startup Cost: 0.00 |
| Total Cost: 437.00 |
| Plan Rows: 1 |
| Plan Width: 24 |
| Actual Startup Time: 0.000 |
| Actual Total Time: 0.000 |
| Actual Rows: 0 |
| Actual Loops: 0 |
| Inner Unique: false |
| Join Filter: "true" |
| Rows Removed by Join Filter: 0 |
| Plans: |
| - Node Type: "Redistribute Motion" |
| Senders: 3 |
| Receivers: 3 |
| Parent Relationship: "Outer" |
| Slice: 3 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Startup Cost: 0.00 |
| Total Cost: 431.00 |
| Plan Rows: 1 |
| Plan Width: 12 |
| Actual Startup Time: 0.000 |
| Actual Total Time: 0.000 |
| Actual Rows: 0 |
| Actual Loops: 0 |
| Hash Key: "boxes.location_id" |
| Plans: |
| - Node Type: "Seq Scan" |
| Parent Relationship: "Outer" |
| Slice: 3 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Relation Name: "boxes" |
| Alias: "boxes" |
| Startup Cost: 0.00 |
| Total Cost: 431.00 |
| Plan Rows: 1 |
| Plan Width: 12 |
| Actual Startup Time: 0.000 |
| Actual Total Time: 0.000 |
| Actual Rows: 0 |
| Actual Loops: 0 |
| - Node Type: "Index Scan" |
| Parent Relationship: "Inner" |
| Slice: 2 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Scan Direction: "Forward" |
| Index Name: "box_locations_pkey" |
| Relation Name: "box_locations" |
| Alias: "box_locations" |
| Startup Cost: 0.00 |
| Total Cost: 6.00 |
| Plan Rows: 1 |
| Plan Width: 12 |
| Actual Startup Time: 0.000 |
| Actual Total Time: 0.000 |
| Actual Rows: 0 |
| Actual Loops: 0 |
| Index Cond: "(id = boxes.location_id)" |
| Rows Removed by Index Recheck: 0 |
| - Node Type: "Index Scan" |
| Parent Relationship: "Inner" |
| Slice: 1 |
| Segments: 3 |
| Gang Type: "primary reader" |
| Parallel Aware: false |
| Async Capable: false |
| Scan Direction: "Forward" |
| Index Name: "apples_pkey" |
| Relation Name: "apples" |
| Alias: "apples" |
| Startup Cost: 0.00 |
| Total Cost: 12.00 |
| Plan Rows: 1 |
| Plan Width: 12 |
| Actual Startup Time: 0.000 |
| Actual Total Time: 0.000 |
| Actual Rows: 0 |
| Actual Loops: 0 |
| Index Cond: "(id = boxes.apple_id)" |
| Rows Removed by Index Recheck: 0 |
| Settings: |
| Optimizer: "GPORCA" |
| Planning Time: 14.827 |
| Triggers: |
| Slice statistics: |
| - Slice: 0 |
| Executor Memory: 195920 |
| - Slice: 1 |
| Executor Memory: |
| Average: 97488 |
| Workers: 3 |
| Subworkers: 0 |
| Maximum Memory Used: 97488 |
| - Slice: 2 |
| Executor Memory: |
| Average: 97488 |
| Workers: 3 |
| Subworkers: 0 |
| Maximum Memory Used: 97488 |
| - Slice: 3 |
| Executor Memory: |
| Average: 60624 |
| Workers: 3 |
| Subworkers: 0 |
| Maximum Memory Used: 60624 |
| Statement statistics: |
| Memory used: 128000 |
| Execution Time: 3.332 |
| (1 row) |
| -- explain_processing_on |
| -- |
| -- Test a simple case with JSON and XML output, too. |
| -- |
| -- This should be enough for those format. The only difference between JSON, |
| -- XML, and YAML is in the formatting, after all. |
| -- Check JSON format |
| -- |
| -- start_matchsubs |
| -- m/Pivotal Optimizer \(GPORCA\) version \d+\.\d+\.\d+/ |
| -- s/Pivotal Optimizer \(GPORCA\) version \d+\.\d+\.\d+/Pivotal Optimizer \(GPORCA\)/ |
| -- end_matchsubs |
| -- explain_processing_off |
| EXPLAIN (FORMAT JSON, COSTS OFF) SELECT * FROM generate_series(1, 10); |
| QUERY PLAN |
| [ |
| { |
| "Plan": { |
| "Node Type": "Function Scan", |
| "Slice": 0, |
| "Segments": 0, |
| "Gang Type": "unallocated", |
| "Parallel Aware": false, |
| "Async Capable": false, |
| "Function Name": "generate_series", |
| "Alias": "generate_series" |
| }, |
| "Settings": { |
| "Optimizer": "GPORCA" |
| } |
| } |
| ] |
| (1 row) |
| EXPLAIN (FORMAT XML, COSTS OFF) SELECT * FROM generate_series(1, 10); |
| QUERY PLAN |
| <explain xmlns="http://www.postgresql.org/2009/explain"> |
| <Query> |
| <Plan> |
| <Node-Type>Function Scan</Node-Type> |
| <Slice>0</Slice> |
| <Segments>0</Segments> |
| <Gang-Type>unallocated</Gang-Type> |
| <Parallel-Aware>false</Parallel-Aware> |
| <Async-Capable>false</Async-Capable> |
| <Function-Name>generate_series</Function-Name> |
| <Alias>generate_series</Alias> |
| </Plan> |
| <Settings> |
| <Optimizer>GPORCA</Optimizer> |
| </Settings> |
| </Query> |
| </explain> |
| (1 row) |
| -- Test for an old bug in printing Sequence nodes in JSON/XML format |
| -- (https://github.com/greenplum-db/gpdb/issues/9410) |
| CREATE TABLE jsonexplaintest (i int4) PARTITION BY RANGE (i) (START(1) END(3) EVERY(1)); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' 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. |
| EXPLAIN (FORMAT JSON, COSTS OFF) SELECT * FROM jsonexplaintest WHERE i = 2; |
| QUERY PLAN |
| [ |
| { |
| "Plan": { |
| "Node Type": "Gather Motion", |
| "Senders": 1, |
| "Receivers": 1, |
| "Slice": 1, |
| "Segments": 1, |
| "Gang Type": "primary reader", |
| "Parallel Aware": false, |
| "Async Capable": false, |
| "Plans": [ |
| { |
| "Node Type": "Dynamic Seq Scan", |
| "Parent Relationship": "Outer", |
| "Slice": 1, |
| "Segments": 1, |
| "Gang Type": "primary reader", |
| "Parallel Aware": false, |
| "Async Capable": false, |
| "Relation Name": "jsonexplaintest", |
| "Alias": "jsonexplaintest", |
| "Number of partitions to scan": 1, |
| "Filter": "(i = 2)" |
| } |
| ] |
| }, |
| "Settings": { |
| "Optimizer": "GPORCA" |
| } |
| } |
| ] |
| (1 row) |
| -- explain_processing_on |
| -- Cleanup |
| DROP TABLE boxes; |
| DROP TABLE apples; |
| DROP TABLE box_locations; |
| DROP TABLE jsonexplaintest; |
