RE: slow SELECT ... LIMIT query

Thanks for the response...

here's the explain for the two queries...

with limit
postgres=# explain select * from myTable where unitid = 2 order by
datetimestamp desc limit 1;
NOTICE: QUERY PLAN:

Limit (cost=0.00..29.33 rows=1 width=32)
-> Index Scan Backward using datetimestamp_idx on myTable
(cost=0.00..159464.46 rows=5436 width=32)

EXPLAIN

and without limit...
postgres=# explain select * from myTable where unitid = 2 order by
datetimestamp desc;
NOTICE: QUERY PLAN:

Sort (cost=15977.53..15977.53 rows=5436 width=32)
-> Index Scan using unitid_idx on myTable (cost=0.00..15640.27 rows=5436
width=32)

hmmm, looks like either using the datetimestamp index or doing the backward
scan could be messing it up.

tried
select * from (select * from myTable where unitid = 2 order by datetimestamp
desc) a limit 1;

and it's super fast. the explain for this one is...
Limit (cost=15977.53..15977.53 rows=1 width=32)
-> Subquery Scan a (cost=15977.53..15977.53 rows=5436 width=32)
-> Sort (cost=15977.53..15977.53 rows=5436 width=32)
-> Index Scan using unitid_idx on locationevent
(cost=0.00..15640.27 rows=5436 width=32

EXPLAIN

can anyone tell me what slowed down the first query?

thanks,
simon

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, July 18, 2001 2:44 PM
To: Simon Stanlake
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] slow SELECT ... LIMIT query

What query plans are you getting for these various combinations?

regards, tom lane