Re: Postgresql simple query performance question - Mailing list pgsql-general

Reid Thompson escreveu: <blockquote cite="mid:1194369123.15405.4.camel@raker.ateb.com" type="cite"><pre wrap="">On
Tue,2007-11-06 at 14:39 -0300, André Volpato wrote: 
 </pre><blockquote type="cite"><pre wrap="">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 <a
class="moz-txt-link-abbreviated"href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a> so that your
messagecan get through to the mailing list cleanly   </pre></blockquote><pre wrap=""> 

not so...

test=# select version();

version

----------------------------------------------------------------------------------------------------------------PostgreSQL
8.2.4on 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 </pre></blockquote><br /> I meant to select using an index. I´ve done the same tests here, and
realizedthat my server is two times slower than yours:<br /><br /> testeprog=# select version();<br />
                                                version<br />
---------------------------------------------------------------------------------------------------------<br/>
 PostgreSQL8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)<br /> (1
row)<br/><br /> testeprog=# select count(*) from test;<br />   count<br /> ----------<br />  10000000<br /> (1 row)<br
/><br/> Time: 4116.613 ms<br /><br /> testeprog=# alter table test add id serial;<br /> NOTICE:  ALTER TABLE will
createimplicit sequence "test_id_seq" for serial column "test.id"<br /> ALTER TABLE<br /> Time: 90617.195 ms<br /><br
/>testeprog=# select id from test order by id desc limit 1;<br />     id<br /> ----------<br />  10000000<br /> (1
row)<br/><br /> Time: 64856.553 ms<br /><br /> testeprog=# create unique index itest1 on test using btree (id);<br />
CREATEINDEX<br /> Time: 29026.891 ms<br /><br /><br /> testeprog=# explain analyze select id from test order by id desc
limit1;<br />                                                                QUERY PLAN<br />
----------------------------------------------------------------------------------------------------------------------------------------<br
/> Limit  (cost=0.00..0.02 rows=1 width=4) (actual time=0.017..0.019 rows=1 loops=1)<br />    ->  Index Scan
Backwardusing itest1 on test  (cost=0.00..185954.00 rows=10000000 width=4) (actual time=0.014..0.014 rows=1 loops=1)<br
/> Total runtime: 0.059 ms<br /> (3 rows)<br /><br /><br /><br /> @Bill:<br /> Bill Moran wrote <blockquote
cite="mid:20071106115927.2aea950e.wmoran@potentialtech.com"type="cite"><pre wrap=""> 
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. </pre></blockquote><br /> You are right, the serial hack should not work in most oltp cases.<br /><br /> --<br />
ACV<br/><br />