Re: Auto explain target tables

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

You'll also need a way to supply the list of tables/indexes you are
interested in to the extension. The simplest way to start with is likely
to be reading a separate file from the data dir that contains one
relation name per line. Integrating it into the postgresql.conf GUC
machinery is harder; you'll need a way to parse a list of tables from a
GUC (maybe you can re-use the search_path code for this?) or some other
way to handle your need for a multi-valued setting.

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