Re: [GENERAL] Idea for the statistics collector - Mailing list pgsql-hackers

Christopher Kings-Lynne wrote:
> Good god - how old was that email? 2002???

Yep, and been in my mailbox since then, waiting for me to process it
into a TODO entry.

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


> 
> Chris
> 
> Bruce Momjian wrote:
> > 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@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
> >>>
> >>
> >>
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 4: Don't 'kill -9' the postmaster
> >>
> >>
> >>
> > 
> > 
> 

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