Re: Auto explain target tables

On 07/23/2013 12:52 PM, Tom Lane wrote:
> Craig Ringer <craig(at)2ndquadrant(dot)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