Postgres Pro
Downloads
Home   >   mailing lists

Thread: Massive slowdown when LIMIT applied

Massive slowdown when LIMIT applied

From
Graeme Hinchliffe
Date:
Hi,
I am using postgres 7.4.7 under Debian Sarge for RADACCT, this means a fairly large table of data allowing us to hold approx 12 months of data.  I have used a trigger to break the system in 2 and keep the live table which takes updates small, and all closed connections are inserted into a large historical table.  The histrorical table is only ever inserted into and queried.

It is indexed on all mayor attributes for which it may be searched or ordered.

The problem is this:

If I run the query:

SELECT username,acctstarttime FROM radacct WHERE username='user';

The results come back nice and quick (indexes are are username, and acctstarttime BTW)

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.

this seems very odd to me, any ideas?

Thanks in advance.


-----
Graeme Hinchliffe (BSc)
Core Systems Designer
Zen Internet (http://www.zen.co.uk/)

Direct: 0845 058 9074
Main  : 0845 058 9000
Fax   : 0845 058 9005

Re: Massive slowdown when LIMIT applied

From
Tom Lane
Date:
Graeme Hinchliffe <graeme.hinchliffe@zeninternet.co.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

Re: Massive slowdown when LIMIT applied

From
"macgillivary"
Date:
Purely out of curiosity, what sort of execution time do you get if you
use:

SELECT *
FROM (SELECT username,acctstarttime FROM radacct WHERE username='user'

ORDER BY acctstarttime ) allstarttimes
LIMIT 50;


Graeme Hinchliffe wrote:
>
> 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.