| -- |
| -- EXPLAIN |
| -- |
| -- There are many test cases elsewhere that use EXPLAIN as a vehicle for |
| -- checking something else (usually planner behavior). This file is |
| -- concerned with testing EXPLAIN in its own right. |
| -- |
| -- To produce stable regression test output, it's usually necessary to |
| -- ignore details such as exact costs or row counts. These filter |
| -- functions replace changeable output details with fixed strings. |
| create function explain_filter(text) returns setof text |
| language plpgsql as |
| $$ |
| declare |
| ln text; |
| begin |
| set local enable_parallel = off; |
| for ln in execute $1 |
| loop |
| -- Replace any numeric word with just 'N' |
| ln := regexp_replace(ln, '-?\m\d+\M', 'N', 'g'); |
| -- In sort output, the above won't match units-suffixed numbers |
| ln := regexp_replace(ln, '\m\d+kB', 'NkB', 'g'); |
| -- Ignore text-mode buffers output because it varies depending |
| -- on the system state |
| CONTINUE WHEN (ln ~ ' +Buffers: .*'); |
| -- Ignore text-mode "Planning:" line because whether it's output |
| -- varies depending on the system state |
| CONTINUE WHEN (ln = 'Planning:'); |
| return next ln; |
| end loop; |
| reset enable_parallel; |
| end; |
| $$; |
| -- To produce valid JSON output, replace numbers with "0" or "0.0" not "N" |
| create function explain_filter_to_json(text) returns jsonb |
| language plpgsql as |
| $$ |
| declare |
| data text := ''; |
| ln text; |
| begin |
| set local enable_parallel = off; |
| for ln in execute $1 |
| loop |
| -- Replace any numeric word with just '0' |
| ln := regexp_replace(ln, '\m\d+\M', '0', 'g'); |
| data := data || ln; |
| end loop; |
| return data::jsonb; |
| reset enable_parallel; |
| end; |
| $$; |
| -- Disable JIT, or we'll get different output on machines where that's been |
| -- forced on |
| set jit = off; |
| -- Similarly, disable track_io_timing, to avoid output differences when |
| -- enabled. |
| set track_io_timing = off; |
| -- Simple cases |
| select explain_filter('explain select * from int8_tbl i8'); |
| explain_filter |
| -------------------------------------------------------------------------- |
| Gather Motion N:N (slice1; segments: N) (cost=N.N..N.N rows=N width=N) |
| -> Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| |
| select explain_filter('explain (analyze) select * from int8_tbl i8'); |
| explain_filter |
| ---------------------------------------------------------------------------------------------------------------- |
| Gather Motion N:N (slice1; segments: N) (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) |
| -> Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) |
| Planning Time: N.N ms |
| (slice0) Executor memory: 111K bytes. |
| (slice1) Executor memory: 111K bytes avg x N workers, 111K bytes max (seg0). |
| Memory used: NkB |
| Optimizer: Postgres query optimizer |
| Execution Time: N.N ms |
| (8 rows) |
| |
| select explain_filter('explain (analyze, verbose) select * from int8_tbl i8'); |
| explain_filter |
| ---------------------------------------------------------------------------------------------------------------- |
| Gather Motion N:N (slice1; segments: N) (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) |
| Output: q1, q2 |
| -> Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) |
| Output: q1, q2 |
| Planning Time: N.N ms |
| (slice0) Executor memory: 111K bytes. |
| (slice1) Executor memory: 110K bytes avg x N workers, 110K bytes max (seg0). |
| Memory used: NkB |
| Optimizer: Postgres query optimizer |
| Execution Time: N.N ms |
| (10 rows) |
| |
| select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8'); |
| explain_filter |
| ---------------------------------------------------------------------------------------------------------------- |
| Gather Motion N:N (slice1; segments: N) (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) |
| -> Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N) |
| Planning Time: N.N ms |
| (slice0) Executor memory: 111K bytes. |
| (slice1) Executor memory: 110K bytes avg x N workers, 110K bytes max (seg0). |
| Memory used: NkB |
| Optimizer: Postgres query optimizer |
| Execution Time: N.N ms |
| (8 rows) |
| |
| select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8'); |
| explain_filter |
| ------------------------------------------------------------ |
| <explain xmlns="http://www.postgresql.org/N/explain"> + |
| <Query> + |
| <Plan> + |
| <Node-Type>Gather Motion</Node-Type> + |
| <Senders>N</Senders> + |
| <Receivers>N</Receivers> + |
| <Slice>N</Slice> + |
| <Segments>N</Segments> + |
| <Gang-Type>primary reader</Gang-Type> + |
| <Parallel-Aware>false</Parallel-Aware> + |
| <Startup-Cost>N.N</Startup-Cost> + |
| <Total-Cost>N.N</Total-Cost> + |
| <Plan-Rows>N</Plan-Rows> + |
| <Plan-Width>N</Plan-Width> + |
| <Actual-Startup-Time>N.N</Actual-Startup-Time> + |
| <Actual-Total-Time>N.N</Actual-Total-Time> + |
| <Actual-Rows>N</Actual-Rows> + |
| <Actual-Loops>N</Actual-Loops> + |
| <Shared-Hit-Blocks>N</Shared-Hit-Blocks> + |
| <Shared-Read-Blocks>N</Shared-Read-Blocks> + |
| <Shared-Dirtied-Blocks>N</Shared-Dirtied-Blocks> + |
| <Shared-Written-Blocks>N</Shared-Written-Blocks> + |
| <Local-Hit-Blocks>N</Local-Hit-Blocks> + |
| <Local-Read-Blocks>N</Local-Read-Blocks> + |
| <Local-Dirtied-Blocks>N</Local-Dirtied-Blocks> + |
| <Local-Written-Blocks>N</Local-Written-Blocks> + |
| <Temp-Read-Blocks>N</Temp-Read-Blocks> + |
| <Temp-Written-Blocks>N</Temp-Written-Blocks> + |
| <Plans> + |
| <Plan> + |
| <Node-Type>Seq Scan</Node-Type> + |
| <Parent-Relationship>Outer</Parent-Relationship>+ |
| <Slice>N</Slice> + |
| <Segments>N</Segments> + |
| <Gang-Type>primary reader</Gang-Type> + |
| <Parallel-Aware>false</Parallel-Aware> + |
| <Relation-Name>int8_tbl</Relation-Name> + |
| <Alias>i8</Alias> + |
| <Startup-Cost>N.N</Startup-Cost> + |
| <Total-Cost>N.N</Total-Cost> + |
| <Plan-Rows>N</Plan-Rows> + |
| <Plan-Width>N</Plan-Width> + |
| <Actual-Startup-Time>N.N</Actual-Startup-Time> + |
| <Actual-Total-Time>N.N</Actual-Total-Time> + |
| <Actual-Rows>N</Actual-Rows> + |
| <Actual-Loops>N</Actual-Loops> + |
| <Shared-Hit-Blocks>N</Shared-Hit-Blocks> + |
| <Shared-Read-Blocks>N</Shared-Read-Blocks> + |
| <Shared-Dirtied-Blocks>N</Shared-Dirtied-Blocks>+ |
| <Shared-Written-Blocks>N</Shared-Written-Blocks>+ |
| <Local-Hit-Blocks>N</Local-Hit-Blocks> + |
| <Local-Read-Blocks>N</Local-Read-Blocks> + |
| <Local-Dirtied-Blocks>N</Local-Dirtied-Blocks> + |
| <Local-Written-Blocks>N</Local-Written-Blocks> + |
| <Temp-Read-Blocks>N</Temp-Read-Blocks> + |
| <Temp-Written-Blocks>N</Temp-Written-Blocks> + |
| </Plan> + |
| </Plans> + |
| </Plan> + |
| <Planning> + |
| <Planning-Time>N.N</Planning-Time> + |
| <Shared-Hit-Blocks>N</Shared-Hit-Blocks> + |
| <Shared-Read-Blocks>N</Shared-Read-Blocks> + |
| <Shared-Dirtied-Blocks>N</Shared-Dirtied-Blocks> + |
| <Shared-Written-Blocks>N</Shared-Written-Blocks> + |
| <Local-Hit-Blocks>N</Local-Hit-Blocks> + |
| <Local-Read-Blocks>N</Local-Read-Blocks> + |
| <Local-Dirtied-Blocks>N</Local-Dirtied-Blocks> + |
| <Local-Written-Blocks>N</Local-Written-Blocks> + |
| <Temp-Read-Blocks>N</Temp-Read-Blocks> + |
| <Temp-Written-Blocks>N</Temp-Written-Blocks> + |
| </Planning> + |
| <Triggers> + |
| </Triggers> + |
| <Slice-statistics> + |
| <Slice> + |
| <Slice>N</Slice> + |
| <Executor-Memory>N</Executor-Memory> + |
| </Slice> + |
| <Slice> + |
| <Slice>N</Slice> + |
| <Executor-Memory> + |
| <Average>N</Average> + |
| <Workers>N</Workers> + |
| <Maximum-Memory-Used>N</Maximum-Memory-Used> + |
| </Executor-Memory> + |
| </Slice> + |
| </Slice-statistics> + |
| <Statement-statistics> + |
| <Memory-used>N</Memory-used> + |
| </Statement-statistics> + |
| <Settings> + |
| <Optimizer>Postgres query optimizer</Optimizer> + |
| </Settings> + |
| <Execution-Time>N.N</Execution-Time> + |
| </Query> + |
| </explain> |
| (1 row) |
| |
| select explain_filter('explain (analyze, buffers, format yaml) select * from int8_tbl i8'); |
| explain_filter |
| ------------------------------------------- |
| - Plan: + |
| Node Type: "Gather Motion" + |
| Senders: N + |
| Receivers: N + |
| Slice: N + |
| Segments: N + |
| Gang Type: "primary reader" + |
| Parallel Aware: false + |
| Startup Cost: N.N + |
| Total Cost: N.N + |
| Plan Rows: N + |
| Plan Width: N + |
| Actual Startup Time: N.N + |
| Actual Total Time: N.N + |
| Actual Rows: N + |
| Actual Loops: N + |
| Shared Hit Blocks: N + |
| Shared Read Blocks: N + |
| Shared Dirtied Blocks: N + |
| Shared Written Blocks: N + |
| Local Hit Blocks: N + |
| Local Read Blocks: N + |
| Local Dirtied Blocks: N + |
| Local Written Blocks: N + |
| Temp Read Blocks: N + |
| Temp Written Blocks: N + |
| Plans: + |
| - Node Type: "Seq Scan" + |
| Parent Relationship: "Outer" + |
| Slice: N + |
| Segments: N + |
| Gang Type: "primary reader" + |
| Parallel Aware: false + |
| Relation Name: "int8_tbl" + |
| Alias: "i8" + |
| Startup Cost: N.N + |
| Total Cost: N.N + |
| Plan Rows: N + |
| Plan Width: N + |
| Actual Startup Time: N.N + |
| Actual Total Time: N.N + |
| Actual Rows: N + |
| Actual Loops: N + |
| Shared Hit Blocks: N + |
| Shared Read Blocks: N + |
| Shared Dirtied Blocks: N + |
| Shared Written Blocks: N + |
| Local Hit Blocks: N + |
| Local Read Blocks: N + |
| Local Dirtied Blocks: N + |
| Local Written Blocks: N + |
| Temp Read Blocks: N + |
| Temp Written Blocks: N + |
| Planning: + |
| Planning Time: N.N + |
| Shared Hit Blocks: N + |
| Shared Read Blocks: N + |
| Shared Dirtied Blocks: N + |
| Shared Written Blocks: N + |
| Local Hit Blocks: N + |
| Local Read Blocks: N + |
| Local Dirtied Blocks: N + |
| Local Written Blocks: N + |
| Temp Read Blocks: N + |
| Temp Written Blocks: N + |
| Triggers: + |
| Slice statistics: + |
| - Slice: N + |
| Executor Memory: N + |
| - Slice: N + |
| Executor Memory: + |
| Average: N + |
| Workers: N + |
| Maximum Memory Used: N + |
| Statement statistics: + |
| Memory used: N + |
| Settings: + |
| Optimizer: "Postgres query optimizer"+ |
| Execution Time: N.N |
| (1 row) |
| |
| select explain_filter('explain (buffers, format text) select * from int8_tbl i8'); |
| explain_filter |
| -------------------------------------------------------------------------- |
| Gather Motion N:N (slice1; segments: N) (cost=N.N..N.N rows=N width=N) |
| -> Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) |
| Optimizer: Postgres query optimizer |
| (3 rows) |
| |
| select explain_filter('explain (buffers, format json) select * from int8_tbl i8'); |
| explain_filter |
| ----------------------------------------------- |
| [ + |
| { + |
| "Plan": { + |
| "Node Type": "Gather Motion", + |
| "Senders": N, + |
| "Receivers": N, + |
| "Slice": N, + |
| "Segments": N, + |
| "Gang Type": "primary reader", + |
| "Parallel Aware": false, + |
| "Async Capable": false, + |
| "Startup Cost": N.N, + |
| "Total Cost": N.N, + |
| "Plan Rows": N, + |
| "Plan Width": N, + |
| "Shared Hit Blocks": N, + |
| "Shared Read Blocks": N, + |
| "Shared Dirtied Blocks": N, + |
| "Shared Written Blocks": N, + |
| "Local Hit Blocks": N, + |
| "Local Read Blocks": N, + |
| "Local Dirtied Blocks": N, + |
| "Local Written Blocks": N, + |
| "Temp Read Blocks": N, + |
| "Temp Written Blocks": N, + |
| "Plans": [ + |
| { + |
| "Node Type": "Seq Scan", + |
| "Parent Relationship": "Outer", + |
| "Slice": N, + |
| "Segments": N, + |
| "Gang Type": "primary reader", + |
| "Parallel Aware": false, + |
| "Async Capable": false, + |
| "Relation Name": "int8_tbl", + |
| "Alias": "i8", + |
| "Startup Cost": N.N, + |
| "Total Cost": N.N, + |
| "Plan Rows": N, + |
| "Plan Width": N, + |
| "Shared Hit Blocks": N, + |
| "Shared Read Blocks": N, + |
| "Shared Dirtied Blocks": N, + |
| "Shared Written Blocks": N, + |
| "Local Hit Blocks": N, + |
| "Local Read Blocks": N, + |
| "Local Dirtied Blocks": N, + |
| "Local Written Blocks": N, + |
| "Temp Read Blocks": N, + |
| "Temp Written Blocks": N + |
| } + |
| ] + |
| }, + |
| "Settings": { + |
| "Optimizer": "Postgres query optimizer"+ |
| }, + |
| "Planning": { + |
| "Shared Hit Blocks": N, + |
| "Shared Read Blocks": N, + |
| "Shared Dirtied Blocks": N, + |
| "Shared Written Blocks": N, + |
| "Local Hit Blocks": N, + |
| "Local Read Blocks": N, + |
| "Local Dirtied Blocks": N, + |
| "Local Written Blocks": N, + |
| "Temp Read Blocks": N, + |
| "Temp Written Blocks": N + |
| } + |
| } + |
| ] |
| (1 row) |
| |
| -- Check output including I/O timings. These fields are conditional |
| -- but always set in JSON format, so check them only in this case. |
| set track_io_timing = on; |
| select explain_filter('explain (analyze, buffers, format json) select * from int8_tbl i8'); |
| explain_filter |
| ----------------------------------------------- |
| [ + |
| { + |
| "Plan": { + |
| "Node Type": "Gather Motion", + |
| "Senders": N, + |
| "Receivers": N, + |
| "Slice": N, + |
| "Segments": N, + |
| "Gang Type": "primary reader", + |
| "Parallel Aware": false, + |
| "Async Capable": false, + |
| "Startup Cost": N.N, + |
| "Total Cost": N.N, + |
| "Plan Rows": N, + |
| "Plan Width": N, + |
| "Actual Startup Time": N.N, + |
| "Actual Total Time": N.N, + |
| "Actual Rows": N, + |
| "Actual Loops": N, + |
| "Shared Hit Blocks": N, + |
| "Shared Read Blocks": N, + |
| "Shared Dirtied Blocks": N, + |
| "Shared Written Blocks": N, + |
| "Local Hit Blocks": N, + |
| "Local Read Blocks": N, + |
| "Local Dirtied Blocks": N, + |
| "Local Written Blocks": N, + |
| "Temp Read Blocks": N, + |
| "Temp Written Blocks": N, + |
| "I/O Read Time": N.N, + |
| "I/O Write Time": N.N, + |
| "Temp I/O Read Time": N.N, + |
| "Temp I/O Write Time": N.N, + |
| "Plans": [ + |
| { + |
| "Node Type": "Seq Scan", + |
| "Parent Relationship": "Outer", + |
| "Slice": N, + |
| "Segments": N, + |
| "Gang Type": "primary reader", + |
| "Parallel Aware": false, + |
| "Async Capable": false, + |
| "Relation Name": "int8_tbl", + |
| "Alias": "i8", + |
| "Startup Cost": N.N, + |
| "Total Cost": N.N, + |
| "Plan Rows": N, + |
| "Plan Width": N, + |
| "Actual Startup Time": N.N, + |
| "Actual Total Time": N.N, + |
| "Actual Rows": N, + |
| "Actual Loops": N, + |
| "Shared Hit Blocks": N, + |
| "Shared Read Blocks": N, + |
| "Shared Dirtied Blocks": N, + |
| "Shared Written Blocks": N, + |
| "Local Hit Blocks": N, + |
| "Local Read Blocks": N, + |
| "Local Dirtied Blocks": N, + |
| "Local Written Blocks": N, + |
| "Temp Read Blocks": N, + |
| "Temp Written Blocks": N, + |
| "I/O Read Time": N.N, + |
| "I/O Write Time": N.N, + |
| "Temp I/O Read Time": N.N, + |
| "Temp I/O Write Time": N.N + |
| } + |
| ] + |
| }, + |
| "Settings": { + |
| "Optimizer": "Postgres query optimizer"+ |
| }, + |
| "Planning": { + |
| "Shared Hit Blocks": N, + |
| "Shared Read Blocks": N, + |
| "Shared Dirtied Blocks": N, + |
| "Shared Written Blocks": N, + |
| "Local Hit Blocks": N, + |
| "Local Read Blocks": N, + |
| "Local Dirtied Blocks": N, + |
| "Local Written Blocks": N, + |
| "Temp Read Blocks": N, + |
| "Temp Written Blocks": N, + |
| "I/O Read Time": N.N, + |
| "I/O Write Time": N.N, + |
| "Temp I/O Read Time": N.N, + |
| "Temp I/O Write Time": N.N + |
| }, + |
| "Planning Time": N.N, + |
| "Triggers": [ + |
| ], + |
| "Slice statistics": [ + |
| { + |
| "Slice": N, + |
| "Executor Memory": N + |
| }, + |
| { + |
| "Slice": N, + |
| "Executor Memory": { + |
| "Average": N, + |
| "Workers": N, + |
| "Subworkers": N, + |
| "Maximum Memory Used": N + |
| } + |
| } + |
| ], + |
| "Statement statistics": { + |
| "Memory used": N + |
| }, + |
| "Execution Time": N.N + |
| } + |
| ] |
| (1 row) |
| |
| set track_io_timing = off; |
| -- SETTINGS option |
| -- We have to ignore other settings that might be imposed by the environment, |
| -- so printing the whole Settings field unfortunately won't do. |
| begin; |
| set local plan_cache_mode = force_generic_plan; |
| select true as "OK" |
| from explain_filter('explain (settings) select * from int8_tbl i8') ln |
| where ln ~ '^ *Settings: .*plan_cache_mode = ''force_generic_plan'''; |
| OK |
| ---- |
| t |
| (1 row) |
| |
| select explain_filter_to_json('explain (settings, format json) select * from int8_tbl i8') #> '{0,Settings,plan_cache_mode}'; |
| ?column? |
| ---------------------- |
| "force_generic_plan" |
| (1 row) |
| |
| rollback; |
| -- GENERIC_PLAN option |
| select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1'); |
| explain_filter |
| --------------------------------------------------------------------------------------- |
| Gather Motion N:N (slice1; segments: N) (cost=N.N..N.N rows=N width=N) |
| -> Bitmap Heap Scan on tenk1 (cost=N.N..N.N rows=N width=N) |
| Recheck Cond: (thousand = $N) |
| -> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N width=N) |
| Index Cond: (thousand = $N) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| -- should fail |
| select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); |
| ERROR: EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together |
| CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement |
| -- Test EXPLAIN (GENERIC_PLAN) with partition pruning |
| -- partitions should be pruned at plan time, based on constants, |
| -- but there should be no pruning based on parameter placeholders |
| create table gen_part ( |
| key1 integer not null, |
| key2 integer not null |
| ) partition by list (key1); |
| create table gen_part_1 |
| partition of gen_part for values in (1) |
| partition by range (key2); |
| create table gen_part_1_1 |
| partition of gen_part_1 for values from (1) to (2); |
| create table gen_part_1_2 |
| partition of gen_part_1 for values from (2) to (3); |
| create table gen_part_2 |
| partition of gen_part for values in (2); |
| -- should scan gen_part_1_1 and gen_part_1_2, but not gen_part_2 |
| select explain_filter('explain (generic_plan) select key1, key2 from gen_part where key1 = 1 and key2 = $1'); |
| explain_filter |
| --------------------------------------------------------------------------------- |
| Gather Motion N:N (slice1; segments: N) (cost=N.N..N.N rows=N width=N) |
| -> Append (cost=N.N..N.N rows=N width=N) |
| -> Seq Scan on gen_part_1_1 gen_part_1 (cost=N.N..N.N rows=N width=N) |
| Filter: ((key1 = N) AND (key2 = $N)) |
| -> Seq Scan on gen_part_1_2 gen_part_2 (cost=N.N..N.N rows=N width=N) |
| Filter: ((key1 = N) AND (key2 = $N)) |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| |
| drop table gen_part; |
| -- |
| -- Test production of per-worker data |
| -- |
| -- Unfortunately, because we don't know how many worker processes we'll |
| -- actually get (maybe none at all), we can't examine the "Workers" output |
| -- in any detail. We can check that it parses correctly as JSON, and then |
| -- remove it from the displayed results. |
| begin; |
| -- encourage use of parallel plans |
| set parallel_setup_cost=0; |
| set parallel_tuple_cost=0; |
| set min_parallel_table_scan_size=0; |
| set max_parallel_workers_per_gather=4; |
| select jsonb_pretty( |
| explain_filter_to_json('explain (analyze, verbose, buffers, format json) |
| select * from tenk1 order by tenthous') |
| -- remove "Workers" node of the Seq Scan plan node |
| #- '{0,Plan,Plans,0,Plans,0,Workers}' |
| -- remove "Workers" node of the Sort plan node |
| #- '{0,Plan,Plans,0,Workers}' |
| -- Also remove its sort-type fields, as those aren't 100% stable |
| #- '{0,Plan,Plans,0,Sort Method}' |
| #- '{0,Plan,Plans,0,Sort Space Type}' |
| ); |
| jsonb_pretty |
| ------------------------------------------------------------- |
| [ + |
| { + |
| "Plan": { + |
| "Plans": [ + |
| { + |
| "Plans": [ + |
| { + |
| "Alias": "tenk1", + |
| "Slice": 0, + |
| "Output": [ + |
| "unique1", + |
| "unique2", + |
| "two", + |
| "four", + |
| "ten", + |
| "twenty", + |
| "hundred", + |
| "thousand", + |
| "twothousand", + |
| "fivethous", + |
| "tenthous", + |
| "odd", + |
| "even", + |
| "stringu1", + |
| "stringu2", + |
| "string4" + |
| ], + |
| "Schema": "public", + |
| "Segments": 0, + |
| "Gang Type": "primary reader", + |
| "Node Type": "Seq Scan", + |
| "Plan Rows": 0, + |
| "Plan Width": 0, + |
| "Total Cost": 0.0, + |
| "Actual Rows": 0, + |
| "Actual Loops": 0, + |
| "Startup Cost": 0.0, + |
| "Async Capable": false, + |
| "Relation Name": "tenk1", + |
| "Parallel Aware": false, + |
| "Local Hit Blocks": 0, + |
| "Temp Read Blocks": 0, + |
| "Actual Total Time": 0.0, + |
| "Local Read Blocks": 0, + |
| "Shared Hit Blocks": 0, + |
| "Shared Read Blocks": 0, + |
| "Actual Startup Time": 0.0, + |
| "Parent Relationship": "Outer",+ |
| "Temp Written Blocks": 0, + |
| "Local Dirtied Blocks": 0, + |
| "Local Written Blocks": 0, + |
| "Shared Dirtied Blocks": 0, + |
| "Shared Written Blocks": 0 + |
| } + |
| ], + |
| "Slice": 0, + |
| "Output": [ + |
| "unique1", + |
| "unique2", + |
| "two", + |
| "four", + |
| "ten", + |
| "twenty", + |
| "hundred", + |
| "thousand", + |
| "twothousand", + |
| "fivethous", + |
| "tenthous", + |
| "odd", + |
| "even", + |
| "stringu1", + |
| "stringu2", + |
| "string4" + |
| ], + |
| "Segments": 0, + |
| "Sort Key": [ + |
| "tenk1.tenthous" + |
| ], + |
| "Gang Type": "primary reader", + |
| "Node Type": "Sort", + |
| "Plan Rows": 0, + |
| "Plan Width": 0, + |
| "Total Cost": 0.0, + |
| "Actual Rows": 0, + |
| "Actual Loops": 0, + |
| "Startup Cost": 0.0, + |
| "Async Capable": false, + |
| "Sort Segments": 0, + |
| "Parallel Aware": false, + |
| "Sort Space Used": 0, + |
| "Local Hit Blocks": 0, + |
| "Temp Read Blocks": 0, + |
| "Actual Total Time": 0.0, + |
| "Local Read Blocks": 0, + |
| "Shared Hit Blocks": 0, + |
| "Shared Read Blocks": 0, + |
| "Actual Startup Time": 0.0, + |
| "Parent Relationship": "Outer", + |
| "Temp Written Blocks": 0, + |
| "Local Dirtied Blocks": 0, + |
| "Local Written Blocks": 0, + |
| "Shared Dirtied Blocks": 0, + |
| "Shared Written Blocks": 0, + |
| "Sort Avg Segment Memory": 0, + |
| "Sort Max Segment Memory": 0 + |
| } + |
| ], + |
| "Slice": 0, + |
| "Output": [ + |
| "unique1", + |
| "unique2", + |
| "two", + |
| "four", + |
| "ten", + |
| "twenty", + |
| "hundred", + |
| "thousand", + |
| "twothousand", + |
| "fivethous", + |
| "tenthous", + |
| "odd", + |
| "even", + |
| "stringu1", + |
| "stringu2", + |
| "string4" + |
| ], + |
| "Senders": 0, + |
| "Segments": 0, + |
| "Gang Type": "primary reader", + |
| "Merge Key": [ + |
| "tenthous" + |
| ], + |
| "Node Type": "Gather Motion", + |
| "Plan Rows": 0, + |
| "Receivers": 0, + |
| "Plan Width": 0, + |
| "Total Cost": 0.0, + |
| "Actual Rows": 0, + |
| "Actual Loops": 0, + |
| "Startup Cost": 0.0, + |
| "Async Capable": false, + |
| "Parallel Aware": false, + |
| "Local Hit Blocks": 0, + |
| "Temp Read Blocks": 0, + |
| "Actual Total Time": 0.0, + |
| "Local Read Blocks": 0, + |
| "Shared Hit Blocks": 0, + |
| "Shared Read Blocks": 0, + |
| "Actual Startup Time": 0.0, + |
| "Temp Written Blocks": 0, + |
| "Local Dirtied Blocks": 0, + |
| "Local Written Blocks": 0, + |
| "Shared Dirtied Blocks": 0, + |
| "Shared Written Blocks": 0 + |
| }, + |
| "Planning": { + |
| "Local Hit Blocks": 0, + |
| "Temp Read Blocks": 0, + |
| "Local Read Blocks": 0, + |
| "Shared Hit Blocks": 0, + |
| "Shared Read Blocks": 0, + |
| "Temp Written Blocks": 0, + |
| "Local Dirtied Blocks": 0, + |
| "Local Written Blocks": 0, + |
| "Shared Dirtied Blocks": 0, + |
| "Shared Written Blocks": 0 + |
| }, + |
| "Settings": { + |
| "jit": "off", + |
| "Optimizer": "Postgres query optimizer", + |
| "enable_parallel": "off", + |
| "parallel_setup_cost": "0", + |
| "parallel_tuple_cost": "0", + |
| "min_parallel_table_scan_size": "0" + |
| }, + |
| "Triggers": [ + |
| ], + |
| "Planning Time": 0.0, + |
| "Execution Time": 0.0, + |
| "Slice statistics": [ + |
| { + |
| "Slice": 0, + |
| "Executor Memory": 0 + |
| }, + |
| { + |
| "Slice": 0, + |
| "Executor Memory": { + |
| "Average": 0, + |
| "Workers": 0, + |
| "Subworkers": 0, + |
| "Maximum Memory Used": 0 + |
| } + |
| } + |
| ], + |
| "Statement statistics": { + |
| "Memory used": 0 + |
| } + |
| } + |
| ] |
| (1 row) |
| |
| rollback; |
| -- Test display of temporary objects |
| create temp table t1(f1 float8); |
| create function pg_temp.mysin(float8) returns float8 language plpgsql |
| as 'begin return sin($1); end'; |
| select explain_filter('explain (verbose) select * from t1 where pg_temp.mysin(f1) < 0.5'); |
| explain_filter |
| -------------------------------------------------------------------------- |
| Gather Motion N:N (slice1; segments: N) (cost=N.N..N.N rows=N width=N) |
| Output: f1 |
| -> Seq Scan on pg_temp.t1 (cost=N.N..N.N rows=N width=N) |
| Output: f1 |
| Filter: (pg_temp.mysin(t1.f1) < 'N.N'::double precision) |
| Settings: jit = 'off', enable_parallel = 'off' |
| Optimizer: Postgres query optimizer |
| (7 rows) |
| |
| -- Test compute_query_id |
| set compute_query_id = on; |
| select explain_filter('explain (verbose) select * from int8_tbl i8'); |
| explain_filter |
| -------------------------------------------------------------------------- |
| Gather Motion N:N (slice1; segments: N) (cost=N.N..N.N rows=N width=N) |
| Output: q1, q2 |
| -> Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N) |
| Output: q1, q2 |
| Query Identifier: N |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
