Обсуждение: 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@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 >
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 >> >
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@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.html
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