Database Reference
In-Depth Information
If you think a little deeper about this, you'll see that the result of the count SQL statement is
just the value at a moment in time. Depending on what happens to the table, that value could
change a little, or a lot, while the count SQL statement is executing. So, once you've executed
this, all you really know is that at some point in the past there were "X" rows in the table.
Quick estimate of the number of rows
in a table
We don't always want an exactly accurate count of rows, especially on a large table, since that
may take a long time to execute. Administrators often need to estimate how big a table is so
that they can estimate how long other operations may take.
How to do it...
We can get a quick estimate of the number of rows in a table by using roughly the same
calculation that the Postgres optimizer uses:
SELECT (CASE WHEN reltuples > 0 THEN
pg_relation_size('mytable')/(8192*relpages/reltuples)
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable'::regclass;
which gives:
estimated_count
─────────────────
293
(1 row)
which returns a row count very quickly, no matter how large the table that we are examining is.
How it works...
We saw the pg_relation_size() function earlier, so we know it brings back an accurate
value for the current size of the table.
When we vacuum a table in Postgres, we record two pieces of information in the pg_class
catalog entry for the table. These two items are the number of data blocks in the table
(relpages) and the number of rows in the table (reltuples). Some people think they can use
the value of relpages in pg_class as an estimate, but if you use that it could be severely out of
date. You will also be fooled if you use information in another table named pg_stat_user_
tables , which is discussed more in the chapter Performance .
 
Search WWH ::




Custom Search