From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Dave E Martin <postgresql-to(dot)dave(at)dave(dot)to> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: enable_sort optimization problem |
Date: | 2005-05-26 07:12:27 |
Message-ID: | 429576DB.304@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Surabhi Ahuja | 2005-05-26 08:06:01 | bulk loading of bplus index tree |
Previous Message | Dave E Martin | 2005-05-26 06:33:20 | enable_sort optimization problem |