pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query when using ORDER BY *and* LIMIT
6+ messages / 4 participants
[nested] [flat]
* Slow query when using ORDER BY *and* LIMIT
@ 2011-07-06 00:18 Jonathan <jonathan@kc8onw.net>
0 siblings, 2 replies; 6+ messages in thread
From: Jonathan @ 2011-07-06 00:18 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow query when using ORDER BY *and* LIMIT
@ 2011-07-08 22:23 Jonathan <jonathan@kc8onw.net>
parent: Jonathan <jonathan@kc8onw.net>
1 sibling, 1 reply; 6+ messages in thread
From: Jonathan @ 2011-07-08 22:23 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow query when using ORDER BY *and* LIMIT
@ 2011-07-09 03:39 Pavel Stehule <pavel.stehule@gmail.com>
parent: Jonathan <jonathan@kc8onw.net>
0 siblings, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2011-07-09 03:39 UTC (permalink / raw)
To: Jonathan <jonathan@kc8onw.net>; +Cc: pgsql-performance
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@kc8onw.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow query when using ORDER BY *and* LIMIT
@ 2011-07-09 03:49 Pavel Stehule <pavel.stehule@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2011-07-09 03:49 UTC (permalink / raw)
To: Jonathan <jonathan@kc8onw.net>; +Cc: 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.stehule@gmail.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@kc8onw.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow query when using ORDER BY *and* LIMIT
@ 2011-10-13 19:34 davidsarmstrong <dsatemp-1@yahoo.com>
parent: Jonathan <jonathan@kc8onw.net>
1 sibling, 1 reply; 6+ messages in thread
From: davidsarmstrong @ 2011-10-13 19:34 UTC (permalink / raw)
To: 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.h...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow query when using ORDER BY *and* LIMIT
@ 2011-10-15 21:09 Michael Viscuso <michael.viscuso@getcarbonblack.com>
parent: davidsarmstrong <dsatemp-1@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Viscuso @ 2011-10-15 21:09 UTC (permalink / raw)
To: davidsarmstrong <dsatemp-1@yahoo.com>; +Cc: pgsql-performance
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@yahoo.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.h...
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-10-15 21:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-06 00:18 Slow query when using ORDER BY *and* LIMIT Jonathan <jonathan@kc8onw.net>
2011-07-08 22:23 ` Jonathan <jonathan@kc8onw.net>
2011-07-09 03:39 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-07-09 03:49 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-10-13 19:34 ` davidsarmstrong <dsatemp-1@yahoo.com>
2011-10-15 21:09 ` Michael Viscuso <michael.viscuso@getcarbonblack.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox