Counting is one of the easiest SQL statements, so it is also many people's first experience of a PostgreSQL query.
From any interface, the SQL command used to count rows is as follows:
SELECT count(*) FROM table;
This will return a single integer value as the result.
In psql, the command looks like the following:
postgres=# select count(*) from orders; count ─────── 345 (1 row)
PostgreSQL can choose between two techniques available to compute the SQL count(*) function.
The first is called Sequential Scan, and it is available in all currently supported versions. 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, it will cause a beneficial disk access pattern, and the statement will be fairly fast.
The other technique is known as Index-Only Scans, and it was introduced in PostgreSQL 9.2. It requires an index on the table, and it covers a more general case than optimizing...