Re: index usage (and foreign keys/triggers)

On Thu, 27 Feb 2003, Patrik Kudo wrote:


> Thanks for a good explanation! However, a setting lower than 2 seems a
> bit scary for me though. Our databases are quite large due to many large
> objects, in some cases around 4Gb, so all the data couldn't possible be
> cached all the time. The most frequently accessed tables however are
> fewer and smaller and would probably easily fit into the 1-2Gb RAM
> (that's the span we usually have on the servers).
>   Any top of mind suggestions or reflections on tuning strategies? ;)

Well, my experience has been that accidentally picking an index lookup
when a sequential scan would be better may cost up to twice as much as the
seq scan, due to the slower random access.  And usually these are queries
you expect to be slow anyway, like something that selects 90% of a 10M row
table.

But, making the mistake the other way can be much more costly.  I've
watched queries that took 30 or more seconds with a seq scan drop to sub
second with indexes.  So, don't be afraid about dropping below 2.  Just
make sure it makes senese for your database.  note that you can change
random_page_cost on the fly as well, so you could do something like:

begin;
select * from table a;
set random_page_cost=1.2;
select * from table b where c='d';
set random_page_cost=3.0;
...