Skip site navigation (1) Skip section navigation (2)

Re: Much Ado About COUNT(*)

From: Christopher Browne <cbbrowne(at)acm(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-25 12:03:28
Message-ID: m38y6hvhpr.fsf@knuth.knuth.cbbrowne.com (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-hackerspgsql-patches
Centuries ago, Nostradamus foresaw when mkoi-pg(at)aon(dot)at (Manfred Koizar) would write:
> On Mon, 24 Jan 2005 08:28:09 -0700, "Jonah H. Harris" <jharris(at)tvi(dot)edu>
> wrote:
>>            UPDATE pg_user_table_counts
>>                SET rowcount = rowcount + 1
>>                WHERE schemaname = this_schemaname
>>                    AND tablename = TG_RELNAME;
>
> This might work for small single user applications.  You'll have to keep
> an eye on dead tuples in pg_user_table_counts though.
>
> But as soon as there are several concurrent transactions doing both
> INSERTs and DELETEs, your solution will in the best case serialise
> access to test_tbl or it will break down because of deadlocks.

At that point, what you need to do is to break the process in three:

 1.  Instead of the above, use...

     insert into pg_user_table_counts (rowcount, schemaname,
       tablename) values (1, this_schemaname, TG_RELNAME);

     The process for DELETEs involves using the value -1, of course...

 2.  A process needs to run once in a while that does...

     create temp table new_counts as
        select sum(rowcount), schemaname, tablename from
            pg_user_table_counts group by schemaname, tablename;
     delete from pg_user_table_counts;
     insert into pg_user_table_counts select * from new_counts;

     This process "compresses" the table so that it becomes cheaper to
     do the aggregate in 3.

 3.  Querying values is done differently...

     select sum(rowcount) from pg_user_table_counts where schemaname =
      'this' and tablename = 'that';
-- 
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/nonrdbms.html
Rules of the  Evil Overlord #118. "If I  have equipment which performs
an  important function,  it  will not  be  activated by  a lever  that
someone  could  trigger  by   accidentally  falling  on  when  fatally
wounded." <http://www.eviloverlord.com/>

In response to

pgsql-announce by date

Next:From: David FetterDate: 2005-01-29 07:04:59
Subject: == PostgreSQL Weekly News - January 28 2005 ==
Previous:From: Manfred KoizarDate: 2005-01-25 07:33:57
Subject: Re: Much Ado About COUNT(*)

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2005-01-25 13:19:05
Subject: Re: userlock changes for 8.1/8.2
Previous:From: Christopher Kings-LynneDate: 2005-01-25 10:36:34
Subject: Re: [HACKERS] RQ: Prepared statements used by multiple connections

pgsql-patches by date

Next:From: Dave PageDate: 2005-01-25 12:39:35
Subject: Re: pg_autovacuum Win32 Service startup delay
Previous:From: Andrew DunstanDate: 2005-01-25 11:02:53
Subject: Re: add soundex difference function to contrib/fuzzystrmatch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group