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

On Jan 9, 2008 10:46 AM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
> On Wed, Jan 09, 2008 at 05:21:24PM +0100, Ivan Sergio Borgonovo wrote:
> >
> > I got the impression that even counting with clauses on on indexed
> > columns means you'll have to check if columns are still there. That
> > seems to imply that the extra cost make pg under perform compared to
> > other DB even in that scenario.
>
> You have to do this for any row you need to see, for _any_ database
> operation in Postgres. But that's no different from any other database
> system: they all have to locate all the rows that satisfy the condition, and
> then tell you how many there are.
>
> Many other systems, however, "know" how many rows there are in the table.
> In some sense, they have optimised for that case at the expense of other
> cases (like, for instance, more aggressive locks than Postgres takes, or
> failures due to rollback segment exhaustion, or whatever your favourite
> limitation of your system of choice is). When you build a system, you're
> going to trade some features for others more than likely, and the real
> question is what things you trade away. The speed of counting all the rows
> in the table seems to me to be a good thing to trade away, because it's very
> rare that you actually need to know that.
>
> > If you're interested in all the record in a table, there is no way to
> > have an "engraved in stone" answer
>
> Sure there is: SELECT count(*) from table. That tells you how many rows
> there were in the table when your transaction started (modulo read
> visibility rules), just like in any other database system.

And if, for some god forsaken reason, you need to operate on that
number, there's always "lock table"...

I feel dirty. :)