Re: [GENERAL] Idea for the statistics collector

Added to TODO:

* Add tool to query pg_stat_* tables and report indexes that aren't needed
or tables that might need indexes

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> 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.
>
> This would put us in the 'mysql makes my indices for me by magic' league -
> but would be far more powerful and flexible. How to do multikey indices is
> beyond me tho.
>
> *sigh* I'm recovering from a septoplasty on my nose atm, so I might have
> some time to do some coding!
>
> Chris
>
> ----- Original Message -----
> From: "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
> To: "Martijn van Oosterhout" <kleptog(at)svana(dot)org>
> Cc: "PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)org>
> Sent: Friday, June 21, 2002 10:50 AM
> Subject: Re: [HACKERS] [GENERAL] Idea for the statistics collector
>
>
> > Martijn van Oosterhout wrote:
> > > Since it's currently all for collecting statistics on tables, why can't
> it
> > > collect another type of statistic, like:
> > >
> > > - How often the estimator gets it wrong?
> > >
> > > At the end of an index scan, the executor could compare the number of
> rows
> > > returned against what was estimated, and if it falls outside a certain
> > > range, flag it.
> > >
> > > Also, the average ratio of rows coming out of a distinct node vs the
> number
> > > going in.
> > >
> > > For a join clause, the amount of correlation between two columns (hard).
> > >
> > > etc
> > >
> > > Ideally, the planner could then use this info to make better plans.
> > > Eventually, the whole system could become somewhat self-tuning.
> > >
> > > Does anyone see any problems with this?
> >
> > [ Discussion moved to hackers.]
> >
> > I have thought that some type of feedback from the executor back into
> > the optimizer would be a good feature. Not sure how to do it, but your
> > idea makes sense. It certainly could update the table statistics after
> > a sequential scan.
> >
> > --
> > Bruce Momjian | http://candle.pha.pa.us
> > pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> > + If your life is a hard drive, | 830 Blythe Avenue
> > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073