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@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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
