Thread: Re: PostgreSQL Gotchas --- 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 duringa 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. Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: pgsql-general-owner@postgresql.org on behalf of Jeffrey Melloy Sent: Thu 10/6/2005 3:47 PM To: Neil Conway Cc: Aly S.P Dharshi; pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL Gotchas Neil Conway wrote: > >"COUNT(*) very slow": this is a known issue -- see the -hackers archives >for many prior discussions. MVCC makes this hard to solve effectively >(whether applications should actually be using COUNT(*) on large tables >with no WHERE clause is another matter...) > >-Neil > > And it's not like a count(*) on an Oracle database of any decently-sized dataset is blazing fast, or even in blazing's ballpark. The only thing I could see actually being an issue is the random() one and add missing from. The rest are trivial. The random() thing is interesting, esoteric, and probably has never been a problem in a real situation. (Or has exactly once, when he wrote that gotcha) Jeff ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings !DSPAM:4345aeea115747915089936!
Gregory S. Williamson wrote: > 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). -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 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?
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.
There's some discussion in the pgsql-hackers archives about possible
add-on mechanisms to maintain COUNT(*) counts on tables for which the
DBA thinks it's justified. It seems clearly doable, but no one's gotten
excited enough to actually do it ... in the end, it seems that everyone
who's looked closely at their application has decided that it wasn't so
important after all.
regards, tom lane