On Mon, Mar 7, 2011 at 1:13 PM, Merlin Moncure <mmonc...@gmail.com> wrote:
> On Sun, Mar 6, 2011 at 2:57 PM, Glenn Maynard <gl...@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. > > beyond what the stats system does you mean? > 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
