Re: [GENERAL] Idea for the statistics collector

Good god - how old was that email? 2002???

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(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
>>
>>
>>
>
>