Re: Slow count(*) again...

>>
>
> A count with any joins or filter criteria would still have to scan all
> pages with visible tuples in them. So the visibility map helps speed up
> scanning of bloated tables, but doesn't provide a magical "fast count"
> except in the utterly trivial "select count(*) from tablename;" case,
> and can probably only be used for accurate results when there are no
> read/write transactions currently open.

select count(*) from tablename where [condition or filter that can use an index] [group by on columns in the index]

will also work, I think.

Additionally, I think it can work if other open transactions exist, provided they haven't written to the table being scanned. If they have, then only those pages that have been altered and marked in the visibility map need to be cracked open the normal way.

> Even if you kept a count of
> tuples in each page along with the mvcc transaction ID information
> required to determine for which transactions that count is valid, it'd
> only be useful if you didn't have to do any condition checks, and it'd
> be yet another thing to update with every insert/delete/update.
>

Yes, lots of drawbacks and added complexity.

> Perhaps for some users that'd be worth having, but it seems to me like
> it'd have pretty narrow utility. I'm not sure that's the answer.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance