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 the PostgreSQL planner-hook part of RegreSQL. It makes the planner trust pg_class statistics instead of rescaling estimates from the physical file size, which is useful for plan regression testing with injected production statistics.

Activate the hook

LOAD 'pg_regresql';

EXPLAIN SELECT ...;

For a whole test instance, upstream recommends:

session_preload_libraries = 'pg_regresql'

What it overrides

The upstream README says the hook runs after estimate_rel_size() and replaces planner estimates with catalog values:

• rel->pages from pg_class.relpages
• rel->tuples from pg_class.reltuples
• rel->allvisfrac from pg_class.relallvisible / relpages
• IndexOptInfo->pages from index pg_class.relpages
• IndexOptInfo->tuples from index pg_class.reltuples

Typical workflow

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';

This is meant for reproducing production plans locally and for migration or upgrade plan-regression tests.

Notes

• The control file currently declares default_version = '2.0'.
• The public repository tags visible upstream are still v2.0.0-alpha*, so the packaged 2.0.0 target is ahead of a clearly tagged final GitHub release.
• Upstream documents PostgreSQL 14+ compatibility for the extension.