(8.0.1 on debian/linux 2.6.11 kernel)
I have noticed that if I set enable_sort=false in the .conf file, my queries are running faster. I had a query which if I did a limit 20, ran in 6 milliseconds, but if I changed it to limit 21, it took around 19 seconds (or 19000 milliseconds). It also took longer if I did limit 19 offset 2. (I don't know what it is about the 21st record). In any case, I noticed that in the analysis, the long version was doing a sort and the quick version was not, so I tried the enable_sort=false setting, and now things are generally running faster.
I HAVE done analyzes, and vacuums, and vacuum analyzes. In prior experimenting with this, there were even some seq_scans, which turned into index_scans when I set enable_seqscan=false, and became moderately faster.
This sort of thing is useful as a way of testing whether a better plan exists. It's not terribly good as a way of tuning a live system.
I am using 8.0.1, and below are the two query plans, first the enable_sort=true version, then the enable_sort=false version, note the VAST difference in speed. What is the problem, and how can I convince the query optimizer to do the right thing (short of enable_sort=false)?
from the config file: # - Planner Cost Constants -
#effective_cache_size = 1000 # typically 8KB each #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same)
You should probably start with the performance-tuning articles here: http://www.powerpostgresql.com/PerfList http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
Certainly your effective-cache-size is (hopefully) too low, and random-page-cost might well be so too.
If sorts in particular seem slow, you might want to increase work_mem (called "sort_mem" in older releases). BUT make changes one step at a time and look at the total impact on the system, otherwise you can end up making one query fast and nine slow.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
