From: Derek Buttineau|Compu-SOLVE <derek@csolve.net>
To: Rosser Schwarz <rosser.schwarz@gmail.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] ORDER BY Optimization
Date: Fri, 06 May 2005 16:54:57 -0400
Message-ID: <427BD9A1.3090901@csolve.net> (raw)
In-Reply-To: <37d451f7050506133534923deb@mail.gmail.com>
References: <427BCA10.4090208@csolve.net>
<37d451f7050506133534923deb@mail.gmail.com>
Thanks for the response :)
>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
>
>
It's currently set at 16mb, I've also tried upping sort_mem as well
without any noticible impact on the uncached query. :(
>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?
>
>
I'm not entirely sure what's up with the row-count estimates, the tables
are updated quite frequently (and VACUUM is also run quite frequently),
however I had just run a VACUUM ANALYZE on both databases before running
the explain.
I'm also still baffled at the differences in the plans between the two
servers, on the one that uses the index to sort, I get for comparison a
nestloop of:
Nested Loop (cost=0.00..1175943.99 rows=1814 width=311) (actual
time=25.337..26.867 rows=10 loops=1)
The plan that the "live" server seems to be using seems fairly inefficient.
Derek
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: derek@csolve.net, rosser.schwarz@gmail.com, pgsql-performance@postgresql.org
Subject: Re: [SQL] ORDER BY Optimization
In-Reply-To: <427BD9A1.3090901@csolve.net>
* 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