On Tue, Apr 26, 2011 at 4:37 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Sok Ann Yap <sokann@gmail.com> wrote:
>
>> So, index scan wins by a very small margin over sequential scan
>> after the tuning. I am a bit puzzled because index scan is more
>> than 3000 times faster in this case, but the estimated costs are
>> about the same. Did I do something wrong?
>
> Tuning is generally needed to get best performance from PostgreSQL.
> Needing to reduce random_page_cost is not unusual in situations
> where a good portion of the active data is in cache (between
> shared_buffers and the OS cache). Please show us your overall
> configuration and give a description of the hardware (how many of
> what kind of cores, how much RAM, what sort of storage system). The
> configuration part can be obtained by running the query on this page
> and pasting the result into your next post:
>
> http://wiki.postgresql.org/wiki/Server_Configuration
>
> There are probably some other configuration adjustments you could do
> to ensure that good plans are chosen.
The very first thing to check is effective_cache_size and to set it to
a reasonable value.
merlin