Re: Forcing use of indexes

At 09:52 AM 4/2/03 -0500, Tom Lane wrote:

>shared_buffers doesn't affect the estimated cost of an indexscan.
>effective_cache_size does, also random_page_cost, but you didn't mention
>having touched those.

Hi,

If my O/S has a cache of say 1GB and my DB is < 1GB and is totally in cache
would setting effective_cache_size to 1GB make the optimizer decide on
index usage just as setting random_page_cost to 1?

If random page cost is high but so is effective_cache_size does postgresql
use sequential scans first time round and then index scans second time
round if everything cached? Of course if random page cost is 1 then always
use index scan even for first read. This is probably "academic" and not
really an issue for real world.

But the main thing is: is it hard for the optimizer to tell whether a
DB/table/index is completely in effective_cache_size?

There's mention of something like this (see below), but the final
suggestion in thread was to set random_page_cost to 1, so I'm wondering how
one would use effective_cache_size.

Brian Hirt (bhirt@mobygames.com)
Re: Performance Tuning Question
Date: 2002-09-09 10:17:52 PST

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&safe=off&selm=1031586091.1345.722.camel%40loopy.tr.berkhirt.com&rnum=3

Regards,
Link.