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, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend