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