Let me add people's expections of the optimizer and the "it isn't using the index" questions are getting very old. I have beefed up the FAQ item on this a month ago, but that hasn't reduced the number of questions. I almost want to require people to read a specific FAQ item 4.8 before we will reply to anything.
Maybe that FAQ item needs more info. Tom can't be running around trying to check all these optimizer reports when >90% are just people not understanding the basics of optimization or query performance. Maybe we need an optimizer FAQ that will answer the basic questions for people. --------------------------------------------------------------------------- Tom Lane wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > > While trying to optimise a query I found that running VACUUM ANALYSE > > changed all the Index Scans to Seq Scans and that the only way to revert > > to Index Scans was the add "enable_seqscan = 0" in postgresql.conf. > >> > >> EXPLAIN ANALYZE output would be more interesting than just EXPLAIN. > >> Also, what does the pg_stats view show for these tables? > > > Thanks, pg_stats output is rather big so I attached it in a separate > > file. Here are the EXPLAIN ANALYZE ouputs: > > Tell you the truth, I'm having a real hard time getting excited over > a bug report that says the planner chose a plan taking 10.90 seconds > in preference to one taking 7.96 seconds. > > Any time the planner's estimates are within a factor of 2 of reality, > I figure it's done very well. The inherent unknowns are so large that > that really amounts to divination. We can't expect to choose a perfect > plan every time --- if we can avoid choosing a truly stupid plan (say, > one that takes a couple orders of magnitude more time than the best > possible plan) then we ought to be happy. > > But having said that, it would be interesting to see if adjusting some > of the planner cost parameters would yield better results in your > situation. The coarsest of these is random_page_cost, which is > presently 4.0 by default. Although I have done some moderately > extensive measurements to get that figure, other folks have reported > that lower numbers like 3.0 or even less seem to suit their platforms > better. In general a lower random_page_cost will favor indexscans... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (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
