Massive slowdown when LIMIT applied - Mailing list pgsql-general

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.