Thread: index and min()

Hello,

I have a table that contains logs from a radius accounting server.
Currently the table contains 1,780,470 rows and is about 350Mb on the disk.

It is running on a mssql server at the moment but I would like to move it 
over to a postgresql server and postgresql seems to be somewhat fast than 
mssql on most things except one.

SELECT min(inserttime) FROM acc_pb; which takes about 11 seconds to 
complete.

On the mssql server this takes less than 1 second.

On the mssql server the inserttime column has a cluster index which if I 
understand correctly means that the index is ordered which would explain why 
the min() and max() functions are fast.

Is there an ordered index on postgresql or is the problem something else?

I have a b-tree index on the inserttime column in postgresql but it isn't 
used somehow.

test=# EXPLAIN ANALYZE SELECT min(inserttime) from accounting;
QUERYPLAN                                                        
 
---------------------------------------------------------------------------- 
Aggregate  (cost=55162.88..55162.88 rows=1 width=8)          (actual time=11798.27..11798.27 rows=1 loops=1)     ->
SeqScan on accounting  (cost=0.00..50711.70 rows=1780470 width=8)                           (actual time=0.14..8703.67
rows=1780470loops=1)
 
Total runtime: 11798.39 msec
(3 rows)

Time: 11799.69 ms

Any help is greatly appreciated.

Best Regards
Magnus

In an attempt to throw the authorities off his trail, sarek@ozaba.cx transmitted:
> SELECT min(inserttime) FROM acc_pb; which takes about 11 seconds to
> complete.
>
> On the mssql server this takes less than 1 second.

If you have NO index on inserttime, then the best that can be done is
the sequential scan that you saw.

If there is an index on inserttime, then the PostgreSQL idiom that
provides a _massive_ speedup is the query:
select inserttime from acc_pb order by inserttime limit 1;

In theory, it ought to be a nifty idea to have a way of automatically
transforming the min(inserttime) query into what I showed, but that is
likely to be difficult to do in general, and nobody has yet proposed
an implementation, so you'll have to do that yourself.
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/x.html
"Linux  and  other  OSS  advocates  are making  a  progressively  more
credible argument  that OSS software is  at least as robust  -- if not
more  -- than  commercial  alternatives." -  Microsoft lamenting  Open
Source Software in the "Halloween Document"