Re: Auto explain target tables

On 07/23/2013 11:41 AM, Craig Ringer wrote:
> On 07/23/2013 11:24 AM, Craig Ringer wrote:
>> On 07/21/2013 10:42 PM, Миша Тюрин wrote:
>>>
>>> hi, list, again. the next proposal into auto explain. one would be happy if could set list of target tables and indexes. sometimes it is very hard to detect who is using your indexes. but turn total logging on under thousands transactions per seconds is not seems like nice idea couse size of resulting log files (cpu utilization might not be so critical)
>>
>> That sounds like a good idea - and since auto_explain is a contrib
>> module, it could potentially be a way to get into PostgreSQL
>> development. The code is in contrib/auto_explain/auto_explain.c .
>>
>> The challenge here is that planned queries don't just touch one table or
>> index. You'd need to walk the query plan (a graph of Node structures) to
>> determine which table(s) and index(es) are touched by the query there's
>> something in the sources for it already (I haven't checked).
>
> 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.

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