Re: count(*) performance improvement ideas - Mailing list pgsql-hackers

Tom Lane wrote
> "Stephen Denne" <Stephen.Denne@datamail.co.nz> writes:
> > What I was asking about with those questions, is if a
> > single row is inserted in transaction A, and updated 1000
> > times still within transaction A, then transaction A
> > commits... does a single row version get written, or 1001,
> > 1000 of which are removable?
>
> Umm ... AFAICS there is no need for an UPDATE to touch the count table
> at all.  You'd only need ON INSERT and ON DELETE triggers.

I'm not referring to updates of my base table... the single row inserted was referring to the delta row...

I'm trying to group together a large number of +1 or -1 deltas into a single delta per transaction.

A cut down example:

CREATE TABLE document_type_summary_detail
( document_type_id integer NOT NULL, txid bigint NOT NULL DEFAULT 0, documents bigint NOT NULL DEFAULT 0, CONSTRAINT
pk_document_type_summaryPRIMARY KEY (document_type_id, txid) 
);

CREATE OR REPLACE FUNCTION document_count_trig()
RETURNS TRIGGER AS
$$  DECLARE  BEGIN  IF TG_OP = 'INSERT' THEN     UPDATE document_type_summary_detail set documents=documents+1 where
document_type_id= NEW.document_type_id and txid=txid_current();     IF NOT FOUND THEN        INSERT INTO
document_type_summary_detail(document_type_id,documents,txid) VALUES(NEW.document_type_id,1,txid_current());     END
IF;    RETURN NEW;  ELSIF TG_OP = 'DELETE' THEN     UPDATE document_type_summary_detail set documents=documents-1 where
document_type_id= OLD.document_type_id and txid=txid_current();     IF NOT FOUND THEN        INSERT INTO
document_type_summary_detail(document_type_id,documents,txid) VALUES(OLD.document_type_id,-1,txid_current());     END
IF;    RETURN OLD;  END IF;  END; 
$$
LANGUAGE 'plpgsql';
create trigger document_count_trig before insert or delete on document for each row execute procedure
document_count_trig();

--one off setup:
insert into document_type_summary_detail (document_type_id,txid,documents)
select dt.id, 0, count(d.id) from document_type dt left join document d on d.document_type_id = dt.id
group by 1,2;


--useful view:
CREATE OR REPLACE VIEW document_type_summary AS SELECT document_type_id, sum(documents) AS documents FROM
document_type_summary_detailGROUP BY document_type_id; 


--scheduled cleanup:
CREATE OR REPLACE FUNCTION document_type_summary_aggregate() RETURNS void AS
$$  BEGIN     INSERT INTO document_type_summary_detail(document_type_id) select distinct document_type_id from
document_type_summary_detailexcept select document_type_id from document_type_summary_detail where txid=0;     UPDATE
document_type_summary_detailset documents=v.documents from document_type_summary as v where
document_type_summary_detail.document_type_id= v.document_type_id and document_type_summary_detail.txid=0 and exists
(select1 from document_type_summary_detail ss where ss.document_type_id = document_type_summary_detail.document_type_id
andss.txid <> 0);     DELETE FROM document_type_summary_detail where txid <> 0;  END; 
$$
LANGUAGE 'plpgsql';


My assumption is that this solves the "locking causes serialisation of transactions" problem as the only rows updated
arethose inserted by the same transaction. 

Regards,
Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any
attachmentsis confidential and may be subject to legal privilege.  If it is not intended for you please advise by reply
immediately,destroy it and do not copy, disclose or use it in any way. 
__________________________________________________________________ This email has been scanned by the DMZGlobal
BusinessQuality             Electronic Messaging Suite. 
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__________________________________________________________________