Re: Auto explain target tables

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
transactionsper 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.

You'll find the code in src/backend/nodes/print.c informative and
useful, though not directly reusable.

Reading src/include/nodes/primnodes.h would be a good idea; it shows how
subqueries, etc, are represented.

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