On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard <glenn@xxxxxxxx> wrote: > On Sun, Mar 6, 2011 at 5:41 AM, Martijn van Oosterhout <kleptog@xxxxxxxxx> > wrote: >> >> If it's really really important there are ways you can use trigger >> tables and summary views to achieve the results you want. Except it's >> expensive and when people are told that all of the sudden the count(*) >> performance isn't so important any more. :) > > That's often perfectly fine, with read-heavy, single-writer workloads. > > I definitely wish there was a way to create indexes to track counters on > various types of queries, even if it eliminates write concurrency on > affected writes. Doing it by hand is a pain. beyond what the stats system does you mean? If you aren't interested in high concurrency count it really isn't all that difficult -- just push table modifying queries into a procedure and grab rows affected. Row level trigger can also do it but performance will suck unless you are already doing all row by row processing (in which case your performance already sucks). The way to do this in with high concurrency is like the above, but insert (not update) rows affected into a table modification log that is rolled up on time interval or user demand so you don't serialize access w/every statement. Or you dispense with all the fuss and grab fee'n'easy approximate count from the stats system which is really what people want 99% of the time. In the old days this was much more complicated problem because to eek every bit of oltp performance out of the server you had to disable the stats collector. Today you don't, so let it do your work for you. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
