Message-ID: <456423B3.7010505@logix-tt.com>
Date: Wed, 22 Nov 2006 11:17:23 +0100
From: Markus Schaber <schabi@logix-tt.com>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Thunderbird 1.5.0.7 (X11/20060927)
MIME-Version: 1.0
To: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: PostgreSQL underestimates sorting
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

Hi,

PostgreSQL 8.1 (and, back then, 7.4) have the tendency to underestimate
the costs of sort operations, compared to index scans.

The Backend allocates gigs of memory (we've set sort_mem to 1 gig), and
then starts spilling out more Gigs of temporary data to the disk. So the
execution gets - in the end - much slower compared to an index scan, and
wastes lots of disk space.

We did not manage to tune the config values appropriately, at least not
without causing other query plans to suffer badly.

Are there some nice ideas how to shift the planners preferences slightly
towards index scans, without affecting other queries?

There's one thing that most of those queries have in common: They
include TOAST data (large strings, PostGIS geometries etc.), and I
remember that there are known problems with estimating the TOAST costs.
This may be part of the problem, or may be irrelevant.


Thanks,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org