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.
A