On Mon, Mar 7, 2011 at 1:13 PM, Merlin Moncure
<mmoncure@gmail.com> wrote:
On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard <
glenn@zewt.org> wrote:
> 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.
The stats system only helps for the most basic case--counting the number of rows in a table. In my experience that's not very common; most of the time it's counting total results from some more interesting query, eg. for pagination. In my particular case, I'm caching results for SELECT COUNT(*), expr2 FROM table WHERE expr GROUP BY expr2 (for a very limited set of expressions).
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).
Row triggers are fast enough for my case--it's a read-heavy workload, so it's okay to take a bit more time inserting new data. It's easier to ensure consistency with row triggers, since they can be tested independently of anything modifying the table.
--
Glenn Maynard