upstream/postgresql
1
0
Fork 0
mirror of https://github.com/postgres/postgres.git synced 2026-05-28 04:35:45 -04:00
Code Issues Projects Releases Packages Wiki Activity Actions
postgresql/contrib/pg_overexplain/expected/pg_overexplain.out
Amit Langote 059cf7f58d Fix pg_overexplain to emit valid output with RANGE_TABLE option. 
overexplain_range_table() emitted the "Unprunable RTIs" and "Result
RTIs" properties before closing the "Range Table" group.  In the JSON
and YAML formats the Range Table group is rendered as an array of RTE
objects, so emitting key/value pairs inside it produced structurally
invalid output.  The XML format had a related oddity, with these
elements nested inside <Range-Table> rather than appearing as its
siblings.

These fields are properties of the PlannedStmt as a whole, not of any
individual RTE, so close the Range Table group before emitting them.
They now appear as siblings of "Range Table" in the parent Query
object, which is what was intended.

Also add a test exercising FORMAT JSON with RANGE_TABLE so that any
future regression in the output structure is caught.

Reported-by: Satyanarayana Narlapuram <satyanarlapuram@gmail.com>
Author: Satyanarayana Narlapuram <satyanarlapuram@gmail.com>
Reviewed-by: Amit Langote <amitlangote09@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/CAHg+QDdDrdqMr98a_OBYDYmK3RaT7XwCEShZfvDYKZpZTfOEjQ@mail.gmail.com
Backpatch-through: 18
2026-04-16 13:47:07 +09:00

786 lines
34 KiB
Text
Raw Permalink Blame History

-- These tests display internal details that would not be stable under
-- debug_parallel_query, so make sure that option is disabled.
SET debug_parallel_query = off;
-- Make sure that we don't print any JIT-related information, as that
-- would also make results unstable.
SET jit = off;
-- These options do not exist, so these queries should all fail.
EXPLAIN (DEBUFF) SELECT 1;
ERROR: unrecognized EXPLAIN option "debuff"
LINE 1: EXPLAIN (DEBUFF) SELECT 1;
^
EXPLAIN (DEBUG) SELECT 1;
ERROR: unrecognized EXPLAIN option "debug"
LINE 1: EXPLAIN (DEBUG) SELECT 1;
^
EXPLAIN (RANGE_TABLE) SELECT 1;
ERROR: unrecognized EXPLAIN option "range_table"
LINE 1: EXPLAIN (RANGE_TABLE) SELECT 1;
^
-- Load the module that creates the options.
LOAD 'pg_overexplain';
-- The first option still does not exist, but the others do.
EXPLAIN (DEBUFF) SELECT 1;
ERROR: unrecognized EXPLAIN option "debuff"
LINE 1: EXPLAIN (DEBUFF) SELECT 1;
^
EXPLAIN (DEBUG) SELECT 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=4)
Disabled Nodes: 0
Parallel Safe: false
Plan Node ID: 0
PlannedStmt:
Command Type: select
Flags: canSetTag
Subplans Needing Rewind: none
Relation OIDs: none
Executor Parameter Types: none
Parse Location: 0 to end
(11 rows)
EXPLAIN (RANGE_TABLE) SELECT 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=4)
RTIs: 1
RTI 1 (result):
Eref: "*RESULT*" ()
(4 rows)
-- Create a partitioned table.
CREATE TABLE vegetables (id serial, name text, genus text)
PARTITION BY LIST (genus);
CREATE TABLE daucus PARTITION OF vegetables FOR VALUES IN ('daucus');
CREATE TABLE brassica PARTITION OF vegetables FOR VALUES IN ('brassica');
INSERT INTO vegetables (name, genus)
VALUES ('carrot', 'daucus'), ('bok choy', 'brassica'),
('brocooli', 'brassica'), ('cauliflower', 'brassica'),
('cabbage', 'brassica'), ('kohlrabi', 'brassica'),
('rutabaga', 'brassica'), ('turnip', 'brassica');
VACUUM ANALYZE vegetables;
-- We filter relation OIDs out of the test output in order to avoid
-- test instability. This is currently only needed for EXPLAIN (DEBUG), not
-- EXPLAIN (RANGE_TABLE). Also suppress actual row counts, which are not
-- stable (e.g. 1/8 is 0.12 on some buildfarm machines and 0.13 on others).
CREATE FUNCTION explain_filter(text) RETURNS SETOF text
LANGUAGE plpgsql AS
$$
DECLARE
ln text;
BEGIN
FOR ln IN EXECUTE $1
LOOP
ln := regexp_replace(ln, 'Relation OIDs:( \m\d+\M)+',
'Relation OIDs: NNN...', 'g');
ln := regexp_replace(ln, '<Relation-OIDs>( ?\m\d+\M)+</Relation-OIDs>',
'<Relation-OIDs>NNN...</Relation-OIDs>', 'g');
ln := regexp_replace(ln, 'actual rows=\d+\.\d+',
'actual rows=N.NN', 'g');
RETURN NEXT ln;
END LOOP;
END;
$$;
-- Test with both options together and an aggregate.
SELECT explain_filter($$
EXPLAIN (DEBUG, RANGE_TABLE, COSTS OFF)
SELECT genus, array_agg(name ORDER BY name) FROM vegetables GROUP BY genus
$$);
explain_filter
-----------------------------------------------------
GroupAggregate
Group Key: vegetables.genus
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 0
-> Sort
Sort Key: vegetables.genus, vegetables.name
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 1
-> Append
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 2
Append RTIs: 1
Child Append RTIs: none
-> Seq Scan on brassica vegetables_1
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 3
Scan RTI: 3
-> Seq Scan on daucus vegetables_2
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 4
Scan RTI: 4
PlannedStmt:
Command Type: select
Flags: canSetTag
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: none
Parse Location: 0 to end
RTI 1 (relation, inherited, in-from-clause):
Eref: vegetables (id, name, genus)
Relation: vegetables
Relation Kind: partitioned_table
Relation Lock Mode: AccessShareLock
Permission Info Index: 1
RTI 2 (group):
Eref: "*GROUP*" (genus)
RTI 3 (relation, in-from-clause):
Alias: vegetables (id, name, genus)
Eref: vegetables (id, name, genus)
Relation: brassica
Relation Kind: relation
Relation Lock Mode: AccessShareLock
RTI 4 (relation, in-from-clause):
Alias: vegetables (id, name, genus)
Eref: vegetables (id, name, genus)
Relation: daucus
Relation Kind: relation
Relation Lock Mode: AccessShareLock
Unprunable RTIs: 1 3 4
(54 rows)
-- Test a different output format.
SELECT explain_filter($$
EXPLAIN (DEBUG, RANGE_TABLE, FORMAT XML, COSTS OFF)
SELECT genus, array_agg(name ORDER BY name) FROM vegetables GROUP BY genus
$$);
explain_filter
---------------------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain"> +
<Query> +
<Plan> +
<Node-Type>Aggregate</Node-Type> +
<Strategy>Sorted</Strategy> +
<Partial-Mode>Simple</Partial-Mode> +
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Disabled>false</Disabled> +
<Group-Key> +
<Item>vegetables.genus</Item> +
</Group-Key> +
<Disabled-Nodes>0</Disabled-Nodes> +
<Parallel-Safe>true</Parallel-Safe> +
<Plan-Node-ID>0</Plan-Node-ID> +
<extParam>none</extParam> +
<allParam>none</allParam> +
<Plans> +
<Plan> +
<Node-Type>Sort</Node-Type> +
<Parent-Relationship>Outer</Parent-Relationship> +
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Disabled>false</Disabled> +
<Sort-Key> +
<Item>vegetables.genus</Item> +
<Item>vegetables.name</Item> +
</Sort-Key> +
<Disabled-Nodes>0</Disabled-Nodes> +
<Parallel-Safe>true</Parallel-Safe> +
<Plan-Node-ID>1</Plan-Node-ID> +
<extParam>none</extParam> +
<allParam>none</allParam> +
<Plans> +
<Plan> +
<Node-Type>Append</Node-Type> +
<Parent-Relationship>Outer</Parent-Relationship> +
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Disabled>false</Disabled> +
<Disabled-Nodes>0</Disabled-Nodes> +
<Parallel-Safe>true</Parallel-Safe> +
<Plan-Node-ID>2</Plan-Node-ID> +
<extParam>none</extParam> +
<allParam>none</allParam> +
<Append-RTIs>1</Append-RTIs> +
<Child-Append-RTIs>none</Child-Append-RTIs> +
<Subplans-Removed>0</Subplans-Removed> +
<Plans> +
<Plan> +
<Node-Type>Seq Scan</Node-Type> +
<Parent-Relationship>Member</Parent-Relationship>+
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Relation-Name>brassica</Relation-Name> +
<Alias>vegetables_1</Alias> +
<Disabled>false</Disabled> +
<Disabled-Nodes>0</Disabled-Nodes> +
<Parallel-Safe>true</Parallel-Safe> +
<Plan-Node-ID>3</Plan-Node-ID> +
<extParam>none</extParam> +
<allParam>none</allParam> +
<Scan-RTI>3</Scan-RTI> +
</Plan> +
<Plan> +
<Node-Type>Seq Scan</Node-Type> +
<Parent-Relationship>Member</Parent-Relationship>+
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Relation-Name>daucus</Relation-Name> +
<Alias>vegetables_2</Alias> +
<Disabled>false</Disabled> +
<Disabled-Nodes>0</Disabled-Nodes> +
<Parallel-Safe>true</Parallel-Safe> +
<Plan-Node-ID>4</Plan-Node-ID> +
<extParam>none</extParam> +
<allParam>none</allParam> +
<Scan-RTI>4</Scan-RTI> +
</Plan> +
</Plans> +
</Plan> +
</Plans> +
</Plan> +
</Plans> +
</Plan> +
<PlannedStmt> +
<Command-Type>select</Command-Type> +
<Flags>canSetTag</Flags> +
<Subplans-Needing-Rewind>none</Subplans-Needing-Rewind> +
<Relation-OIDs>NNN...</Relation-OIDs> +
<Executor-Parameter-Types>none</Executor-Parameter-Types> +
<Parse-Location>0 to end</Parse-Location> +
</PlannedStmt> +
<Range-Table> +
<Range-Table-Entry> +
<RTI>1</RTI> +
<Kind>relation</Kind> +
<Inherited>true</Inherited> +
<In-From-Clause>true</In-From-Clause> +
<Eref>vegetables (id, name, genus)</Eref> +
<Relation>vegetables</Relation> +
<Relation-Kind>partitioned_table</Relation-Kind> +
<Relation-Lock-Mode>AccessShareLock</Relation-Lock-Mode> +
<Permission-Info-Index>1</Permission-Info-Index> +
<Security-Barrier>false</Security-Barrier> +
<Lateral>false</Lateral> +
</Range-Table-Entry> +
<Range-Table-Entry> +
<RTI>2</RTI> +
<Kind>group</Kind> +
<Inherited>false</Inherited> +
<In-From-Clause>false</In-From-Clause> +
<Eref>"*GROUP*" (genus)</Eref> +
<Security-Barrier>false</Security-Barrier> +
<Lateral>false</Lateral> +
</Range-Table-Entry> +
<Range-Table-Entry> +
<RTI>3</RTI> +
<Kind>relation</Kind> +
<Inherited>false</Inherited> +
<In-From-Clause>true</In-From-Clause> +
<Alias>vegetables (id, name, genus)</Alias> +
<Eref>vegetables (id, name, genus)</Eref> +
<Relation>brassica</Relation> +
<Relation-Kind>relation</Relation-Kind> +
<Relation-Lock-Mode>AccessShareLock</Relation-Lock-Mode> +
<Security-Barrier>false</Security-Barrier> +
<Lateral>false</Lateral> +
</Range-Table-Entry> +
<Range-Table-Entry> +
<RTI>4</RTI> +
<Kind>relation</Kind> +
<Inherited>false</Inherited> +
<In-From-Clause>true</In-From-Clause> +
<Alias>vegetables (id, name, genus)</Alias> +
<Eref>vegetables (id, name, genus)</Eref> +
<Relation>daucus</Relation> +
<Relation-Kind>relation</Relation-Kind> +
<Relation-Lock-Mode>AccessShareLock</Relation-Lock-Mode> +
<Security-Barrier>false</Security-Barrier> +
<Lateral>false</Lateral> +
</Range-Table-Entry> +
</Range-Table> +
<Unprunable-RTIs>1 3 4</Unprunable-RTIs> +
<Result-RTIs>none</Result-RTIs> +
</Query> +
</explain>
(1 row)
-- Test JSON format with RANGE_TABLE to verify valid JSON structure.
SELECT explain_filter($$
EXPLAIN (RANGE_TABLE, FORMAT JSON, COSTS OFF)
SELECT genus, array_agg(name ORDER BY name) FROM vegetables GROUP BY genus
$$);
explain_filter
----------------------------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Aggregate", +
"Strategy": "Sorted", +
"Partial Mode": "Simple", +
"Parallel Aware": false, +
"Async Capable": false, +
"Disabled": false, +
"Group Key": ["vegetables.genus"], +
"Plans": [ +
{ +
"Node Type": "Sort", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Disabled": false, +
"Sort Key": ["vegetables.genus", "vegetables.name"],+
"Plans": [ +
{ +
"Node Type": "Append", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Disabled": false, +
"Append RTIs": "1", +
"Child Append RTIs": "none", +
"Subplans Removed": 0, +
"Plans": [ +
{ +
"Node Type": "Seq Scan", +
"Parent Relationship": "Member", +
"Parallel Aware": false, +
"Async Capable": false, +
"Relation Name": "brassica", +
"Alias": "vegetables_1", +
"Disabled": false, +
"Scan RTI": 3 +
}, +
{ +
"Node Type": "Seq Scan", +
"Parent Relationship": "Member", +
"Parallel Aware": false, +
"Async Capable": false, +
"Relation Name": "daucus", +
"Alias": "vegetables_2", +
"Disabled": false, +
"Scan RTI": 4 +
} +
] +
} +
] +
} +
] +
}, +
"Range Table": [ +
{ +
"RTI": 1, +
"Kind": "relation", +
"Inherited": true, +
"In From Clause": true, +
"Eref": "vegetables (id, name, genus)", +
"Relation": "vegetables", +
"Relation Kind": "partitioned_table", +
"Relation Lock Mode": "AccessShareLock", +
"Permission Info Index": 1, +
"Security Barrier": false, +
"Lateral": false +
}, +
{ +
"RTI": 2, +
"Kind": "group", +
"Inherited": false, +
"In From Clause": false, +
"Eref": "\"*GROUP*\" (genus)", +
"Security Barrier": false, +
"Lateral": false +
}, +
{ +
"RTI": 3, +
"Kind": "relation", +
"Inherited": false, +
"In From Clause": true, +
"Alias": "vegetables (id, name, genus)", +
"Eref": "vegetables (id, name, genus)", +
"Relation": "brassica", +
"Relation Kind": "relation", +
"Relation Lock Mode": "AccessShareLock", +
"Security Barrier": false, +
"Lateral": false +
}, +
{ +
"RTI": 4, +
"Kind": "relation", +
"Inherited": false, +
"In From Clause": true, +
"Alias": "vegetables (id, name, genus)", +
"Eref": "vegetables (id, name, genus)", +
"Relation": "daucus", +
"Relation Kind": "relation", +
"Relation Lock Mode": "AccessShareLock", +
"Security Barrier": false, +
"Lateral": false +
} +
], +
"Unprunable RTIs": "1 3 4", +
"Result RTIs": "none" +
} +
]
(1 row)
-- Test just the DEBUG option. Verify that it shows information about
-- disabled nodes, parallel safety, and the parallelModeNeeded flag.
SET enable_seqscan = false;
SET debug_parallel_query = true;
SELECT explain_filter($$
EXPLAIN (DEBUG, COSTS OFF)
SELECT genus, array_agg(name ORDER BY name) FROM vegetables GROUP BY genus
$$);
explain_filter
-----------------------------------------------------------
Gather
Workers Planned: 1
Single Copy: true
Disabled Nodes: 0
Parallel Safe: false
Plan Node ID: 0
-> GroupAggregate
Group Key: vegetables.genus
Disabled Nodes: 2
Parallel Safe: true
Plan Node ID: 1
-> Sort
Sort Key: vegetables.genus, vegetables.name
Disabled Nodes: 2
Parallel Safe: true
Plan Node ID: 2
-> Append
Disabled Nodes: 2
Parallel Safe: true
Plan Node ID: 3
-> Seq Scan on brassica vegetables_1
Disabled: true
Disabled Nodes: 1
Parallel Safe: true
Plan Node ID: 4
-> Seq Scan on daucus vegetables_2
Disabled: true
Disabled Nodes: 1
Parallel Safe: true
Plan Node ID: 5
PlannedStmt:
Command Type: select
Flags: canSetTag, parallelModeNeeded
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: none
Parse Location: 0 to end
(37 rows)
SET debug_parallel_query = false;
RESET enable_seqscan;
-- Test the DEBUG option with a non-SELECT query, and also verify that the
-- hasReturning flag is shown.
SELECT explain_filter($$
EXPLAIN (DEBUG, COSTS OFF)
INSERT INTO vegetables (name, genus)
VALUES ('Brotero''s carrot', 'brassica') RETURNING id
$$);
explain_filter
----------------------------------
Insert on vegetables
Disabled Nodes: 0
Parallel Safe: false
Plan Node ID: 0
-> Result
Disabled Nodes: 0
Parallel Safe: false
Plan Node ID: 1
PlannedStmt:
Command Type: insert
Flags: hasReturning, canSetTag
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: 0
Parse Location: 0 to end
(15 rows)
-- Create an index, and then attempt to force a nested loop with inner index
-- scan so that we can see parameter-related information. Also, let's try
-- actually running the query, but try to suppress potentially variable output.
CREATE INDEX ON vegetables (id);
ANALYZE vegetables;
SET enable_hashjoin = false;
SET enable_material = false;
SET enable_mergejoin = false;
SET enable_seqscan = false;
SELECT explain_filter($$
EXPLAIN (BUFFERS OFF, COSTS OFF, SUMMARY OFF, TIMING OFF, ANALYZE, DEBUG)
SELECT * FROM vegetables v1, vegetables v2 WHERE v1.id = v2.id;
$$);
explain_filter
------------------------------------------------------------------------------------------
Nested Loop (actual rows=N.NN loops=1)
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 0
-> Append (actual rows=N.NN loops=1)
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 1
-> Index Scan using brassica_id_idx on brassica v1_1 (actual rows=N.NN loops=1)
Index Searches: 1
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 2
-> Index Scan using daucus_id_idx on daucus v1_2 (actual rows=N.NN loops=1)
Index Searches: 1
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 3
-> Append (actual rows=N.NN loops=8)
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 4
extParam: 0
allParam: 0
-> Index Scan using brassica_id_idx on brassica v2_1 (actual rows=N.NN loops=8)
Index Cond: (id = v1.id)
Index Searches: 8
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 5
extParam: 0
allParam: 0
-> Index Scan using daucus_id_idx on daucus v2_2 (actual rows=N.NN loops=8)
Index Cond: (id = v1.id)
Index Searches: 8
Disabled Nodes: 0
Parallel Safe: true
Plan Node ID: 6
extParam: 0
allParam: 0
PlannedStmt:
Command Type: select
Flags: canSetTag
Subplans Needing Rewind: none
Relation OIDs: NNN...
Executor Parameter Types: 23
Parse Location: 0 to end
(47 rows)
RESET enable_hashjoin;
RESET enable_material;
RESET enable_mergejoin;
RESET enable_seqscan;
-- Test the RANGE_TABLE option with a case that allows partition pruning.
EXPLAIN (RANGE_TABLE, COSTS OFF)
SELECT * FROM vegetables WHERE genus = 'daucus';
QUERY PLAN
----------------------------------------------
Seq Scan on daucus vegetables
Filter: (genus = 'daucus'::text)
Scan RTI: 2
Elided Node Type: Append
Elided Node RTIs: 1
RTI 1 (relation, inherited, in-from-clause):
Eref: vegetables (id, name, genus)
Relation: vegetables
Relation Kind: partitioned_table
Relation Lock Mode: AccessShareLock
Permission Info Index: 1
RTI 2 (relation, in-from-clause):
Alias: vegetables (id, name, genus)
Eref: vegetables (id, name, genus)
Relation: daucus
Relation Kind: relation
Relation Lock Mode: AccessShareLock
Unprunable RTIs: 1 2
(18 rows)
-- Also test a case that involves a write.
EXPLAIN (RANGE_TABLE, COSTS OFF)
INSERT INTO vegetables (name, genus) VALUES ('broccoflower', 'brassica');
QUERY PLAN
----------------------------------------
Insert on vegetables
Nominal RTI: 1
Exclude Relation RTI: 0
-> Result
RTIs: 2
RTI 1 (relation):
Eref: vegetables (id, name, genus)
Relation: vegetables
Relation Kind: partitioned_table
Relation Lock Mode: RowExclusiveLock
Permission Info Index: 1
RTI 2 (result):
Eref: "*RESULT*" ()
Unprunable RTIs: 1
Result RTIs: 1
(15 rows)
-- should show "Subplan: sub"
EXPLAIN (RANGE_TABLE, COSTS OFF)
SELECT * FROM vegetables v,
(SELECT * FROM vegetables WHERE genus = 'daucus' OFFSET 0) sub;
QUERY PLAN
----------------------------------------------
Nested Loop
-> Seq Scan on daucus vegetables
Filter: (genus = 'daucus'::text)
Scan RTI: 6
Elided Node Type: Append
Elided Node RTIs: 5
Elided Node Type: SubqueryScan
Elided Node RTIs: 2
-> Append
Append RTIs: 1
Child Append RTIs: none
-> Seq Scan on brassica v_1
Scan RTI: 3
-> Seq Scan on daucus v_2
Scan RTI: 4
RTI 1 (relation, inherited, in-from-clause):
Alias: v ()
Eref: v (id, name, genus)
Relation: vegetables
Relation Kind: partitioned_table
Relation Lock Mode: AccessShareLock
Permission Info Index: 1
RTI 2 (subquery, in-from-clause):
Alias: sub ()
Eref: sub (id, name, genus)
RTI 3 (relation, in-from-clause):
Alias: v (id, name, genus)
Eref: v (id, name, genus)
Relation: brassica
Relation Kind: relation
Relation Lock Mode: AccessShareLock
RTI 4 (relation, in-from-clause):
Alias: v (id, name, genus)
Eref: v (id, name, genus)
Relation: daucus
Relation Kind: relation
Relation Lock Mode: AccessShareLock
RTI 5 (relation, inherited, in-from-clause):
Subplan: sub
Eref: vegetables (id, name, genus)
Relation: vegetables
Relation Kind: partitioned_table
Relation Lock Mode: AccessShareLock
Permission Info Index: 2
RTI 6 (relation, in-from-clause):
Subplan: sub
Alias: vegetables (id, name, genus)
Eref: vegetables (id, name, genus)
Relation: daucus
Relation Kind: relation
Relation Lock Mode: AccessShareLock
Unprunable RTIs: 1 3 4 5 6
(52 rows)
-- should show "Subplan: unnamed_subquery"
EXPLAIN (RANGE_TABLE, COSTS OFF)
SELECT * FROM vegetables v,
(SELECT * FROM vegetables WHERE genus = 'daucus' OFFSET 0);
QUERY PLAN
----------------------------------------------
Nested Loop
-> Seq Scan on daucus vegetables
Filter: (genus = 'daucus'::text)
Scan RTI: 6
Elided Node Type: Append
Elided Node RTIs: 5
Elided Node Type: SubqueryScan
Elided Node RTIs: 2
-> Append
Append RTIs: 1
Child Append RTIs: none
-> Seq Scan on brassica v_1
Scan RTI: 3
-> Seq Scan on daucus v_2
Scan RTI: 4
RTI 1 (relation, inherited, in-from-clause):
Alias: v ()
Eref: v (id, name, genus)
Relation: vegetables
Relation Kind: partitioned_table
Relation Lock Mode: AccessShareLock
Permission Info Index: 1
RTI 2 (subquery, in-from-clause):
Eref: unnamed_subquery (id, name, genus)
RTI 3 (relation, in-from-clause):
Alias: v (id, name, genus)
Eref: v (id, name, genus)
Relation: brassica
Relation Kind: relation
Relation Lock Mode: AccessShareLock
RTI 4 (relation, in-from-clause):
Alias: v (id, name, genus)
Eref: v (id, name, genus)
Relation: daucus
Relation Kind: relation
Relation Lock Mode: AccessShareLock
RTI 5 (relation, inherited, in-from-clause):
Subplan: unnamed_subquery
Eref: vegetables (id, name, genus)
Relation: vegetables
Relation Kind: partitioned_table
Relation Lock Mode: AccessShareLock
Permission Info Index: 2
RTI 6 (relation, in-from-clause):
Subplan: unnamed_subquery
Alias: vegetables (id, name, genus)
Eref: vegetables (id, name, genus)
Relation: daucus
Relation Kind: relation
Relation Lock Mode: AccessShareLock
Unprunable RTIs: 1 3 4 5 6
(51 rows)
-- Property graph test
CREATE PROPERTY GRAPH vegetables_graph
VERTEX TABLES
(
daucus KEY(name) DEFAULT LABEL LABEL vegetables,
brassica KEY(name) DEFAULT LABEL LABEL vegetables
);
EXPLAIN (RANGE_TABLE, COSTS OFF)
SELECT * FROM GRAPH_TABLE (vegetables_graph MATCH (v1 IS vegetables) WHERE v1.genus = 'daucus' COLUMNS (v1.name));
QUERY PLAN
----------------------------------------------
Append
Append RTIs: 1
Child Append RTIs: none
-> Seq Scan on daucus
Filter: (genus = 'daucus'::text)
Scan RTI: 4
Elided Node Type: SubqueryScan
Elided Node RTIs: 2
-> Seq Scan on brassica
Filter: (genus = 'daucus'::text)
Scan RTI: 5
Elided Node Type: SubqueryScan
Elided Node RTIs: 3
RTI 1 (subquery, inherited, in-from-clause):
Eref: "graph_table" (name)
Relation: vegetables_graph
Relation Kind: property_graph
Relation Lock Mode: AccessShareLock
Permission Info Index: 1
Lateral: true
RTI 2 (subquery):
Eref: unnamed_subquery (name)
Lateral: true
RTI 3 (subquery):
Eref: unnamed_subquery (name)
Lateral: true
RTI 4 (relation):
Subplan: unnamed_subquery
Eref: daucus (id, name, genus)
Relation: daucus
Relation Kind: relation
Relation Lock Mode: AccessShareLock
Permission Info Index: 2
RTI 5 (relation):
Subplan: unnamed_subquery_1
Eref: brassica (id, name, genus)
Relation: brassica
Relation Kind: relation
Relation Lock Mode: AccessShareLock
Permission Info Index: 3
Unprunable RTIs: 1 4 5
(41 rows)
Reference in a new issue View git blame Copy permalink