Slow query when using ORDER BY *and* LIMIT

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

Responses

Browse pgsql-performance by date

  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