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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
