Re: Re: select count... - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Re: select count...
Date
Msg-id 16913.995039057@sss.pgh.pa.us
Whole thread Raw
In response to Re: select count...  ("P. Dwayne Miller" <dmiller@espgroup.net>)
List pgsql-hackers
"P. Dwayne Miller" <dmiller@espgroup.net> writes:
> I think 4 seconds is way too long to return the results.  And NULLs in a
> column should not change the answer.

If you're doing count(foo) then NULLs in column foo definitely *should*
change the answer.  count(foo) does not count nulls.

It seemed to me that your original question was comparing apples and
oranges.  count(*) with no where clause will count all the rows in
the table, sure enough, but if you add a where clause then it's not
counting all the rows anymore, so why shouldn't that take less time?

But possibly the answer you need is just that Postgres does not maintain
an accurate count of the rows in a table, so it has to scan the table
to compute count(*).  Some other DBMSes do maintain such a count and so
they can return count(*) essentially instantaneously.  But they pay for
that speed with a distributed slowdown in all updates of the table.  If
you have a database application that's designed around the assumption
that count(*) is free, you'll probably need to rethink that assumption
to get good performance with Postgres.
        regards, tom lane


pgsql-hackers by date:

Previous
From: Hannu Krosing
Date:
Subject: Re: Re: select count...
Next
From: Zeugswetter Andreas SB
Date:
Subject: AW: AW: Re: [GENERAL] Vacuum and Transactions