5 ms·

http://wiki.postgresql.org/wiki/Slow_Counting http://wiki.postgresql.org/wiki/Slow_Counting Are there DBMSs where this doesn't happen? I just did a SELECT COU

by lusr 14y ago

http://wiki.postgresql.org/wiki/Slow_Counting http://wiki.postgresql.org/wiki/Slow_Counting Are there DBMSs where this doesn't happen? I just did a SELECT COUNT(*) on a table here in our QA environment; 20.5 seconds to count 42385875 records from one table and 68.8 seconds to count 191906711 records from another table (Oracle 10g 64-bit).

mgkimsal 14y ago
"The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table; PostgreSQL must walk through all rows, in some sense." When operating on a table, my understanding is that pg will select a version and operate on that version. If other versions are being worked on in transactions - that's a different story. Why can't metadata about the number of rows be assigned with the table version data, so after every operation, you'd know what the number of rows was at at that moment in time?
- jeltz 14y ago
  PostgreSQL does not track any versions at the table level, instead it tracks the versions for every row. This means two queries can modify different parts of the table concurrently without any lock contention.[1] In PostgreSQL every row has two numbers. The transaction ID it was insert in and the transaction ID it was deleted in. An update is an insert plus a delete.[2] When running a select in PostgreSQL you just traverse the table and for each row check these two numbers to know if you are allowed to see the row. The details above are PostgreSQL specific but most other databases have the same problem with there being no way to know the exact count without actually counting the rows. Footnotes: 1. There is contention currently in PostgreSQL when writing the database journal (used for crash recovery and replication). 2. There are some optimization which are done here. For example HOT to avoid index updates.

by lusr 14y ago

mgkimsal 14y ago

"The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table; PostgreSQL must walk through all rows, in some sense." When operating on a table, my understanding is that pg will select a version and operate on that version. If other versions are being worked on in transactions - that's a different story. Why can't metadata about the number of rows be assigned with the table version data, so after every operation, you'd know what the number of rows was at at that moment in time?

jeltz 14y ago
PostgreSQL does not track any versions at the table level, instead it tracks the versions for every row. This means two queries can modify different parts of the table concurrently without any lock contention.[1] In PostgreSQL every row has two numbers. The transaction ID it was insert in and the transaction ID it was deleted in. An update is an insert plus a delete.[2] When running a select in PostgreSQL you just traverse the table and for each row check these two numbers to know if you are allowed to see the row. The details above are PostgreSQL specific but most other databases have the same problem with there being no way to know the exact count without actually counting the rows. Footnotes: 1. There is contention currently in PostgreSQL when writing the database journal (used for crash recovery and replication). 2. There are some optimization which are done here. For example HOT to avoid index updates.