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