Grokbase › Groups › PostgreSQL › pgsql-performance › July 2011
FAQ
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

Search Discussions

  • Jonathan at Jul 8, 2011 at 10:24 pm ⇧
    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.
  • Pavel Stehule at Jul 9, 2011 at 3:40 am ⇧
    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 <[email protected]>:
    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 ([email protected])
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-performance
  • Pavel Stehule at Jul 9, 2011 at 3:50 am ⇧
    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 <[email protected]>:
    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 <[email protected]>:
    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 ([email protected])
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-performance
  • Davidsarmstrong at Oct 13, 2011 at 7:51 pm ⇧
    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.
  • Michael Viscuso at Oct 15, 2011 at 10:17 pm ⇧
    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 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 ([email protected])
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-performance

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJul 6, '11 at 12:27a
activeOct 15, '11 at 10:17p
posts6
users4
websitepostgresql.org
irc#postgresql

4 users in discussion

Pavel Stehule: 2 posts Jonathan: 2 posts Davidsarmstrong: 1 post Michael Viscuso: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase