Re: [GENERAL] Idea for the statistics collector

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I was thinking of writing a command line tool like 'pgtune' that looks at
> the stats views and will generate SQL code for, or do automatically the
> following:

> * Dropping indices that are never used
> * Creating appropriate indices to avoid large, expensive sequential scans.

Dropping unused indices sounds good --- but beware of dropping unique
indexes; they may be there to enforce a constraint, and not because of
any desire to use them in queries.

I'm not sure how you're going to automatically intuit appropriate
indexes to add, though.  You'd need to look at a suitable workload
(ie, a representative set of queries) which is not data that's readily
available from the stats views.  Perhaps we could expect the DBA to
provide a segment of log output that includes debug_print_query
and show_query_stats results.
        regards, tom lane