Re: reducing random_page_cost from 4 to 2 to force index scan

On Wed, Apr 27, 2011 at 5:19 PM, Sok Ann Yap <sokann(at)gmail(dot)com> wrote:
>
> I understand the need to tune PostgreSQL properly for my use case.
> What I am curious about is, for the data set I have, under what
> circumstances (hardware/workload/cache status/etc) would a sequential
> scan really be faster than an index scan for that particular query?

The sequential scan on contacts can be terminated as soon as the first
matching row is found. If each block of the contacts table contains
one example of each salutation, then the inner sequential scan will
always be very short, and faster than an index scan.

I can engineer this to be the case by populating the table like this:

insert into contacts select (generate_series%44+1)::int from
generate_series (1,1000000);

Here I get the seq scan being 2.6ms while the index scan is 5.6ms.

Predicting how far the inner scan needs to go would be quite
difficult, and I don't know how the system will do it.

However, when I create and populate simple tables based on your
description, I get the index scan being the lower estimated cost. So
the tables I built are not sufficient to study the matter in detail.

Cheers,

Jeff