Re: Auto explain target tables

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.

There are some definitional issues that might be worth considering:
1. should a view used in a query be a match? (probably)
2. how about a table that's accessed via a view? (almost certainly)
3. should an inheritance child table that's excluded by constraint
exclusion, and thus not actually scanned, be a match? (debatable)

If you believe you want #2 then you don't want to look at raw Query
trees, because view expansion hasn't been done so you won't see anything
hidden under a view.

If you just blindly scan the flat rangetable of a plan then you will get
hits for all three cases. The only way to exclude #3 would be to scan
the plan tree and identify which rangetable entries are actually scanned
by scan nodes. However, if you filter in that way then you'll also
exclude #1 (since a view isn't referenced in the plan tree). You could
have #1 and not #3 if you used some complicated ie fragile hybrid method,
but I'm doubting it's worth it.

regards, tom lane