Re: PostgreSQL Gotchas --- count()

>> Gregory S. Williamson wrote:
>>> [ re COUNT(*) ]
>>> On Informix however it is blindingly fast, and can also be instantly
>>> conjured with the dbaccess tool (Info/Table/Status). They might be
>>> stashing this count somewhere, but it is not available when the table
>>> is locked, as during a load. However they do it, performance does not
>>> seem to suffer, and having this rapidly available is certainly nice.
>>> Especially when people are used to it.
>
>> Informix locks rows during modification so they don't have the MVCC
>> visibility problem we have (some rows are visible to only some
>> backends).
>
>More to the point: "performance does not seem to suffer" is an opinion
>based on no facts. You have no idea what it's costing Informix to
>maintain that count --- ie, how much faster might other things go if
>COUNT(*) didn't have to be instant?

Excellent point. But since my standard was "is Postgres as fast as Informix" on bulk loads / deletes / updates (which is where this delay would surely manifest itself) I had a faint disappointment on first using Postgres and seeing this: "The load, etc. speeds are close enough, so why this wild disparity in count" I thought to myself.

I understand well why this is -- been hashed out a lot on various lists -- and I am not specifically arguing for changing Postgres. Just emphasizing that this needs to be spelled well in any "Gotchas" discussion.

>We know quite well what it would cost to make this happen in Postgres,
>and it's the general judgment that we don't want to pay those costs ---
>certainly not to force everyone to pay them.

An option (compile time ?) that let users have some tradeoff *might* be of interest to some. But not worth desitracting core people from more pressing issues.

My $0.02 worth ... sorry to waste bandwidth.

G