INTRO : Why It Matters?
On my previous post “Table Statistics vs. SELECT COUNT: What to Choose?” I have mentioned two concepts of finding Row Counts: Using Table Statistics and SELECT COUNT(*) from a non-technical, mostly business requirements perspective.
The debate centers around whether to rely on the table statistics or to compute the exact row count with “SELECT COUNT(*) FROM” of the table.
The main reason for questioning the accuracy of the table statistics is that they are often gathered using a sample (like default_statistics_target parameter, ALTER TABLE SET STATISTICS) or may be outdated and not current. If the table is not in a read-only state and serves an actively used application, the statistics can never truly be up-to-date.
On the other side, what’s wrong with computing the exact row count of each table?
The performance overhead alone is a sufficient reason to avoid it.
Worries arise when row counts are queried for all tables, not when only a single table.
Now, I aim to question this practice from a technical perspective to analyze for PostgreSQL whether this really has such a catastrophic impact from a technical standpoint.
I will use the “Adventureworks” database for testing in the PostgreSQL Database 17.2 running on a docker with MacBook M1. To create test environment you may use commands provided below.
Pull PostgreSQL image and Start PostgreSQL database container
osmandinc@192 ~ % docker pull postgres:latest
latest: Pulling from library/postgres
Digest: sha256:994cc3113ce004ae73df11f0dbc5088cbe6bb0da1691dd7e6f55474202a4f211
Status: Image is up to date for postgres:latest
docker.io/library/postgres:latest
osmandinc@192 ~ % docker run -d -p 4459:5432 --name postgres-latest -e POSTGRES_PASSWORD=InsaneDBAiscool postgres:latest
f06832f9ecfbc57c592e69327b6d166fb2528f35af64edec89123cb096b5e90bTest connection.
osmandinc@192 ~ % export PGPASSWORD='InsaneDBAiscool';
osmandinc@192 ~ % psql -d postgres -h localhost -p 4459 -U postgres
psql (14.11 (Homebrew), server 17.2 (Debian 17.2-1.pgdg120+1))
WARNING: psql major version 14, server major version 17.
Some psql features might not work.
Type "help" for help.
postgres=# select version();
version
-------------------------------------------------------------------------------------------------
PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)Download Adventure Works database from Microsoft Github Repo.
Download AdventureWorks-for-Postgres from Github Repo.
Extract both .zip files and copy all of the CSV files into the same folder( /Users/osmandinc/Downloads/AdventureWorks-oltp-install-script for me) also containing update_csvs.rb file and install.sql.
Modify the CSVs to work with Postgres by running:
osmandinc@192 ~ % cd Downloads/AdventureWorks-oltp-install-script
osmandinc@192 AdventureWorks-oltp-install-script % ruby update_csvs.rb
Processing ./CountryRegion.csv
Processing ./EmailAddress.csv
Processing ./ProductModel.csv
Processing ./BusinessEntityAddress.csv
Processing ./StateProvince.csv
Processing ./BusinessEntityContact.csv
Processing ./Employee.csv
Processing ./Password.csv
Processing ./JobCandidate.csv
Processing ./Address.csv
Processing ./CountryRegionCurrency.csv
Processing ./Person.csv
Processing ./Illustration.csv
Processing ./BusinessEntity.csv
Processing ./JobCandidate_TOREMOVE.csv
Processing ./Document.csv
Processing ./PhoneNumberType.csv
Processing ./ProductPhoto.csv
Processing ./ProductDescription.csv
Processing ./Store.csv
Processing ./PersonPhone.csv
Processing ./ProductModelorg.csv
osmandinc@192 AdventureWorks-oltp-install-script %Create the database and tables, import the data.
osmandinc@192 bin % psql -d postgres -h localhost -p 4459 -U postgres -c "CREATE DATABASE \"Adventureworks\";"
CREATE DATABASE
osmandinc@192 bin % cd /Users/osmandinc/Downloads/AdventureWorks-oltp-install-script
osmandinc@192 AdventureWorks-oltp-install-script % psql -d Adventureworks -h localhost -p 4459 -U postgres < install.sql
CREATE EXTENSION
CREATE EXTENSION
CREATE DOMAIN
CREATE DOMAIN
CREATE DOMAIN
CREATE DOMAIN
CREATE DOMAIN
CREATE DOMAIN
CREATE SCHEMA
COMMENT
Copying data into Person.BusinessEntity
COPY 20777
Copying data into Person.Person
...
CREATE SCHEMA
UPDATE 0
List of relations
Schema | Name | Type | Owner
----------------+---------------------------------------+-------+----------
humanresources | department | table | postgres
humanresources | employee | table | postgres
humanresources | employeedepartmenthistory | table | postgres
humanresources | employeepayhistory | table | postgres
humanresources | jobcandidate | table | postgres
humanresources | shift | table | postgres
person | address | table | postgres
person | addresstype | table | postgres
person | businessentity | table | postgres
person | businessentityaddress | table | postgres
person | businessentitycontact | table | postgres
person | contacttype | table | postgres
person | countryregion | table | postgres
person | emailaddress | table | postgres
person | password | table | postgres
person | person | table | postgres
person | personphone | table | postgres
person | phonenumbertype | table | postgres
person | stateprovince | table | postgres
production | billofmaterials | table | postgres
production | culture | table | postgres
production | document | table | postgres
production | illustration | table | postgres
production | location | table | postgres
production | product | table | postgres
production | productcategory | table | postgres
production | productcosthistory | table | postgres
production | productdescription | table | postgres
production | productdocument | table | postgres
production | productinventory | table | postgres
production | productlistpricehistory | table | postgres
production | productmodel | table | postgres
production | productmodelillustration | table | postgres
production | productmodelproductdescriptionculture | table | postgres
production | productphoto | table | postgres
production | productproductphoto | table | postgres
production | productreview | table | postgres
production | productsubcategory | table | postgres
production | scrapreason | table | postgres
production | transactionhistory | table | postgres
production | transactionhistoryarchive | table | postgres
production | unitmeasure | table | postgres
production | workorder | table | postgres
production | workorderrouting | table | postgres
purchasing | productvendor | table | postgres
purchasing | purchaseorderdetail | table | postgres
purchasing | purchaseorderheader | table | postgres
purchasing | shipmethod | table | postgres
purchasing | vendor | table | postgres
sales | countryregioncurrency | table | postgres
sales | creditcard | table | postgres
sales | currency | table | postgres
sales | currencyrate | table | postgres
sales | customer | table | postgres
sales | personcreditcard | table | postgres
sales | salesorderdetail | table | postgres
sales | salesorderheader | table | postgres
sales | salesorderheadersalesreason | table | postgres
sales | salesperson | table | postgres
sales | salespersonquotahistory | table | postgres
sales | salesreason | table | postgres
sales | salestaxrate | table | postgres
sales | salesterritory | table | postgres
sales | salesterritoryhistory | table | postgres
sales | shoppingcartitem | table | postgres
sales | specialoffer | table | postgres
sales | specialofferproduct | table | postgres
sales | store | table | postgres
Adventureworks=# ANALYZE;Option 1 : Relying on the Stats Collector to Find Row Counts (pg_stat_all_tables)
First, I will create a test table with exactly 21,350,068 rows to analyze whether table size impacts query execution statistics. The goal is to determine how many buffers (through the cache or the disk) are required to calculate the total row count for a large table. When retrieving the row count from catalog statistics using pg_stat_all_tables , the actual table size is irrelevant since the query accesses metadata, not the table itself.
Adventureworks=# create table person.big_table as select person.firstname,person.lastname,person.suffix,person.emailpromotion,productinventory.productid, productinventory.locationid, productinventory.quantity
from person.person ,production.productinventory ;
SELECT 21350068Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
schemaname AS schemaname,
relname AS table_name,
n_live_tup AS num_rows
FROM
pg_stat_all_tables
WHERE
relid= 'person.big_table'::regclass;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Subquery Scan on pg_stat_all_tables (cost=13.80..13.83 rows=1 width=136) (actual time=0.089..0.091 rows=1 loops=1)
Buffers: shared hit=5
-> GroupAggregate (cost=13.80..13.82 rows=1 width=316) (actual time=0.087..0.088 rows=1 loops=1)
Group Key: n.nspname
Buffers: shared hit=5
-> Sort (cost=13.80..13.80 rows=1 width=132) (actual time=0.072..0.073 rows=1 loops=1)
Sort Key: n.nspname
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=5
-> Nested Loop Left Join (cost=0.42..13.79 rows=1 width=132) (actual time=0.056..0.058 rows=1 loops=1)
Join Filter: (n.oid = c.relnamespace)
Rows Removed by Join Filter: 4
Buffers: shared hit=5
-> Nested Loop Left Join (cost=0.42..12.47 rows=1 width=72) (actual time=0.039..0.040 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using pg_class_oid_index on pg_class c (cost=0.28..8.30 rows=1 width=72) (actual time=0.023..0.024 rows=1 loops=1)
Index Cond: (oid = '19479'::oid)
Filter: (relkind = ANY ('{r,t,m,p}'::"char"[]))
Buffers: shared hit=3
-> Index Only Scan using pg_index_indrelid_index on pg_index i (cost=0.15..4.17 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (indrelid = '19479'::oid)
Heap Fetches: 0
Buffers: shared hit=1
-> Seq Scan on pg_namespace n (cost=0.00..1.14 rows=14 width=68) (actual time=0.013..0.013 rows=5 loops=1)
Buffers: shared hit=1
Planning:
Buffers: shared hit=7
Planning Time: 0.636 ms
Execution Time: 0.164 ms
(29 rows)With just 12 blocks (7 blocks accessed during the query planning phase from catalog + 5 blocks were accessed from the shared buffer during query execution.), we can retrieve the row count information for a single large table efficiently. But what happens when we add two more large tables each containing 20 million records? Without surprise, even in this case, it’s not a big deal. Since all values are retrieved from the catalog, the table size does not play a role. The actual n_live_tup values are maintained by the stats collector, which monitors table modifications (inserts, updates, and deletes) in near real-time. Querying this catalog view does not involve scanning the table, so buffer gets are minimal.
Until PostgreSQL 15, the statistics collector received updates through a UDP socket and stored the collected data in temporary files, which were regularly written to disk. These files could become quite large and were updated frequently, often within fractions of a second. This approach was a potential bottleneck. In PostgreSQL 15, all statistics changes are initially collected locally on a per-user basis and stored as “pending.” These pending statistics represent data that has been gathered but not yet added to the shared statistics system. After a short delay, typically following a commit or when a timeout occurs, the pending statistics are flushed to shared memory. For more information refer to “How PostgreSQL improved statistics collector in the latest version” by Fujitsu team.
Adventureworks=# \timing
Adventureworks=# create table person.big_table2 as select person.firstname,person.lastname,person.suffix,person.emailpromotion,productinventory.productid, productinventory.locationid, productinventory.quantity
from person.person ,production.productinventory ;
Table created.
Adventureworks=# create table person.big_table3 as select person.firstname,person.lastname,person.suffix,person.emailpromotion,productinventory.productid, productinventory.locationid, productinventory.quantity
from person.person ,production.productinventory ;
Table created.
-- Execute the query several times to stabilize
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
schemaname AS schemaname,
relname AS table_name,
n_live_tup AS num_rows
FROM
pg_stat_all_tables
WHERE
schemaname = 'person'
and relname like 'big_table%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Subquery Scan on pg_stat_all_tables (cost=13.67..13.70 rows=1 width=136) (actual time=0.107..0.116 rows=3 loops=1)
Buffers: shared hit=11
-> GroupAggregate (cost=13.67..13.69 rows=1 width=316) (actual time=0.106..0.114 rows=3 loops=1)
Group Key: c.oid
Buffers: shared hit=11
-> Sort (cost=13.67..13.68 rows=1 width=132) (actual time=0.088..0.089 rows=3 loops=1)
Sort Key: c.oid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=11
-> Nested Loop (cost=0.42..13.66 rows=1 width=132) (actual time=0.053..0.071 rows=3 loops=1)
Join Filter: (c.relnamespace = n.oid)
Buffers: shared hit=11
-> Nested Loop Left Join (cost=0.42..12.48 rows=1 width=72) (actual time=0.039..0.053 rows=3 loops=1)
Buffers: shared hit=8
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..8.30 rows=1 width=72) (actual time=0.031..0.041 rows=3 loops=1)
Index Cond: ((relname >= 'big'::text) AND (relname < 'bih'::text))
Filter: ((relname ~~ 'big_table%'::text) AND (relkind =
ANY ('{r,t,m,p}'::"char"[])))
Buffers: shared hit=5
-> Index Only Scan using pg_index_indrelid_index on pg_index i (cost=0.15..4.17 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=3)
Index Cond: (indrelid = c.oid)
Heap Fetches: 0
Buffers: shared hit=3
-> Seq Scan on pg_namespace n (cost=0.00..1.18 rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=3)
Filter: (nspname = 'person'::name)
Rows Removed by Filter: 4
Buffers: shared hit=3
Planning:
Buffers: shared hit=16
Planning Time: 0.852 ms
Execution Time: 0.185 ms
(30 rows)Retrieving the row counts for three tables does not result in a significant change in performance or resource usage compared to retrieving the count for a single table. (27 block access)
-- Execute the query several times to stabilize
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
ps.schemaname AS schemaname,
ps.relname AS table_name,
ps.n_live_tup AS num_rows
FROM
pg_stat_all_tables ps
INNER JOIN pg_tables pt
ON ps.schemaname = pt.schemaname
AND ps.relname = pt.tablename
WHERE
ps.schemaname IN ('person', 'sales', 'purchasing', 'production', 'humanresources');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Hash Join (cost=50.98..84.61 rows=45 width=168) (actual time=0.859..1.622 rows=71 loops=1)
Hash Cond: ((((((n.nspname)::text || '.'::text) || (c.relname)::text))::regclass)::oid = ps.relid)
Buffers: shared hit=51
-> Hash Join (cost=1.41..32.75 rows=139 width=128) (actual time=0.045..0.339 rows=139 loops=1)
Hash Cond: (c.relnamespace = n.oid)
Buffers: shared hit=22
-> Seq Scan on pg_class c (cost=0.00..30.89 rows=139 width=72) (actual time=0.020..0.258 rows=139 loops=1)
Filter: (relkind = ANY ('{r,p}'::"char"[]))
Rows Removed by Filter: 652
Buffers: shared hit=21
-> Hash (cost=1.18..1.18 rows=18 width=68) (actual time=0.017..0.018 rows=18 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace n (cost=0.00..1.18 rows=18 width=68) (actual time=0.003..0.007 rows=18 loops=1)
Buffers: shared hit=1
-> Hash (cost=48.76..48.76 rows=65 width=140) (actual time=0.762..0.764 rows=73 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 21kB
Buffers: shared hit=29
-> Subquery Scan on ps (cost=47.30..48.76 rows=65 width=140) (actual time=0.650..0.735 rows=73 loops=1)
Buffers: shared hit=29
-> HashAggregate (cost=47.30..48.11 rows=65 width=316) (actual time=0.649..0.719 rows=73 loops=1)
Group Key: c_1.oid, n_1.nspname
Batches: 1 Memory Usage: 48kB
Buffers: shared hit=29
-> Hash Right Join (cost=35.79..46.98 rows=65 width=132) (actual time=0.527..0.595 rows=74 loops=1)
Hash Cond: (i.indrelid = c_1.oid)
Buffers: shared hit=29
-> Seq Scan on pg_index i (cost=0.00..9.87 rows=287 width=4) (actual time=0.004..0.048 rows=287 loops=1)
Buffers: shared hit=7
-> Hash (cost=34.98..34.98 rows=65 width=132) (actual time=0.455..0.457 rows=73 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 20kB
Buffers: shared hit=22
-> Hash Join (cost=1.35..34.98 rows=65 width=132) (actual time=0.046..0.408 rows=73 loops=1)
Hash Cond: (c_1.relnamespace = n_1.oid)
Buffers: shared hit=22
-> Seq Scan on pg_class c_1 (cost=0.00..32.87 rows=233 width=72) (actual time=0.005..0.315 rows=233 loops=1)
Filter: (relkind = ANY ('{r,t,m,p}'::"char"[
]))
Rows Removed by Filter: 558
Buffers: shared hit=21
-> Hash (cost=1.29..1.29 rows=5 width=68) (actual time=0.036..0.036 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace n_1 (cost=0.00..1.29 rows=5 width=68) (actual time=0.005..0.009 rows=5 loops=1)
Filter: (nspname = ANY ('{person,sales,purchasing,production,humanresources}'::name[]))
Rows Removed by Filter: 13
Buffers: shared hit=1
Planning:
Buffers: shared hit=20
Planning Time: 1.031 ms
Execution Time: 1.725 ms
(50 rows)Determining the row counts for all tables in the sample schemas required minimal buffer gets (71 block access), even when dealing with 71 tables, only three of which are significantly large.
Option 2 : Relying on the Autovacuum Daemon to Find Row Counts (pg_class)
This time we will use reltuples column of pg_class view.
In the Official Documentation of PostgreSQL 17 – Chapter 51 – it is identified as:
Number of live rows in the table. This is only an estimate used by the planner. It is updated by
VACUUM,ANALYZE, and a few DDL commands such asCREATE INDEX. If the table has never yet been vacuumed or analyzed, reltuples contains-1indicating that the row count is unknown.
-- Analyze table to populate reltuples value -- not required if PostgreSQL version >= 13 and autovacuum enabled
Adventureworks=# analyze person.big_table;
Adventureworks=# analyze person.big_table2;
Adventureworks=# analyze person.big_table3;
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS) SELECT
relnamespace::regnamespace as schemaname,
relname AS table_name,
reltuples::bigint AS num_rows
FROM
pg_class ps
WHERE
ps.oid = 'person.big_table'::regclass;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Index Scan using pg_class_oid_index on pg_class ps (cost=0.28..8.29 rows=1 width=76) (actual time=0.066..0.069 rows=1 loops=1)
Index Cond: (oid = '19479'::oid)
Buffers: shared hit=3
Planning Time: 0.211 ms
Execution Time: 0.120 ms
(5 rows)This time, With just 3 block access from the shared buffer, we can retrieve the row count information for a single large table efficiently.
In that case, Analyze command is no longer necessary with the introduction of the “Autovacuum for Insert-Only Tables” feature available starting in PostgreSQL version 13 if autovacuum is enabled.
Autovacuum for Insert-Only Tables:
With the parameters autovacuum_vacuum_insert_scale_factor (default: 0.2) and autovacuum_vacuum_insert_threshold (default: 1000), introduced in PostgreSQL version 13, the behavior somewhat resembles Oracle Database 12c’s Online Statistics Gathering (_optimizer_gather_stats_on_load). If the number of newly inserted rows exceeds 20% of the table’s row count and this 20% is greater than 1000 rows, autovacuum will trigger a vacuum and analyze operation for the table. This behavior some extent is similar to Oracle’s approach, where statistics are updated immediately after bulk data loads or direct-path inserts. However, in PostgreSQL, the update occurs during the first autovacuum cycle (default autovacuum_naptime = 1 minute) after the insert operations exceed the defined thresholds.
For three large tables: 19 block access.
-- Execute the query several times to stabilize
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
relnamespace::regnamespace as schemaname,
relname AS table_name,
reltuples::bigint AS num_rows
FROM
pg_tables pt
JOIN pg_class pc ON pc.oid = (pt.schemaname || '.' || pt.tablename)::regclass
WHERE
pt.schemaname = 'person'
AND pt.tablename LIKE 'big_table%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.56..17.87 rows=1 width=76) (actual time=0.088..0.125 rows=3 loops=1)
Buffers: shared hit=17
-> Nested Loop (cost=0.28..9.54 rows=1 width=128) (actual time=0.058..0.079 rows=3 loops=1)
Join Filter: (c.relnamespace = n.oid)
Buffers: shared hit=8
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..8.30 rows=1 width=72) (actual time=0.037..0.048 rows=3 loops=1)
Index Cond: ((relname >= 'big'::text) AND (relname < 'bih'::text))
Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND (relname ~~ 'big_table%'::text))
Buffers: shared hit=5
-> Seq Scan on pg_namespace n (cost=0.00..1.23 rows=1 width=68) (actual time=0.007..0.007 rows=1 loops=3)
Filter: (nspname = 'person'::name)
Rows Removed by Filter: 4
Buffers: shared hit=3
-> Index Scan using pg_class_oid_index on pg_class pc (cost=0.29..8.30 rows=1 width=76) (actual time=0.007..0.007 rows=1 loops=3)
Index Cond: (oid = (((((n.nspname)::text || '.'::text) || (c.relname)::text))::regclass)::oid)
Buffers: shared hit=9
Planning:
Buffers: shared hit=2
Planning Time: 1.013 ms
Execution Time: 0.207 ms
(20 rows)
For all tables (71 tables) : 45 block access.
-- Execute the query several times to stabilize
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
relnamespace::regnamespace as schemaname,
relname AS table_name,
reltuples::bigint AS num_rows
FROM
pg_tables pt
JOIN pg_class pc ON pc.oid = (pt.schemaname || '.' || pt.tablename)::regclass
WHERE
pt.schemaname IN ('person', 'sales', 'purchasing', 'production', 'humanresources');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Hash Join (cost=40.15..71.72 rows=39 width=76) (actual time=0.494..0.855 rows=71 loops=1)
Hash Cond: ((((((n.nspname)::text || '.'::text) || (c.relname)::text))::regclass)::oid = pc.oid)
Buffers: shared hit=43
-> Hash Join (cost=1.35..32.70 rows=39 width=128) (actual time=0.057..0.233 rows=71 loops=1)
Hash Cond: (c.relnamespace = n.oid)
Buffers: shared hit=22
-> Seq Scan on pg_class c (cost=0.00..30.89 rows=139 width=72) (actual time=0.028..0.177 rows=139 loops=1)
Filter: (relkind = ANY ('{r,p}'::"char"[]))
Rows Removed by Filter: 652
Buffers: shared hit=21
-> Hash (cost=1.29..1.29 rows=5 width=68) (actual time=0.017..0.019 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace n (cost=0.00..1.29 rows=5 width=68) (actual time=0.006..0.009 rows=5 loops=1)
Filter: (nspname = ANY ('{person,sales,purchasing,production,humanresources}'::name[]))
Rows Removed by Filter: 13
Buffers: shared hit=1
-> Hash (cost=28.91..28.91 rows=791 width=76) (actual time=0.420..0.420 rows=791 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 92kB
Buffers: shared hit=21
-> Seq Scan on pg_class pc (cost=0.00..28.91 rows=791 width=76) (actual time=0.002..0.241 rows=791 loops=1)
Buffers: shared hit=21
Planning:
Buffers: shared hit=2
Planning Time: 0.555 ms
Execution Time: 0.909 ms
(26 rows)
It is also worth mentioning that increasing the sample size improves the accuracy of the reltuples column, but this comes at the cost of longer execution times for the ANALYZE command and increased storage usage in the pg_statistic system catalog. With the default settings in place (default_statistics_target = 100), the row count is computed by the analyzer randomly sampling 300 × default_statistics_target rows. (300 is a constant choosen according to the some statistical theory(Source Code).) For the default value of 100, this results in a total of 30,000 rows being sampled. Up to 30,000 rows it will provide the exact row count, if table is not changing.
Next, I will try the other method: dynamically executing SELECT COUNT(*) for each table and computing row counts instantly. While this method provides exact counts, it requires scanning the data directly, which may result in increased resource consumption, especially for larger tables. This comparison will help highlight the trade-offs between relying on table statistics and direct query execution for row count retrieaval.
Option 3: Finding the number of rows in each table by using “SELECT COUNT(*) FROM”
First, It’s a common misconception that COUNT(1) might be faster than COUNT(*) because the latter seems to process an entire row. However, this is not the case. In this context, the * is not related to selecting all columns (as in SELECT *) and has no special meaning.
I will begin the testing with a single large table, person.big_table to serve as a baseline for understanding how buffer gets are impacted by the table size. As To ensure a full table scan (sequential scan) on each processed large table, I did not create an index. Once this baseline is established, I will expand the test to include multiple tables to evaluate overall performance overhead.
I will create a temporay function and call it for each table.
Adventureworks=# CREATE FUNCTION pg_temp.get_rows(p_schemaname text, p_tablename text)
RETURNS bigint AS $$
DECLARE
result_rowcount bigint;
BEGIN
EXECUTE format('SELECT COUNT(*) FROM "%I"."%I"', p_schemaname, p_tablename)
INTO result_rowcount;
RETURN result_rowcount;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 13.863 ms
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
schemaname AS schemaname,
tablename AS table_name,
pg_temp.get_rows(schemaname, tablename) AS cnt
FROM
pg_tables
WHERE
schemaname = 'person'
AND tablename = 'big_table';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.28..9.79 rows=1 width=136) (actual time=610.738..610.742 rows=1 loops=1)
Join Filter: (c.relnamespace = n.oid)
Buffers: shared hit=1936 read=147892
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..8.29 rows=1 width=72) (actual time=0.151..0.154 rows=1 loops=1)
Index Cond: (relname = 'big_table'::name)
Filter: (relkind = ANY ('{r,p}'::"char"[]))
Buffers: shared hit=3
-> Seq Scan on pg_namespace n (cost=0.00..1.23 rows=1 width=68) (actual time=0.017..0.017 rows=1 loops=1)
Filter: (nspname = 'person'::name)
Rows Removed by Filter: 4
Buffers: shared hit=1
Planning:
Buffers: shared hit=2
Planning Time: 2.293 ms
Execution Time: 611.062 ms
(15 rows)For a single large table, executing a dynamic query to calculate the row count resulted in 149,830 (2 planning phase , 1936 execution phase shared buffer cache accesses (logical reads) and 147,892 disk reads (physical reads)) total block accesses. In contrast, retrieving the count using table statistics required just 21 (16 planning phase, 5 execution phase) buffer gets. This shows a huge difference of nearly 7134 times more for the retrieving almost same information.
Now, let’s extend this test to three large tables to show the impact more clearly.
Adventureworks=# CREATE FUNCTION pg_temp.get_rows(p_schemaname text, p_tablename text)
RETURNS bigint AS $$
DECLARE
result_rowcount bigint;
BEGIN
EXECUTE format('SELECT COUNT(*) FROM "%I"."%I"', p_schemaname, p_tablename)
INTO result_rowcount;
RETURN result_rowcount;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 13.863 ms
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
schemaname AS schemaname,
tablename AS table_name,
pg_temp.get_rows(schemaname, tablename) AS cnt
FROM
pg_tables
WHERE
schemaname = 'person'
AND tablename like 'big_table%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.28..9.79 rows=1 width=136) (actual time=478.424..935.163 rows=3 loops=1)
Join Filter: (c.relnamespace = n.oid)
Buffers: shared hit=6298 read=443182
-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..8.30 rows=1 width=72) (actual time=0.050..0.097 rows=3 loops=1)
Index Cond: ((relname >= 'big'::text) AND (relname < 'bih'::text))
Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND (relname ~~ 'big_table%'::text))
Buffers: shared hit=5
-> Seq Scan on pg_namespace n (cost=0.00..1.23 rows=1 width=68) (actual time=0.006..0.006 rows=1 loops=3)
Filter: (nspname = 'person'::name)
Rows Removed by Filter: 4
Buffers: shared hit=3
Planning:
Buffers: shared hit=2
Planning Time: 0.726 ms
Execution Time: 935.244 ms
(15 rows)The calculation of 449482 ÷ 27 = 16647 reveals that the when tables are queried directly for finding row count, this method comes with an approximately 16000 times worse performance for this case. Almost the same results with Oracle for three large tables.
| CREATE FUNCTION pg_temp.get_rows(p_schemaname text, p_tablename text) | |
| RETURNS bigint AS $$ | |
| DECLARE | |
| result_rowcount bigint; | |
| BEGIN | |
| EXECUTE format('SELECT COUNT(*) FROM "%I"."%I"', p_schemaname, p_tablename) | |
| INTO result_rowcount; | |
| RETURN result_rowcount; | |
| END; | |
| $$ LANGUAGE plpgsql; | |
| SELECT | |
| schemaname AS schemaname, | |
| tablename AS table_name, | |
| pg_temp.get_rows(schemaname, tablename) AS cnt | |
| FROM | |
| pg_tables | |
| WHERE | |
| schemaname in ('person','sales','purchasing','production','humanresources'); |
Adventureworks=# CREATE FUNCTION pg_temp.get_rows(p_schemaname text, p_tablename text)
RETURNS bigint AS $$
DECLARE
result_rowcount bigint;
BEGIN
EXECUTE format('SELECT COUNT(*) FROM "%I"."%I"', p_schemaname, p_tablename)
INTO result_rowcount;
RETURN result_rowcount;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 13.863 ms
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
schemaname AS schemaname,
tablename AS table_name,
pg_temp.get_rows(schemaname, tablename) AS cnt
FROM
pg_tables
WHERE
schemaname in ('person','sales','purchasing','production','humanresources');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.35..42.64 rows=39 width=136) (actual time=0.261..856.935 rows=71 loops=1)
Hash Cond: (c.relnamespace = n.oid)
Buffers: shared hit=16023 read=443315
-> Seq Scan on pg_class c (cost=0.00..30.89 rows=139 width=72) (actual time=0.025..0.289 rows=139 loops=1)
Filter: (relkind = ANY ('{r,p}'::"char"[]))
Rows Removed by Filter: 652
Buffers: shared hit=21
-> Hash (cost=1.29..1.29 rows=5 width=68) (actual time=0.016..0.016 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace n (cost=0.00..1.29 rows=5 width=68) (actual time=0.006..0.010 rows=5 loops=1)
Filter: (nspname = ANY ('{person,sales,purchasing,production,humanresources}'::name[]))
Rows Removed by Filter: 15
Buffers: shared hit=1
Planning:
Buffers: shared hit=2
Planning Time: 0.611 ms
Execution Time: 856.997 ms
(18 rows)
Additionally, the pg_stat_statements.shared_blks_read and pg_stat_statements.shared_blks_hit columns values verify the results we have observed so far.
Adventureworks=# select * from pg_stat_statements where query like '%pg_temp.get_rows(schemaname, tablename) AS cnt%' ;
userid | 10
dbid | 16388
toplevel | t
queryid | 1298699028538865439
query | EXPLAIN (ANALYZE, BUFFERS) +
| SELECT +
| schemaname AS schemaname, +
| tablename AS table_name, +
| pg_temp.get_rows(schemaname, tablename) AS cnt+
| FROM +
| pg_tables +
| WHERE +
| schemaname in +
| ($1,$2,$3,$4,$5)
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 1
total_exec_time | 2987.850626
min_exec_time | 2987.850626
max_exec_time | 2987.850626
mean_exec_time | 2987.850626
stddev_exec_time | 0
rows | 0
shared_blks_hit | 16025
shared_blks_read | 443315
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
shared_blk_read_time | 0
shared_blk_write_time | 0
local_blk_read_time | 0
local_blk_write_time | 0
temp_blk_read_time | 0
temp_blk_write_time | 0
wal_records | 0
wal_fpi | 0
wal_bytes | 0
jit_functions | 0
jit_generation_time | 0
jit_inlining_count | 0
jit_inlining_time | 0
jit_optimization_count | 0
jit_optimization_time | 0
jit_emission_count | 0
jit_emission_time | 0
jit_deform_count | 0
jit_deform_time | 0
stats_since | 2024-12-02 20:02:59.590654+00
minmax_stats_since | 2024-12-02 20:02:59.590654+00shared_blks_read : 443315 (data coming from the disk or from the operating system cache)
shared_blks_hit : (16023 + 2) =16025 (data coming from the shared buffer)
Instead of creating a function, we can also use query_to_xml function to execute the query whose text is passed as parameter query and maps the result set similar to XMLQUERY in Oracle.
SELECT schemaname,
TABLENAME,
(xpath('/row/num_rows/text()', query_to_xml(format('select count(*) as num_rows from %I.%I', schemaname, TABLENAME), FALSE, TRUE, '')))[1]::text::int AS num_rows
FROM pg_tables pt
WHERE schemaname IN ('person', 'sales', 'purchasing', 'production', 'humanresources')Alternative Approaches:
Option 4: Finding the Number of Rows by using block sampling with TABLESAMPLE
Another method for estimating row counts is block sampling, but its accuracy may decrease if the blocks of the table contain significantly different numbers of rows. Block sampling can be performed using the TABLESAMPLE clause, introduced in PostgreSQL 9.5.
PostgreSQL provides two built-in sampling methods: SYSTEM and BERNOULLI.
SYSTEM sampling selects entire table blocks and uses the rows within them, making it faster but less evenly distributed.
BERNOULLI sampling randomly selects individual rows from the entire table, offering a more uniform distribution but at a higher performance cost.
The following query demonstrates using SYSTEM sampling with a 10% of the rows.
Adventureworks=# CREATE FUNCTION pg_temp.get_rows_sample(p_schemaname text, p_tablename text)
RETURNS bigint AS $$
DECLARE
result_rowcount bigint;
BEGIN
EXECUTE format('SELECT COUNT(*) FROM "%I"."%I" TABLESAMPLE SYSTEM (10)', p_schemaname, p_tablename)
INTO result_rowcount;
RETURN result_rowcount * 10;
END;
$$ LANGUAGE plpgsql;
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
schemaname AS schemaname,
tablename AS table_name,
pg_temp.get_rows_sample(schemaname, tablename) AS cnt
FROM
pg_tables
WHERE
schemaname in ('person','sales','purchasing','production','humanresources');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.35..42.64 rows=39 width=136) (actual time=2.165..767.138 rows=71 loops=1)
Hash Cond: (c.relnamespace = n.oid)
Buffers: shared hit=1672 read=44662
-> Seq Scan on pg_class c (cost=0.00..30.89 rows=139 width=72) (actual time=0.039..0.203 rows=139 loops=1)
Filter: (relkind = ANY ('{r,p}'::"char"[]))
Rows Removed by Filter: 652
Buffers: shared hit=21
-> Hash (cost=1.29..1.29 rows=5 width=68) (actual time=0.023..0.024 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace n (cost=0.00..1.29 rows=5 width=68) (actual time=0.008..0.012 rows=5 loops=1)
Filter: (nspname = ANY ('{person,sales,purchasing,production,humanresources}'::name[]))
Rows Removed by Filter: 15
Buffers: shared hit=1
Planning:
Buffers: shared hit=2
Planning Time: 1.539 ms
Execution Time: 767.302 ms
(18 rows)This method is a little bit limited can be useful in scenarios where the statistics in pg_class are outdated or insufficient, such as when autovacuum is not running or when dealing with temporary tables.
Option 5: Acting like Planner
By the way, the PostgreSQL planner uses a calculation to estimate the number of rows (as seen in the source code) that is conceptually similar to the one provided below. It simply multiplies reltuples/relpages by the current number of pages.
-- https://stackoverflow.com/questions/7943233/fast-way-to-discover-the-row-count-of-a-table-in-postgresql
-- Erwin Brandstetter
-- Minimal Form
Adventureworks=# SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM pg_class
WHERE oid = 'mytable'::regclass;
-- Safe and explicit
Adventureworks=# SELECT (CASE WHEN c.reltuples < 0 THEN NULL -- never vacuumed
WHEN c.relpages = 0 THEN float8 '0' -- empty table
ELSE c.reltuples / c.relpages END
* (pg_catalog.pg_relation_size(c.oid)
/ pg_catalog.current_setting('block_size')::int)
)::bigint
FROM pg_catalog.pg_class c
WHERE c.oid = 'myschema.mytable'::regclass; -- schema-qualified table here
This method can also be particularly useful for large tables. As the sample size of data increases, the average number of rows per physical page tends to stabilize more reliably compared to the total row count. By multiplying this average rows-per-page value by the most recent information about the current number of pages occupied by the table.
Adventureworks=# EXPLAIN (ANALYZE, BUFFERS)
SELECT
pt.schemaname AS schemaname,
pc.relname AS table_name,
(CASE WHEN pc.reltuples < 0 THEN NULL
WHEN pc.relpages = 0 THEN float8 '0'
ELSE pc.reltuples / pc.relpages END
* (pg_catalog.pg_relation_size(pc.oid)
/ pg_catalog.current_setting('block_size')::int)
)::bigint as num_rows
FROM
pg_tables pt
JOIN pg_class pc ON pc.oid = (pt.schemaname || '.' || pt.tablename)::regclass
WHERE
pt.schemaname IN ('person', 'sales', 'purchasing', 'production', 'humanresources');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Hash Join (cost=40.15..72.79 rows=39 width=136) (actual time=0.608..1.633 rows=71 loops=1)
Hash Cond: ((((((n.nspname)::text || '.'::text) || (c.relname)::text))::regclass)::oid = pc.oid)
Buffers: shared hit=43
-> Hash Join (cost=1.35..32.70 rows=39 width=128) (actual time=0.056..0.244 rows=71 loops=1)
Hash Cond: (c.relnamespace = n.oid)
Buffers: shared hit=22
-> Seq Scan on pg_class c (cost=0.00..30.89 rows=139 width=72) (actual time=0.028..0.188 rows=139 loops=1)
Filter: (relkind = ANY ('{r,p}'::"char"[]))
Rows Removed by Filter: 652
Buffers: shared hit=21
-> Hash (cost=1.29..1.29 rows=5 width=68) (actual time=0.017..0.019 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on pg_namespace n (cost=0.00..1.29 rows=5 width=68) (actual time=0.006..0.010 rows=5 loops=1)
Filter: (nspname = ANY ('{person,sales,purchasing,production,humanresources}'::name[]))
Rows Removed by Filter: 15
Buffers: shared hit=1
-> Hash (cost=28.91..28.91 rows=791 width=76) (actual time=0.467..0.467 rows=791 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 92kB
Buffers: shared hit=21
-> Seq Scan on pg_class pc (cost=0.00..28.91 rows=791 width=76) (actual time=0.003..0.270 rows=791 loops=1)
Buffers: shared hit=21
Planning:
Buffers: shared hit=2
Planning Time: 0.689 ms
Execution Time: 1.704 ms
(26 rows)There is also a generic implementation of this logic available for all the queries you want to execute. You can use the planner to return estimated rows without executing the query, which can be found in detail on the Count Estimate page of the PostgreSQL Wiki explained in detail.
CREATE OR REPLACE FUNCTION count_estimate(
query text
) RETURNS integer LANGUAGE plpgsql AS $$
DECLARE
plan jsonb;
BEGIN
EXECUTE FORMAT('EXPLAIN (FORMAT JSON) %s', query) INTO plan;
RETURN plan->0->'Plan'->'Plan Rows';
END;
$$;
SELECT count_estimate('SELECT * FROM person.big_table');The Results:
Depesz’s explain plan visualization provides a much clearer and more intuitive representation of query execution plans, making it easier to understand database performance.
Option 1 : Relying on Stats Collector: 66 Total Block Accesses.
Option 2 : Relying on Autovacuum Daemon: 45 Total Block Accesses
Option 3 : Using Select Count(*) from – Direct Query : 459,340 Total Block Accesses
Option 4 : Using Block Sampling with TABLESAMPLE : 45,886 Total Block Accesses
Option 5 : Acting Like Planner: 45 Total Block accesses : 45 Total Block Accesses
Conclusion
This analysis highlights the trade-offs between using table statistics and directly querying row counts with SELECT COUNT(*). While table statistics provide a quick and efficient way to estimate row counts with minimal resource consumption, their accuracy depends on how recently and comprehensively the statistics were gathered. On the other side, while direct row count queries offer precise results but come at a significant performance cost due to the need for full table scans, index full scans and index fast full scans, especially for large tables or when multiple tables are queried.
Using dictionary views like pg_class and pg_stat_all_tables, and relying on the information provided by the Stats Collector and Autovacuum Daemon, performed much better than directly querying each table.
In our tests on PostgreSQL v17 with the sample database (Adventureworks);
Direct row count queries with SELECT COUNT(*) resulted in 149,832 block accesses for a single table with 21 million records and up to 449,482 block gets for three large tables: an overhead of approximately 16,000 times compared to using the n_live_tup value from pg_stat_all_tables, which relies on the stats collector, and about 23,000 times when compared to using the reltuples value from pg_class, which depends on the autovacuum daemon.
This technical perspective demonstrates that, while direct row count queries can be useful in certain scenarios, their heavy performance impact makes them unsuitable for almost all operations, particularly in large-scale environments with tables containing millions of rows. Relying on up-to-date table statistics is the more practical choice for most applications, especially when performance are key priorities.
Option 1, Option 2, and Option 5 all yielded similar results. In my humble opinion, if autovacuum is enabled (as it should be, sticking with default settings is generally advisable), mimicking the planner’s logic by multiplying reltuples/relpages with the current number of pages (retrieved via pg_relation_size) for estimating row counts seems like a practical and effective choice.
As a best practice, consider the following; Enable autovacuum and leverage autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor parameters to keep statistics accurate and reduce the need for manual vacuum operations.
Hope it helps.
Osman’s DBlog 
Leave your comment