Re: count(*) and bad design was: Experiences with extensibility

On Jan 9, 2008 12:58 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wed, 9 Jan 2008 20:01:05 +0100
> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:
>
> > On Wed, 9 Jan 2008 10:30:45 -0600
> > "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:
> >
> > > Now, everything's a tradeoff. If PostgreSQL had visibility
> > > information in the indexes, it would have to lock both the table and
> > > index for every write, thus slowing down all the other queries that
> > > are trying to access the table. It would be a tradeoff that
> > > sacrificed write speed for read speed. In a db that was used mostly
> > > for writing, it would likely be a fair trade. In a db that did a
> > > lot of writing, it might slow the whole thing to a crawl.
> >
> > OK... we are getting near to the point. I understand the trade-off
> > problem in storing into indexes id the row is still there.
> > Is there a way to get the count of the rows that *may be* there,
>
> If you analyze regularly you can use pg_class. It isn't exact but is
> usually close enough (especially if you are just using it for something
> like pagination).

Yeah, but the OP's point was that it doesn't work if you have a where clause.

I could see a use for an approximate count(*) with where clause, just
like I could see a use for the ability to retrieve random rows from a
table without using order by random() on it. And those are both
things that would require some form of hacking in the db that I'm
certainly not capable of pulling off...