On Fri, Jul 22, 2005 at 10:46:39AM +0200, Kilian Hagemann wrote: > > - did you run "VACUUM ANALYZE speed" lately? > > Yes, just before I ran all of the queries in my last email. Did you run VACUUM ANALYZE or just ANALYZE? Could we see the output of VACUUM ANALYZE VERBOSE speed? > Hence I mentioned increasing default_statistics_target to 50 and > reanalysing, which didn't help either. It might be better to use ALTER TABLE to set the statistics target for specific columns instead of changing the system-wide default -- no need to spend time over-analyzing columns when it's not necessary. And if EXPLAIN'S row estimates are already reasonably accurate, then increasing the statistics will have little effect on the planner's decisions. > Hmm, that's interesting. I need to set random_page_cost as low as 0.5 for the > index scan's cost to dip below that of the seq_scan. Surely that's a > non-realistic setting and not what I want in the long run. What are the values of other relevant settings, like shared_buffers, effective_cache_size, and cpu_index_tuple_cost? How much memory does this system have? > station_data=# SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'speed%'; > relname | relkind | reltuples | relpages > ------------------+---------+-------------------+---------- > speed | r | 1.39002e+07 | 68138 > speed_pkey | i | 1000 | 1 That's odd -- why aren't there more tuples and pages in the speed_pkey index? Those look like never-been-vacuumed defaults. Are you sure you've been vacuuming this table, or have you just been analyzing it? How much update/delete activity does this table undergo? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
