Re: Forcing use of indexes

Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
> 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?

I don't feel like going through the equations at the moment (it's open
source, read for yourself) but certainly if table+index are less than
effective_cache_size the cost estimate should be pretty low.

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

No, there is no notion of "first time round" vs "second time round".

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

It knows the table & index size as last recorded by VACUUM. This might
not match up with current reality, of course...

regards, tom lane