On Jan 9, 2008 10:46 AM, Andrew Sullivan <ajs@xxxxxxxxxxxxxxx> 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. :) ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
