Re: count * performance issue

Matthew wrote:
> No, actually I was referring to a race condition. So, you find the count
> of rows with IS NULL, then someone changes a row, then you find the
> count of rows with IS NOT NULL. Add the two together, and there may be
> rows that were counted twice, or not at all.

Not a problem if you use a serializable transaction, or if you do

SELECT COUNT(*) from table WHERE indexed_field IS NULL
UNION ALL
SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL

as one statement.

However, this makes no sense whatsoever. As both index scans (assuming
the planner even chooses an index scan for them, which seems highly
unlikely) still have to visit each tuple in the heap. It's always going
to be slower than a single "SELECT COUNT(*) FROM table" with a seq scan.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com