pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedindex and min()
2+ messages / 2 participants
[nested] [flat]
* index and min()
@ 2003-10-26 20:01 sarek@ozaba.cx
2003-10-26 20:52 ` Re: index and min() Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 2+ messages in thread
From: sarek@ozaba.cx @ 2003-10-26 20:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: index and min()
2003-10-26 20:01 index and min() sarek@ozaba.cx
@ 2003-10-26 20:52 ` Christopher Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 2+ messages in thread
From: Christopher Browne @ 2003-10-26 20:52 UTC (permalink / raw)
To: pgsql-sql
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"
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-10-26 20:52 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-26 20:01 index and min() sarek@ozaba.cx
2003-10-26 20:52 ` Christopher Browne <cbbrowne@acm.org>
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