Explain and filter over subplans - Mailing list pgsql-general

From Chantal Keller
Subject Explain and filter over subplans
Date
Msg-id 149c5c2f-4267-44e3-a177-d1fd24c53f6d@universite-paris-saclay.fr
Whole thread Raw
Responses Re: Explain and filter over subplans
List 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>



pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Tips on troubleshooting slow DELETE (suspect cascades)
Next
From: Rob Sargent
Date:
Subject: Re: Tips on troubleshooting slow DELETE (suspect cascades)