pg_regresql

Overview

Package version 2.1.0 installs SQL version 2.0; activate with LOAD pg_regresql or session_preload_libraries.

Version

Build

You can build the RPM / DEB packages for pg_regresql using pig build:

pig build pkg pg_regresql         # build RPM / DEB packages

Install

You can install pg_regresql directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install pg_regresql;          # Install for current active PG version

pig ext install -y pg_regresql -v 18  # PG 18
pig ext install -y pg_regresql -v 17  # PG 17
pig ext install -y pg_regresql -v 16  # PG 16
pig ext install -y pg_regresql -v 15  # PG 15
pig ext install -y pg_regresql -v 14  # PG 14

dnf install -y pg_regresql_18       # PG 18
dnf install -y pg_regresql_17       # PG 17
dnf install -y pg_regresql_16       # PG 16
dnf install -y pg_regresql_15       # PG 15
dnf install -y pg_regresql_14       # PG 14

apt install -y postgresql-18-pg-regresql   # PG 18
apt install -y postgresql-17-pg-regresql   # PG 17
apt install -y postgresql-16-pg-regresql   # PG 16
apt install -y postgresql-15-pg-regresql   # PG 15
apt install -y postgresql-14-pg-regresql   # PG 14

Preload:

shared_preload_libraries = 'pg_regresql';

Create Extension:

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.