Slow query when using ORDER BY *and* LIMIT
От | Jonathan |
---|---|
Тема | Slow query when using ORDER BY *and* LIMIT |
Дата | |
Msg-id | 4E13A9C2.9000906@kc8onw.net обсуждение исходный текст |
Ответы |
Re: Slow query when using ORDER BY *and* LIMIT
Re: Slow query when using ORDER BY *and* LIMIT |
Список | 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
В списке pgsql-performance по дате отправления: