4 ms·
Has slowness of SELECT COUNT() on large datasets been addressed in any way? Last time I checked it was responded with "it's slow because of how we do things her
by scotty79 15y ago
Has slowness of SELECT COUNT() on large datasets been addressed in any way? Last time I checked it was responded with "it's slow because of how we do things here" and if you want to count records fast "you count them yourself" (not actual quotes from anyone, just tl;dr of some answers I've seen).
- saurik 15y agoIf you mean, "have other major database vendors, such as MySQL, become slower in most of the same ways", then "yes, and for the same reasons ;P"; the one place PostgreSQL could be better on that is that it can't use a covering index currently (which would let it get the count of a filtered query only with a scan of the index, without needing to read and validate the table rows), but that is being addressed in 9.2.
- fdr 15y agoI basically find your sentiment accurate: databases with high concurrency support need to do a lot of work to do an accurate count(*). However, many that were not postgres supported index-only scans to do it. That doesn't change the algorithmic complexity of O(n) to grovel through the tuples, but it can have a large constant time difference. No anymore, though....9.2 supports index only scans in many common cases. Still, as you say, counting all your tuples repeatedly in a table is going to be much, much more expensive than maintaining the count on categories you care about up-front, paying the lock contention and update-costs as one goes in virtually any database that supports SMP.
- Erwin 15y agoIf you want an approximate count you can do something like to get data for all your tables: SELECT S.nspname || '.' || relname, reltuples FROM pg_class C, pg_namespace S WHERE C.relnamespace = S.oid ORDER BY reltuples DESC This is the estimate used by the query planner, so it's updated whenever a VACUUM occurs (not sure how often the autovacuum runs).
- outworlder 15y agoAs often as it is needed, depending on your database settings.
- fusiongyro 15y agoI kind of hate this "tl;dr" culture. I can explain the reason why this is never going to change (though it may get better with index scans) but I expect you'll just see a wall of text and decide not to read it. But if you never read long answers, you have no right to expect solutions to your problems or to understand anything. So I hope you'll read this, it may help. PostgreSQL and most modern, ACID-compliant RDBMSes use MVCC instead of locks. MVCC has a lot of upsides, namely, you almost never need table-level locking for writes, but it ensures you need to look at the whole table to do COUNT. That's because MVCC is basically multiple parallel universes for data. What you see depends critically on which transaction you're in when you look. For example, suppose you have a process that needs to know the number of users in some user table. You start your transaction, and then another process starts a transaction that goes through and deletes certain users. While this is happening, you need the count. In MVCC, deletion doesn't delete, it just marks rows with the transaction ID range in which they're visible. The rows that are being deleted by the other process are just being marked dead as of that process's transaction ID, but your transaction ID is below that one, so they're not dead to you. Later on, when the youngest transaction alive is older than the oldest transaction that can see these rows, PostgreSQL will actually expunge the data (VACUUM does this). Because of MVCC, there is more than one legitimate answer the question of COUNT; potentially, one for each transaction in progress. The answer is changing constantly. People tend not to think in these terms; we tend to think, well the table has so many rows in it, right? Why don't you just increment the count when you write one? It's not true, because with ACID compliance, uncommitted transactions should not have visible effects until they commit and transactions in progress should not see anything vary during their operation. So there really are multiple right answers at any given moment. The expectation that SELECT COUNT(*) will be fast is built on the assumption that the database has some sort of master count of what rows are in the table it can just glance at. But it doesn't, and to have one would require removing MVCC altogether and using locks instead.
- ataggart 15y agoI'm not sure I buy your argument. It is certainly true that with MVCC "there is more than one legitimate answer the question of COUNT", but that's true for any query, not just COUNT. Given that we can maintain transactional correctness for the data, it's not at all clear to me why the same mechanisms cannot be used to maintain transactional correctness for the metadata.