On Thu, Apr 28, 2011 at 7:23 AM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote: > Sok Ann Yap <sokann@xxxxxxxxx> wrote: > >> Anyway, the overhead of spawning 44 extra queries means that it is >> still better off for me to stick with the original query and tune >> PostgreSQL to choose index scan. > > Maybe, but what is *best* for you is to tune PostgreSQL so that your > costs are accurately modeled, at which point it will automatically > pick the best plan for most or all of your queries without you > needing to worry about it. > > If you set your effective_cache_size to the sum of shared_buffers > and what your OS reports as cache after you've been running a while, > that will help the optimizer know what size index fits in RAM, and > will tend to encourage index use. ÂIf the active portion of your > data is heavily cached, you might want to set random_page_cost and > seq_page_cost to the same value, and make that value somewhere in > the 0.1 to 0.05 range. ÂIf you have moderate caching, using 1 and 2 > can be good. > > If you're still not getting reasonable plans, please post again with > more information about your hardware along with the query and its > EXPLAIN ANALYZE output. > > -Kevin > I understand the need to tune PostgreSQL properly for my use case. What I am curious about is, for the data set I have, under what circumstances (hardware/workload/cache status/etc) would a sequential scan really be faster than an index scan for that particular query? To simulate a scenario when nothing is cached, I stopped PostgreSQL, dropped all system cache (sync; echo 3 > /proc/sys/vm/drop_caches), restarted PostgreSQL, and ran the query. A sequential scan run took 13.70 seconds, while an index scan run took 0.34 seconds, which is still 40 times faster. Also, I tried increasing effective_cache_size from 512MB to 3GB (the database size is 2+GB), and it still favor sequential scan. The estimated costs did not change at all. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
