Re: Query planner refuses to use index

On Monday 25 July 2005 15:43, Michael Fuhr pondered:
> Whatever the results of your experiments, could you post the settings
> you tried and the corresponding EXPLAIN ANALYZE outputs?

I did lots of tests now that you pointed me to a useful guide, also taking
what's in the documentation into account. In the attached file I have
documented my results.

There are three sections to the file, each separated by '====' markers. The
first section deals in detail with the EXPLAIN ANALYZE info relating to the
troublesome queries. The second is probably of least interest, just showing
that I could implement my problem differently to improve performance.

But the last section is the most important, where I varied
effective_cache_size, random_page_cost, shared_buffers and cpu_tuple_costs,
each on its own with the other ones assuming default values(unless
indicated).

To summarise, increasing effective_cache_size and decreasing random_page_cost
both yield in lower index scan cost estimates while not changing the seqscan
ones. As expected, increasing shared_buffers makes no difference whatsoever
in the query cost estimates or the actual query times. A higher cpu_tuple
cost penalises the seqscans significantly while only slightly increasing the
index scan estimates.

Also note that these are all related to the query planner only, they do NOT
change the actual query time which explains why I did not include EXPLAIN
ANALYZE outputs, only plain EXPLAIN ones.

In order to make PostgreSQL choose the index scans when I need them (other
than by setting enable_seq_scans to off), I ended up choosing
        effective_cache_size 40000
        random_page_cost 2.5
        cpu_tuple_cost 0.08
as only a combination yielded the desired results. Hardly optimal, but the
real problem seems to lie with the correlation of the indexed columns (see
other post in this thread). If I encounter trouble with these somewhere down
the line, I'll post again.

Hope this helps someone out there.

--
Kilian Hagemann

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