| From: | André Volpato <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br> |
|---|---|
| To: | Reid(dot)Thompson(at)ateb(dot)com |
| Cc: | pgsql-general(at)postgresql(dot)org, wmoran(at)potentialtech(dot)com |
| Subject: | Re: Postgresql simple query performance question |
| Date: | 2007-11-07 12:08:46 |
| Message-ID: | 4731AACE.6070908@ecomtecnologia.com.br |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Reid Thompson escreveu:
<blockquote cite="mid:1194369123(dot)15405(dot)4(dot)camel(at)raker(dot)ateb(dot)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(at)postgresql(dot)org">majordomo(at)postgresql(dot)org</a> so that your
message can get through to the mailing list cleanly
</pre>
</blockquote>
<pre wrap=""><!---->
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
</pre>
</blockquote>
<br>
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:<br>
<br>
testeprog=# select version();<br>
version<br>
---------------------------------------------------------------------------------------------------------<br>
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)<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 create implicit 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>
CREATE INDEX<br>
Time: 29026.891 ms<br>
<br>
<br>
testeprog=# explain analyze select id from test order by id desc limit
1;<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 Backward using 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(dot)2aea950e(dot)wmoran(at)potentialtech(dot)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>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 4.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | rihad | 2007-11-07 12:12:24 | Re: prepared statements suboptimal? |
| Previous Message | Simon Riggs | 2007-11-07 11:54:09 | Re: Exclusive Locks Taken on User Tables? |