From: | Jonathan <jonathan(at)kc8onw(dot)net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Slow query when using ORDER BY *and* LIMIT |
Date: | 2011-07-06 00:18:10 |
Message-ID: | 4E13A9C2.9000906@kc8onw.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Clem Dickey | 2011-07-06 02:26:18 | GROUP BY with reasonable timings in PLAN but unreasonable execution time |
Previous Message | Matthias Howell | 2011-07-05 20:50:22 | Query in 9.0.2 not using index in 9.0.0 works fine |