Re: Query planner refuses to use index - Mailing list pgsql-general

From Michael Fuhr
Subject Re: Query planner refuses to use index
Date
Msg-id 20050722132327.GB18703@winnie.fuhr.org
Whole thread Raw
In response to Re: Query planner refuses to use index  (Kilian Hagemann <hagemann1@egs.uct.ac.za>)
Responses Re: Query planner refuses to use index
List pgsql-general
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/

pgsql-general by date:

Previous
From: Leonel Nunez
Date:
Subject: Re: Copying bytea data out via pgsql
Next
From: Ezequiel Tolnay
Date:
Subject: Re: Wishlist?