Slow query when using ORDER BY *and* LIMIT - Mailing list pgsql-performance

From Jonathan
Subject Slow query when using ORDER BY *and* LIMIT
Date
Msg-id 4E13A9C2.9000906@kc8onw.net
Whole thread Raw
Responses Re: Slow query when using ORDER BY *and* LIMIT  (Jonathan <jonathan@kc8onw.net>)
Re: Slow query when using ORDER BY *and* LIMIT  (davidsarmstrong <dsatemp-1@yahoo.com>)
List 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 by date:

Previous
From: Greg Smith
Date:
Subject: Re: bitmask index
Next
From: "Matthias Howell"
Date:
Subject: Query in 9.0.2 not using index in 9.0.0 works fine