pg_regresql

pg_regresql : Trust pg_class statistics for planning instead of physical relation size

Overview

Packages

Source

pig build pkg pg_regresql;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_regresql;		# install via package name, for the active PG version

pig install pg_regresql -v 18;   # install for PG 18
pig install pg_regresql -v 17;   # install for PG 17
pig install pg_regresql -v 16;   # install for PG 16
pig install pg_regresql -v 15;   # install for PG 15
pig install pg_regresql -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_regresql';

Create this extension with:

CREATE EXTENSION pg_regresql;

Usage

Sources: extension README, control file, portable stats article

pg_regresql is a PostgreSQL extension that makes the planner trust catalog statistics from pg_class instead of recomputing relation size from physical file blocks. It is the extension part of the RegreSQL project, intended for realistic plan regression testing with injected production statistics.

Problem

The upstream extension README explains that PostgreSQL normally does not fully trust pg_class.relpages and pg_class.reltuples when estimating relation size. Instead, planner code reads the current physical file size and rescales statistics from that.

That behavior is useful for stale-statistics safety, but it breaks test setups where catalog statistics were intentionally restored from another environment and the local table files are much smaller.

What It Overrides

pg_regresql hooks into get_relation_info_hook after estimate_rel_size() and replaces planner estimates with catalog values.

Installation

The upstream README documents three installation paths:

sudo pgxn install pg_regresql

make PG_SOURCE=/path/to/postgresql
make install PG_SOURCE=/path/to/postgresql

make USE_PGXS=1
make install USE_PGXS=1

The control file ships as pg_regresql.control with default_version = '2.0' and module_pathname = '$libdir/pg_regresql'.

Activation

The extension becomes active when the shared library is loaded:

LOAD 'pg_regresql';

EXPLAIN SELECT ...;

For a whole test instance, the README recommends:

session_preload_libraries = 'pg_regresql'

This is the important runtime configuration: package installation alone is not the point; the planner hook only takes effect after the library is loaded for the session or instance.

Typical Workflow

The main use case is plan regression testing with restored production statistics. After injecting catalog statistics into a CI or test database, pg_regresql makes the planner use those restored values instead of the tiny local heap size.

The README gives this example:

SELECT pg_restore_relation_stats(
    'schemaname', 'public',
    'relname', 'test_orders',
    'relpages', 123513::integer,
    'reltuples', 50000000::real,
    'relallvisible', 123513::integer
);

LOAD 'pg_regresql';

EXPLAIN SELECT * FROM test_orders WHERE created_at > '2024-06-01';

That pattern is useful for:

• reproducing production plans locally
• testing schema migrations against realistic plan estimates
• simulating table growth and index choices
• improving partition-planning experiments

Compatibility

• PostgreSQL 14 and newer in this repository packaging
• upstream README notes the hook itself exists since PostgreSQL 8.3
• intended to coexist with extensions such as pg_hint_plan and hypopg, though upstream marks that as not yet fully tested