2011/3/8 Merlin Moncure <mmonc...@gmail.com> > On Mon, Mar 7, 2011 at 3:16 PM, Glenn Maynard <gl...@zewt.org> wrote: > > 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). > > SELECT COUNT(*) FROM table WHERE expr; > > will use index (assuming expr is optimizable and is worth while to > optimize). Your case might be interesting for cache purposes if expr2 > is expensive, but has nothing to do with postgres index usage via > count(*). mysql/myisam needs to scan as well in this case -- it > can't magically 'look up' the value as it can for the in filtered > (very special) case...
Exactly! > it only differs from pg in that it can skip > heap visibility check because all records are known good (and pg is > moving towards optimizing this case in mostly read only workloads!) > > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- // Dmitriy.
