Re: Adding "LIMIT 1" kills performance.

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