Re: Slow query when using ORDER BY *and* LIMIT

Hello

sorry, I didn't see a link on privatepastebin

There is problem in LIMIT, because query without LIMIT returns only a
few lines more than query with LIMIT. You can try to materialize query
without LIMIT and then to use LIMIT like

SELECT * FROM (your query without limit OFFSET 0) x LIMIT 30;

Regards

Pavel Stehule

2011/7/9 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello
>
> Is impossible to help you without more detailed info about your problems,
>
> we have to see a execution plan, we have to see slow query
>
> Regards
>
> Pavel Stehule
>
> 2011/7/9 Jonathan <jonathan(at)kc8onw(dot)net>:
>> Does anyone have any suggestions for my problem?  (I have to wonder if I'm
>> somehow just not getting peoples attention or what. This is my second
>> question this week on a public mailing list that has gotten exactly 0
>> replies)
>>
>> Jonathan
>>
>> On 7/5/2011 8:18 PM, Jonathan wrote:
>>>
>>> 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.
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>