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

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
>