Re: count(*) and bad design was: Experiences with extensibility

Ivan Sergio Borgonovo írta:
> On Wed, 09 Jan 2008 20:29:39 +0100
> Zoltan Boszormenyi <zb@cybertec.at> 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/