From: Rosser Schwarz <rosser.schwarz@gmail.com>
To: Derek Buttineau|Compu-SOLVE <derek@csolve.net>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] ORDER BY Optimization
Date: Fri, 6 May 2005 15:35:30 -0500
Message-ID: <37d451f7050506133534923deb@mail.gmail.com> (raw)
In-Reply-To: <427BCA10.4090208@csolve.net>
References: <427BCA10.4090208@csolve.net>
while you weren't looking, Derek Buttineau|Compu-SOLVE wrote:
> I'm hoping this is the right place to send this.
The PostgreSQL Performance list, pgsql-performance@postgresql.org
would be more appropriate. I'm copying my followup there, as well.
As for your query, almost all the time is actually spent in the
nestloop, not the sort. Compare:
> -> Sort (cost=31402.85..31405.06 rows=886 width=306) (actual
> time=87454.187..87454.240 rows=10 loops=1)
vs.
> -> Nested Loop (cost=0.00..31359.47 rows=886 width=306)
> (actual time=4.740..86430.468 rows=26308 loops=1)
That's 50-ish ms versus 80-odd seconds.
It seems to me a merge join might be more appropriate here than a
nestloop. What's your work_mem set at? Off-the-cuff numbers show the
dataset weighing in the sub-ten mbyte range.
Provided it's not already at least that big, and you don't want to up
it permanently, try saying:
SET work_mem = 10240; -- 10 mbytes
immediately before running this query (uncached, of course) and see
what happens.
Also, your row-count estimates look pretty off-base. When were these
tables last VACUUMed or ANALYZEd?
/rls
--
:wq
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rosser.schwarz@gmail.com, derek@csolve.net, pgsql-performance@postgresql.org
Subject: Re: [SQL] ORDER BY Optimization
In-Reply-To: <37d451f7050506133534923deb@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox