Re: [GENERAL] Postgresql simple query performance question

[André Volpato]

Reid Thompson escreveu: 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 [EMAIL PROTECTED] 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 I meant to select using an index. I´ve done the same tests here, and realized that my server is two times slower than yours: testeprog=# select version();                                                  version ---------------------------------------------------------------------------------------------------------  PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7) (1 row) testeprog=# select count(*) from test;   count ----------  10000000 (1 row) Time: 4116.613 ms testeprog=# alter table test add id serial; NOTICE:  ALTER TABLE will create implicit sequence "test_id_seq" for serial column "test.id" ALTER TABLE Time: 90617.195 ms testeprog=# select id from test order by id desc limit 1;     id ----------  10000000 (1 row) Time: 64856.553 ms testeprog=# create unique index itest1 on test using btree (id); CREATE INDEX Time: 29026.891 ms testeprog=# explain analyze select id from test order by id desc limit 1;                                                                QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------  Limit  (cost=0.00..0.02 rows=1 width=4) (actual time=0.017..0.019 rows=1 loops=1)    ->  Index Scan Backward using itest1 on test  (cost=0.00..185954.00 rows=10000000 width=4) (actual time=0.014..0.014 rows=1 loops=1)  Total runtime: 0.059 ms (3 rows) @Bill: Bill Moran wrote I don't think so. What kind of accuracy do you have when rows are deleted? Also, sequences are not transactional, so rolled-back transactions will increment the sequence without actually adding rows. You are right, the serial hack should not work in most oltp cases. -- ACV