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@sss.pgh.pa.us]
Sent: Wednesday, July 18, 2001 2:44 PM
To: Simon Stanlake
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] slow SELECT ... LIMIT query


What query plans are you getting for these various combinations?

            regards, tom lane