"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
