Re: count * performance issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Mark Mielke wrote:
Josh Berkus wrote:
Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle.  They have the same
transactional issues we do.

Nope. Oracle's MVCC is implemented through rollback segments, rather than non-overwriting the way ours is. So Oracle can just do a count(*) on the index, then check the rollback segment for any concurrent update/delete/insert activity and adjust the count. This sucks if there's a *lot* of concurrent activity, but in the usual case it's pretty fast

I read the "almost instantaneous" against "the above claim is false" and "Nope.", and I am not sure from the above whether you are saying that Oracle keeps an up-to-date count for the index (which might make it instantaneous?), or whether you are saying it still has to scan the index - which can take time if the index is large (therefore not instantaneous).

Cheers,
mark

--
Mark Mielke <mark@xxxxxxxxx>


Oracle scans the index pages, if the b-tree index is on non-nullable columns, or if the bitmap index is on low-ish cardinality data. Otherwise, it table scans. MyISAM in MySQL would be an example where a counter is kept.





--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux