Re: enable_sort optimization problem

Dave E Martin wrote:
> (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