Explain and filter over subplans

From: Chantal Keller <chantal(dot)keller(at)universite-paris-saclay(dot)fr>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Explain and filter over subplans
Date: 2024-01-18 16:52:18
Message-ID: 149c5c2f-4267-44e3-a177-d1fd24c53f6d@universite-paris-saclay.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I would like "explain" to output formulas for filtering over
subplans. Is it possible?

Here is a minimal example. Consider the queries:

create table t(a int);
explain (format xml, verbose true) select * from t where a >= all
(select * from t);

I put the result of the second query at the end of the e-mail.

The root node filters over "SubPlan 1", which is the materialization of
the inner "select * from t". But nothing indicates that this filtering
consists in checking that a is greater or equal than all the elements
returned by the subplan.

Is there a way to print it?

Many thanks
Chantal

QUERY PLAN
----------------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain"> +
<Query> +
<Plan> +
<Node-Type>Seq Scan</Node-Type> +
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Relation-Name>t</Relation-Name> +
<Schema>public</Schema> +
<Alias>t</Alias> +
<Startup-Cost>0.00</Startup-Cost> +
<Total-Cost>69688.75</Total-Cost> +
<Plan-Rows>1275</Plan-Rows> +
<Plan-Width>4</Plan-Width> +
<Output> +
<Item>t.a</Item> +
</Output> +
<Filter>(SubPlan 1)</Filter> +
<Plans> +
<Plan> +
<Node-Type>Materialize</Node-Type> +
<Parent-Relationship>SubPlan</Parent-Relationship> +
<Subplan-Name>SubPlan 1</Subplan-Name> +
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Startup-Cost>0.00</Startup-Cost> +
<Total-Cost>48.25</Total-Cost> +
<Plan-Rows>2550</Plan-Rows> +
<Plan-Width>4</Plan-Width> +
<Output> +
<Item>t_1.a</Item> +
</Output> +
<Plans> +
<Plan> +
<Node-Type>Seq Scan</Node-Type> +
<Parent-Relationship>Outer</Parent-Relationship>+
<Parallel-Aware>false</Parallel-Aware> +
<Async-Capable>false</Async-Capable> +
<Relation-Name>t</Relation-Name> +
<Schema>public</Schema> +
<Alias>t_1</Alias> +
<Startup-Cost>0.00</Startup-Cost> +
<Total-Cost>35.50</Total-Cost> +
<Plan-Rows>2550</Plan-Rows> +
<Plan-Width>4</Plan-Width> +
<Output> +
<Item>t_1.a</Item> +
</Output> +
</Plan> +
</Plans> +
</Plan> +
</Plans> +
</Plan> +
</Query> +
</explain>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2024-01-18 16:53:52 Re: Tips on troubleshooting slow DELETE (suspect cascades)
Previous Message Adrian Klaver 2024-01-18 16:46:46 Re: Tips on troubleshooting slow DELETE (suspect cascades)