Extensions
- pg_regresql 2.2.0
- Trust pg_class statistics for query planning instead of physical file size; restore dumped statistics on 16/17
Documentation
- README
- PostgreSQL code
README
Contents
pg_regresql
PostgreSQL extension to force planner to use pg_class statistics instead of the estimates from the physical file size. This provides last-mile for RegreSQL query regression testing.
The Problem
As descibed in article Production query plans without production data planner ignores the relpages/reltuples stored in pg_class when checking the relation size.
Instead it calls smgrnblocks() to get the actual physical file size and scales statistics proportionally.
The reasononing for this is to avoid stale statistics (which can happen for example after TRUNCATE). The decision seems to be to use disk size being more reliable than potentially outdated catalog stats.
What pg_regresql Overrides
The extension hooks into get_relation_info_hook (fires after estimate_rel_size()) and replaces the planner’s physical-size estimates with catalog values.
| Planner field | Default source | pg_regresql source |
|---|---|---|
rel->pages |
smgrnblocks() via tableam |
pg_class.relpages |
rel->tuples |
density × physical pages | pg_class.reltuples |
rel->allvisfrac |
relallvisible / physical pages |
pg_class.relallvisible / relpages |
IndexOptInfo->pages |
RelationGetNumberOfBlocks() |
pg_class.relpages (index) |
IndexOptInfo->tuples |
copied from rel->tuples |
pg_class.reltuples (index) |
Installation
From PGXN
sudo pgxn install pg_regresql
From PostgreSQL source tree
# Point PG_SOURCE at your PostgreSQL source (must be configured)
make PG_SOURCE=/path/to/postgresql
make install PG_SOURCE=/path/to/postgresql
With PGXS (installed PostgreSQL)
make USE_PGXS=1
make install USE_PGXS=1
Usage
LOAD 'pg_regresql';
EXPLAIN SELECT ..
For all sessions on a test instance, add to postgresql.conf:
session_preload_libraries = 'pg_regresql'
Restoring statistics on PostgreSQL 16 and 17
CREATE EXTENSION pg_regresql; adds the pg_restore_* functions pg_dump 18
and later call, plus pg_clear_extended_stats(). Then load the dump, or just
its statistics:
pg_dump --statistics-only -f stats.sql ...
psql -f stats.sql
On 16, drop the SET transaction_timeout = 0; line first. From 2.0, drop and
recreate the extension; from 2.1, ALTER EXTENSION pg_regresql UPDATE. On 18+
nothing is created, core has the functions.
2.2 adds pg_restore_extended_stats() and pg_clear_extended_stats() from
PostgreSQL 19, and makes '[{"attributes" : [11,14], "ndistinct" : 24}]'::pg_ndistinct
and the pg_dependencies equivalent work on 16/17, which is what pg_dump 19
emits. n_distinct and dependencies also accept plain text. The two input
functions are detached from the extension, so DROP EXTENSION leaves the casts
working (and the functions behind, they are named regresql_pg_*_in). Output of
those types stays in the 16/17 format. The test is
pg_ext/test/ext_stats/run.sh.
Use Cases
The primary use case is SQL query plan regression testing. Inject production statistics into a CI/test database and compare EXPLAIN output across schema migrations or PostgreSQL upgrades.
-- restore the stats
SELECT pg_restore_relation_stats(
'schemaname', 'public',
'relname', 'test_orders',
'relpages', 123513::integer,
'reltuples', 50000000::real,
'relallvisible', 123513::integer
);
EXPLAIN SELECT * FROM test_orders WHERE created_at > '2024-06-01';
Other use cases are: - reproducing production query plans locally - simulating table growth and index strategy (what if scenarios) - partition planing
Compatibility
- PostgreSQL 15+ for the planner hook (
get_relation_info_hook) - Stats restore functions: 16 and 17 only; 18+ has the relation and attribute ones in core (extended ones only from 19)
- Should work with
pg_hint_plan,hypopg, and other hook-based extensions (not tested for now)
License
BSD 2-Clause License
Copyright © 2026 Radim Marek radim@boringsql.com