Re: Massive slowdown when LIMIT applied

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Graeme Hinchliffe <graeme(dot)hinchliffe(at)zeninternet(dot)co(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Massive slowdown when LIMIT applied
Date: 2006-08-16 14:23:01
Message-ID: 19785.1155738181@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Graeme Hinchliffe <graeme(dot)hinchliffe(at)zeninternet(dot)co(dot)uk> writes:
> If I do the following:

> SELECT username,acctstarttime FROM radacct WHERE username='user'
> ORDER BY acctstarttime;

> again the results come up slightly slower as would be expected but
> still very quickly.. (a few hundred rows in this case)

> Now, if I do the following:

> SELECT username,acctstarttime FROM radacct WHERE username='user'
> ORDER BY acctstarttime LIMIT 50;

> I have to wait a very long time, IE instead of 2-3 seconds.. can be
> as slow as 10+ minutes.

Have you compared EXPLAIN ANALYZE outputs for the two cases? Presumably
the planner is picking a different plan for the LIMIT case (and guessing
wrong :-()

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message John R. Allgood 2006-08-16 14:24:35 Determine Transactions Per Second
Previous Message Alvaro Herrera 2006-08-16 14:22:13 Re: Timezones -- what comes out does not go in?