Re: Postgresql simple query performance question

[André Volpato <andre.volpato@xxxxxxxxxxxxxxxxxxxxx>]

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 majordomo@xxxxxxxxxxxxxx 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