From: Jonathan <jonathan@kc8onw.net>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query when using ORDER BY *and* LIMIT
Date: Fri, 08 Jul 2011 18:23:41 -0400
Message-ID: <4E17836D.9070201@kc8onw.net> (raw)
In-Reply-To: <4E13A9C2.9000906@kc8onw.net>
References: <4E13A9C2.9000906@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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jonathan@kc8onw.net
Subject: Re: Slow query when using ORDER BY *and* LIMIT
In-Reply-To: <4E17836D.9070201@kc8onw.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox