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
