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

On Wed, Apr 27, 2011 at 5:37 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Sok Ann Yap <sokann(at)gmail(dot)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.
>
> -Kevin
>

Here's the configuration (this is just a low end laptop):

name |
current_setting
----------------------------+-------------------------------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.0.4 on x86_64-pc-linux-gnu,
compiled by GCC x86_64-pc-linux-gnu-gcc (Gentoo 4.5.2 p1.0, pie-0.4.5)
4.5.2, 64-bit
checkpoint_segments | 16
default_statistics_target | 10000
effective_cache_size | 512MB
lc_collate | en_US.UTF-8
lc_ctype | en_US.UTF-8
listen_addresses | *
log_destination | syslog
log_min_duration_statement | 0
maintenance_work_mem | 256MB
max_connections | 100
max_stack_depth | 2MB
port | 5432
random_page_cost | 4
server_encoding | UTF8
shared_buffers | 256MB
silent_mode | on
TimeZone | Asia/Kuala_Lumpur
wal_buffers | 1MB
work_mem | 32MB
(20 rows)

The thing is, the query I posted was fairly simple (I think), and
PostgreSQL should be able to choose the 3000+ times faster index scan
with the default random_page_cost of 4. If I need to reduce it to 2
when using a 5.4k rpm slow disk, what is random_page_cost = 4 good
for?

(Sorry for the double message, I forgot to CC the list in the first one)