Re: count(*) performance improvement ideas

(apologies for the email format & top posting, I've had to temporarily switch to using a web interface from home that doesn't seem to know what plain text is)

pg_settings view doesn't contain custom variables created on the fly, (nor, from memory, ones defined in postgresql.conf. I'm not able to check and confirm that at the moment).
Fixing that would satisfy 2 & 3 nicely.
The docs on them say the ones in postgresql.conf are to be used by modules when they initialize, as the values to use when setting up actual real server variables, (able to store more types than just text), which I presume would appear in pg_settings.

Updating existing variables is much quicker, over 100000 updates per second.

Regards,
Stephen Denne.

________________________________

From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wed 16/04/2008 1:55 p.m.

"Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)nz> writes:
> The custom variable class can also be used to aggregate the deltas within a transaction, though there are some minor difficulties:

> 1) They only store text
> 2) You can only find out that a value has not been set by catching an exception
> 3) There is no way to list the settings.

As for 2) and 3), can't you look into the pg_settings view?

> The time to create them for the first time for a connection seems to
vary a bit, between 200 and 1000 per minute.

Yeah, that path is not optimized at all because it was never considered
performance-critical. Updating an existing variable should be cheaper.

regards, tom lane

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.
__________________________________________________________________