Re: Auto explain target tables

On 07/23/2013 12:52 PM, Tom Lane wrote:
> Craig Ringer <craig@2ndquadrant.com> writes:
>>> Showing that I'm still very much learning this area myself, a bit more
>>> looking around found:
>>> http://www.postgresql.org/docs/current/static/querytree.html
>>> which makes it clear that the range table for the query will contain
>>> what you want. I suspect you'll need to find CTEs and subqueries and
>>> extract the relations they touch; I haven't yet checked to see whether
>>> they're aggregated into the top level range table, but suspect not.
> 
>> Of course, having said that I was then curious enough to go digging.
>> It's trivial to dump the parse tree or query plan tree, as documented in
>> the above linked documentation, and doing so clearly shows that
>> subqueries and CTEs have their own range tables.
> 
>> So you'll have to walk the node tree looking for range tables and check
>> to see whether any of the range table entries match one of the tables
>> you're looking for.
> 
>> It's possible that this is a completely backwards approach; if so,
>> hopefully one of the more experienced people here will correct me with
>> the simple and obvious way to do it.
> 
> That's correct if you're looking at the parser-output representation.
> However, a plan has a "flat" rangetable, so if you're looking at a plan
> rather than a raw Query it's much easier.  I'm too lazy to go check
> right now, but I think in auto_explain it would be at least as easy to
> look at the plan.

Thanks for that Tom, I appreciate your taking the time to clarify the
details there.

I should've seen that in the debug plan output:

STATEMENT:  WITH fred AS (SELECT * FROM foo)       SELECT * FROM fred;
LOG:  plan:
DETAIL:     {PLANNEDSTMT          ....          :rtable (             {RTE             :alias <>             :eref
         {ALIAS                :aliasname fred                :colnames ("stuff")                }              ...
       }             {RTE             :alias <>             :eref                {ALIAS                :aliasname foo
            :colnames ("stuff")                }             ....             }          )          :resultRelations <>
        ....
 

STATEMENT:  WITH fred AS (SELECT * FROM foo)       SELECT * FROM fred;



It's certainly as easy, if not easier, to look at the plan in
auto_explain, so that seems to be the way to go.

auto_explain gets a QueryDesc* in its executor hook, which includes a
PlannedStmt* with a List* of RangeTblEntry nodes.

Scanning the range table for relations of interest looks pretty
reasonable so long as you don't want to do anything too tricky.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services