Re: Query planner refuses to use index

On Friday 22 July 2005 15:23, Michael Fuhr pondered:
> Did you run VACUUM ANALYZE or just ANALYZE? Could we see the output
> of VACUUM ANALYZE VERBOSE speed?

I just ran a plain ANALYZE then. When I VACUUM ANALYZE the table the
(inferior) sequential scan strategy is still chosen over the index scan. Here
is the output of VACUUM ANALYZE VERBOSE speed (second VACUUM ANALYZE):

INFO: vacuuming "public.speed"
INFO: index "speed_pkey" now contains 13959950 row versions in 53283 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 3.73s/0.57u sec elapsed 258.89 sec.
INFO: "speed": found 0 removable, 13959950 nonremovable row versions in 68138
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 5.50s/1.32u sec elapsed 365.29 sec.
INFO: analyzing "public.speed"
INFO: "speed": 68138 pages, 3000 rows sampled, 13900152 estimated total rows
VACUUM

> 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?

shared_buffers, effective_cache_size and cpu_index_tuple_cost all have their
default values of 1000, 1000 and 0.001 respectively. From their descriptions
I gather that's reasonable and I don't know how I would optimise these for my
system (I cannot find any hints in the documentation). It has 512MB of RAM,
Pentium M 1.5Ghz, but is used as a desktop system (KDE) with the database
sitting on an external 7200rpm USB 2.0 harddisk for space reasons.

> > 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?

I also noticed that. I didn't realise that a plain ANALYZE wouldn't update the
stats of the index, only a VACUUM ANALYZE seems to take care of that (why
isn't this documented?). As I said above, this seems to make no difference.

> How much update/delete activity does this table undergo?

It's a low usage table/database, I'm its only user (not really production) and
insertion happens infrequently (only in the beginning of its lifetime) while
queries of the type I posted constitute the bulk of the resource-intensive
database usage.

--
Kilian Hagemann

Climate Systems Analysis Group
University of Cape Town
Republic of South Africa
Tel(w): ++27 21 650 2748