Re: count(*) performance improvement ideas

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)nz> writes:
>
>> However I'm not after a fast count(*) from table, but more like a fast
>> select grouping_id, count(*) from my_table group by grouping_id
>
> You could apply the same technique across each group id, though this
> certainly is getting beyond what any built-in feature might offer.

At that point you're talking about materialized views. Which makes it a whole
lot more interesting imho.

>> Does this idea apply with the same efficiency in pre 8.3, non-HOT implementations?
>
> I didn't claim it was amazingly efficient in any implementation ;-).
> HOT in particular is nearly useless since most rows in the count
> table will never be updated, only inserted and eventually deleted.
> You might get some mileage on the base row, but that'd be about it.
> The count table will need frequent vacuums as well as frequent
> aggregation scans.

It might be better not to update this delta table in normal transactional
updates. After all the tuples you're deleting are precisely the ones that
nobody should be interested in any more. If you locked the table and magically
deleted those tuples and updated the master tuple using the global xmin
instead of your real xid people would get the same result and you could
reclaim the space much much sooner. Locking the table kind of sucks though.
And crash recovery would be a problem.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!