On Tue, 2007-11-06 at 14:39 -0300, André Volpato wrote:
Remember that you can always use serial fields to count a table, like:
alter table foo add id serial;
select id from foo order by id desc limit 1;
This should return the same value than count(*), in a few msecs.
--
ACV
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
not so...
test=# select version();
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.1)
(1 row)
test=# create table serialcount(aval integer);
CREATE TABLE
test=# \timing
Timing is on.
test=# insert into serialcount values ( generate_series(1,10000000));
INSERT 0 10000000
Time: 42297.468 ms
test=# select count(*) from serialcount;
count
----------
10000000
(1 row)
Time: 6158.188 ms
test=# select count(*) from serialcount;
count
----------
10000000
(1 row)
Time: 2366.596 ms
test=# select count(*) from serialcount;
count
----------
10000000
(1 row)
Time: 2090.416 ms
test=# select count(*) from serialcount;
count
----------
10000000
(1 row)
Time: 2125.377 ms
test=# select count(*) from serialcount;
count
----------
10000000
(1 row)
Time: 2122.584 ms
test=# alter table serialcount add id serial;
NOTICE: ALTER TABLE will create implicit sequence "serialcount_id_seq"
for serial column "serialcount.id"
ALTER TABLE
Time: 51733.139 ms
test=# select id from serialcount order by id desc limit 1;
id
----------
10000000
(1 row)
Time: 41088.062 ms
test=# select id from serialcount order by id desc limit 1;
id
----------
10000000
(1 row)
Time: 35638.317 ms
test=# vacuum analyze serialcount;
VACUUM
Time: 927.760 ms
test=# select id from serialcount order by id desc limit 1;
id
----------
10000000
(1 row)
Time: 34281.178 ms