Re: count(*) performance improvement ideas

On Thu, 17 Apr 2008 02:48:37 +0200, Stephen Denne
<Stephen.Denne@datamail.co.nz> wrote:

> PFC wrote:
>> Let's try this quick & dirty implementation of a local
>> count-delta cache
>> using a local in-memory hashtable (ie. {}).
>
>> CREATE OR REPLACE FUNCTION update_count( key TEXT, delta INTEGER )
>>    RETURNS INTEGER
>> AS $$
>>      if key in GD:
>>              GD[key] += delta
>>      else:
>>              GD[key] = delta
>>      return GD[key]
>> $$ LANGUAGE plpythonu;
>
> Thanks for the code, this seems to be very much what I was looking for.
>
> I don't know plpythonu (nor python), just read a few docs now:
Learn Python, it is a really useful language ;)

> "The global dictionary SD is available to store data between function
> calls. This variable is private static data. The global dictionary GD is
> public data, available to all Python functions within a session. Use
> with care."
>
> Does session == transaction or connection?
> I don't understand the difference between SD and GD, private and public.
> Where are the context boundaries?
There is no sharing between processes, so- both SD and GD are limited to the current session (connection, postgres
process), no shared memory is involved- GD is global between all python functions (global)- SD is specific to each
pythonfunction (static) 
The big gotcha is that these are all non-transactional : if you rollback,
GD and SD stay the same, and when you issue a query, you can assume the
state of SD and GD is random (due to previous queries) unless you
initialize them to a known value.