Message-ID: <4E13A9C2.9000906@kc8onw.net>
Date: Tue, 05 Jul 2011 20:18:10 -0400
From: Jonathan <jonathan@kc8onw.net>
User-Agent: Mozilla/5.0 (Windows NT 6.0; WOW64;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Slow query when using ORDER BY *and* LIMIT
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I have a query that uses ORDER BY and LIMIT to get a set of image data 
rows that match a given tag.  When both ORDER BY and LIMIT are included 
for some reason the planner chooses a very slow query plan.  Dropping 
one or the other results in a much faster query going from 4+ seconds -> 
30 ms.  Database schema, EXPLAIN ANALYZE and other information can be 
found at http://pgsql.privatepaste.com/31113c27bf  Is there a way to 
convince the planner to use the faster plan when doing both ORDER BY and 
LIMIT without using SET options or will I need to disable the slow plan 
options to force the planner to use the fast plan?

I found some stuff in the mailing list archives that looks related but I 
didn't see any fixes.  Apparently the planner hopes the merge join will 
find the LIMIT # of rows fairly quickly but instead it winds up scanning 
almost the entire table.

Thanks,
Jonathan