From: sarek@ozaba.cx
To: pgsql-sql@postgresql.org
Subject: index and min()
Date: Sun, 26 Oct 2003 21:01:42 +0100
Message-ID: <cone.1067198502.760123.11582.100@ratbert.ozaba.cx> (raw)
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;
QUERY PLAN
----------------------------------------------------------------------------
Aggregate (cost=55162.88..55162.88 rows=1 width=8)
(actual time=11798.27..11798.27 rows=1 loops=1)
-> Seq Scan on accounting (cost=0.00..50711.70 rows=1780470 width=8)
(actual time=0.14..8703.67 rows=1780470 loops=1)
Total runtime: 11798.39 msec
(3 rows)
Time: 11799.69 ms
Any help is greatly appreciated.
Best Regards
Magnus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sarek@ozaba.cx
Subject: Re: index and min()
In-Reply-To: <cone.1067198502.760123.11582.100@ratbert.ozaba.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox