Message-ID: <427BD9A1.3090901@csolve.net>
Date: Fri, 06 May 2005 16:54:57 -0400
From: Derek Buttineau|Compu-SOLVE <derek@csolve.net>
Organization: Compu-SOLVE Internet Services
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050328)
MIME-Version: 1.0
To: Rosser Schwarz <rosser.schwarz@gmail.com>
Cc: pgsql-sql@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: [SQL] ORDER BY Optimization
References: <427BCA10.4090208@csolve.net>
	<37d451f7050506133534923deb@mail.gmail.com>
In-Reply-To: <37d451f7050506133534923deb@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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