On Wed, Apr 27, 2011 at 5:37 AM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote: > Sok Ann Yap <sokann@xxxxxxxxx> 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) -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
