From: Shane Ambler <pgsql@Sheeky.Biz>
To: Chris Shoemaker <chris.shoemaker@cox.net>
Cc: pgsql-performance@postgresql.org
Cc: ejones@engineyard.com
Subject: Re: Adding "LIMIT 1" kills performance.
Date: Fri, 30 May 2008 02:23:46 +0930
Message-ID: <483EDF9A.20100@Sheeky.Biz> (raw)
In-Reply-To: <20080529154734.GA18273@pe.Belkin>
References: <20080529154734.GA18273@pe.Belkin>
Chris Shoemaker wrote:
> [Attn list-queue maintainers: Please drop the earlier version
> of this email that I accidentally sent from an unsubscribed address. ]
>
> Hi,
>
> I'm having a strange problem with a slow-running select query. The
> query I use in production ends in "LIMIT 1", and it runs very slowly.
> But when I remove the "LIMIT 1", the query runs quite quickly. This
> behavior has stumped a couple smart DBAs.
>
> Can anyone explain why such a slow plan is chosen when the "LIMIT 1"
> is present? Is there anything I can do to speed this query up?
> Thanks.
>
From what I know using an ORDER BY and a LIMIT can often prevent
*shortening* the query as it still needs to find all rows to perform the
order by before it limits.
The difference in plans eludes me.
> production=> EXPLAIN ANALYZE SELECT event_updates.*
> FROM event_updates
> INNER JOIN calendars ON event_updates.feed_id = calendars.id
> INNER JOIN calendar_links ON calendars.id = calendar_links.source_tracker_id
> WHERE (calendar_links.calendar_group_id = 3640)
> ORDER BY event_updates.id DESC
> LIMIT 1;
Does removing the DESC from the order by give the same variation in
plans? Or is this only when using ORDER BY ... DESC LIMIT 1?
One thing that interests me is try -
EXPLAIN ANALYZE SELECT * FROM (
SELECT event_updates.*
FROM event_updates
INNER JOIN calendars ON event_updates.feed_id = calendars.id
INNER JOIN calendar_links ON calendars.id = calendar_links.source_tracker_id
WHERE (calendar_links.calendar_group_id = 3640)
ORDER BY event_updates.id DESC
) AS foo
LIMIT 1;
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
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: pgsql@Sheeky.Biz, chris.shoemaker@cox.net, ejones@engineyard.com
Subject: Re: Adding "LIMIT 1" kills performance.
In-Reply-To: <483EDF9A.20100@Sheeky.Biz>
* 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