Slow query when using ORDER BY *and* LIMIT

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

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.

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
>

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
>>
>

I'm running into the same problem. I removed the limit and it was fine. I
guess I could have removed the order by as well but it doesn't help if you
really need both.

Have you found any more information on this?

Thanks!

Dave (Armstrong)

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-when-using-ORDER-BY-and-LIMIT-tp4555260p4900348.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Dave,

Since I control the application that was performing the query and I've
separated my data into daily partitioned tables (which enforced my order by
clause on a macro-level), I took Stephen's advice and implemented the nested
loop over each daily table from within the application versus having
Postgres figure it out for me. Sorry I don't have a better answer for you.

Mike

On Thu, Oct 13, 2011 at 3:34 PM, davidsarmstrong <dsatemp-1(at)yahoo(dot)com>wrote:

> I'm running into the same problem. I removed the limit and it was fine. I
> guess I could have removed the order by as well but it doesn't help if you
> really need both.
>
> Have you found any more information on this?
>
> Thanks!
>
> Dave (Armstrong)
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Slow-query-when-using-ORDER-BY-and-LIMIT-tp4555260p4900348.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
> --
> 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
>