Re: count(*) performance improvement ideas

> My wife has a snake phobia, besides, I've just started learning Scala.
Just had a look at Scala, it looks nice. Slightly Lispish (like all good
languages)...

> txid_current()
> No... hold on, it is per session, and a session can't have two or more
> transactions active at once can it?
It could be used to detect rollback.

> So the problem is that other functions may be using GD themselves, and
> your own code is at the mercy of the other functions. Conversely you
> shouldn't clear GD, as some other function may be using it.
Exactly.

> So you're better off using a single function for everything, and using
> SD within it?
Since the purpose is to store counts for rows matching a certain criteria
in a set of tables, you could build a hashtable of hashtables, like :

GD[table name][criteria name][criteria value] = count
This would add complexity, about half a line of code. But you'd have to
create lots of plpgsql trigger functions to wrap it.

> There isn't any way of telling whether the function is being called for
> the first time in a transaction. You don't know when to clear it.
The first time in a session, GD will be empty.Clearing it at the start of a transaction would not be useful (clearing
it at ROLLBACK would).It is updating the "real" summary table with the contents of this hash
that is the problem, also.
So, basically, if you connect, do one insert, and disconnect, this would
be useless.But, if you do a zillion inserts, caching the counts deltas in RAM would
be faster.And if you use persistent connections, you could update the counts in the
real table only every N minutes, for instance, but this would need some
complicity from the backend.

> Regards,
> Stephen Denne.
>
> Disclaimer:
> At the Datamail Group we value team commitment, respect, achievement,
> customer focus, and courage. This email with any attachments is
> 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 Business Quality
>               Electronic Messaging Suite.
> Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
> __________________________________________________________________
>
>