Database Reference
In-Depth Information
How many rows in a table?
Counting is one of the easiest SQL statements, so it is also many people's first experience
of a Postgres query.
How to do it...
From any interface the SQL command is, as follows:
SELECT count(*) FROM table;
which returns a single integer value as the result.
In psql this looks like the following:
postgres=# select count(*) from orders;
count
───────
345
(1 row)
How it works...
The SQL
count(*)
function will scan every row in the table using a technique named a
Sequential Scan. We access every data block in the table one after the other, reading the
number of rows in each block. If the table is on disk, then this will cause a beneficial disk
access pattern, and the statement will be fairly fast.
Some people think that the count SQL statement is a good test of the performance of a
database management system (DBMS). Some DBMS have specific tuning features for the
count
SQL statement, though Postgres does not optimizes this SQL. The PostgreSQL project
has talked about this many times, but few people think we should try to optimize this. Yes,
the "count" function is frequently used within applications, but the count function without
any
WHERE
clause is not that useful. So, we have not yet chosen to optimize this form of
SQL specifically.
The reason we scan every block of the table is because of a major feature of Postgres named
MVCC, which stands for Multi-Version Concurrency Control. MVCC allows us to run the count
SQL statement at the same time that we are inserting, updating, or deleting data from the table.
That's a very cool feature, and we go into a lot of trouble in Postgres to provide that to you.
MVCC requires us to record information on each row of a table, stating when that change
was made. If the changes were made after the SQL statement begins to execute, then we
just ignore those changes. This means that we need to make "visibility checks" on each row
in the table to allow us to work out the result to the
count
SQL statement.
