Re: Query planner refuses to use index

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/