Ivan Sergio Borgonovo írta:
On Wed, 09 Jan 2008 20:29:39 +0100
Zoltan Boszormenyi <zb@xxxxxxxxxxx> wrote:
The decision to use MVCC in PostgreSQL makes the point moot.
...
thanks.
In PostgreSQL, COUNT(*) responds closely at the same speed
regardless of other transactions. Which way do you prefer?
Considering the relative value of count my interest was for something
that is even less precise than the "usual" count but performs better.
I'm not proposing to turn Postgres into MySQL.
This below might be a good compromise.
Although every INSERT/DELETE will be a bit slower
because of the additional UPDATE on the administrative table.
create table rec_count (tablename text unique, rec_cnt bigint) with
(fillfactor 50);
Add any tables you want to it with their current record count and
for any tables you want to watch:
create or replace function inc_tablename_rec_cnt()
returns trigger as $$
begin
update rec_count set rec_cnt = rec_cnt + 1 where tablename =
'tablename';
return new;
end; $$ language plpgsql;
create or replace function dec_tablename_rec_cnt()
returns trigger as $$
begin
update rec_count set rec_cnt = rec_cnt - 1 where tablename =
'tablename';
return new;
end; $$ language plpgsql;
create trigger ins_tablename_rec_cnt after insert on tablename for each
row execute procedure inc_tablename_rec_cnt();
create trigger del_tablename_rec_cnt after insert on tablename for each
row execute procedure dec_tablename_rec_cnt();
The administrative table will be a highly updated one if you want
to watch a high-traffic table, hence the FILLFACTOR setting.
You may need to adjust max_fsm_pages. Later, you can do a
SELECT rec_cnt FROM rec_count WHERE tablename = 'tablename';
which will be fast and depending on the initial value of COUNT(*)
it will be very close to the exact figure. You can extend the example
with more columns if you know your SELECT COUNT(*) ... WHERE
conditions in advance but this way you have to keep several administrative
tables for different monitored tables. Again, this trades some disk space
and INSERT/DELETE operation speed on the monitored tables for
quicker count.
--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
