Re: [GENERAL] Idea for the statistics collector

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@candle.pha.pa.us>
To: "Martijn van Oosterhout" <kleptog@svana.org>
Cc: "PostgreSQL-development" <pgsql-hackers@postgresql.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@candle.pha.pa.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
>