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

Tom Lane wrote
> "Pavan Deolasee" <pavan.deolasee@gmail.com> writes:
> > I am sure this must have been discussed before.
>
> Indeed.  Apparently you didn't find the threads in which the idea of
> having transactions enter "delta" entries was discussed?  Solves both
> the locking and the MVCC problems, at the cost that you need to make
> cleanup passes over the counter table fairly often.

I can't find any posts that directly address what I was looking for.

In my situation I have a small number of concurrent transactions with each transaction running a large number of single
rowinserts or deletes. 

However I'm not after a fast count(*) from table, but more like a fastselect grouping_id, count(*) from my_table group
bygrouping_id 

I initially thought that what you meant by "having transactions enter "delta" entries" was that I have a trigger that
wouldcreate a row each time it was called, writing how many records where inserted or deleted. I didn't understand how
thiswould be much of an improvement, as each of my rows would contain either +1 or -1. 

But I just realised you might mean to include a txid row in my table of deltas, and in my trigger insert or update that
rowwhere txid = txid_current() 
(per grouping_id)

Is that what is recommended?

No locking problems as each transaction is only updating its own rows.

Can you clarify the lack of MVCC problems?
Do new versions of rows get created if the original version of the row was created in the current transaction?
Does this idea apply with the same efficiency in pre 8.3, non-HOT implementations?
Any advice on creating or avoiding indexes on the tables in question?

I can think of two options for a performing the cleanup passes using current functionality:
1) Using Listen/Notify
Issue a notify whenever a new row is inserted for a transaction. They get delivered post transaction commit don't they?
Andnot at all on rollback? Have an application listening for them, performing the aggregation & cleanup work. 
2) Use a schedule, based on local knowledge of expected number of transactions over time.

So I'd rephrase Pavan's suggestion as a request to have post-transaction commit triggers that have access to (at least)
thetxid of the transaction that was committed. 

Suggested syntax is to add the option "TRANSACTION" (or perhaps "COMMIT") to the CREATE TRIGGER statement:

CREATE TRIGGER name AFTER INSERT OR UPDATE OR DELETE ON table FOR EACH TRANSACTION EXECUTE PROCEDURE funcname (
arguments); 

Any of the triggering actions on the specified table ensure that the function is called once if the transaction
commits.
Requires a new TG_LEVEL.
TG_OP could be the first action triggered.

Would implementing this be extremely difficult due to transferring information from within the transaction to outside
thetransaction? 
If so, perhaps I'd get the same benefit from having a trigger set up to fire pre-commit (or pre-prepare), and be a part
ofthe transaction. 
Would the locking difficulties be reduced as the lock would not be required till late in the game, and not held for
long?

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/services/bqem.htm for details.
__________________________________________________________________