Skip site navigation (1) Skip section navigation (2)

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

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <sokann(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
Date: 2011-04-27 12:40:58
Message-ID: 4DB7C88A020000250003CF10@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Sok Ann Yap  wrote:
> Kevin Grittner  wrote:
 
>> 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).
 
> Here's the configuration (this is just a low end laptop):
 
> 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
 
Usually overkill.  If this didn't help, you should probably change it
back.
 
> 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
 
You probably don't need this many connections.
 
> 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)
 
It's hard to recommend other changes without knowing the RAM on the
system.  How many of what kind of CPUs would help, too.
 
> 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.
 
It picks the plan with the lowest estimated cost.  If it's not
picking the best plan, that's usually an indication that you need to
adjust cost factors so that estimates better model the actual costs.
 
> If I need to reduce it to 2 when using a 5.4k rpm slow disk, what
> is random_page_cost = 4 good for?
 
It's good for large databases with a lot of physical disk I/O.  In
fact, in some of those cases, it needs to be higher.  In your test,
the numbers indicate that everything was cached in RAM.  That makes
the effective cost very low.
 
Also, the odds are that you have more total cache space between the
shared_buffers and the OS cache than the effective_cache_size
setting, so the optimizer doesn't expect the number of cache hits
you're getting on index usage.
 
-Kevin

Responses

pgsql-performance by date

Next:From: Dhimant PatelDate: 2011-04-27 15:28:19
Subject: Query Performance with Indexes on Integer type vs. Date type.
Previous:From: Claudio FreireDate: 2011-04-27 07:23:32
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group