hypopg

Overview

Version

Install

You can install hypopg directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

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

• Install
• pig
• dnf
• apt

pig install hypopg;          # Install for current active PG version

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

dnf install -y hypopg_18       # PG 18
dnf install -y hypopg_17       # PG 17
dnf install -y hypopg_16       # PG 16
dnf install -y hypopg_15       # PG 15
dnf install -y hypopg_14       # PG 14

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

Create Extension:

CREATE EXTENSION hypopg;

Usage

hypopg: Hypothetical indexes for PostgreSQL

HypoPG lets you create hypothetical (virtual) indexes that exist only in the current session and are considered by EXPLAIN (without ANALYZE) for query planning. This enables testing the impact of indexes without the cost of actually creating them.

Functions

Workflow

Create a test table and check the baseline plan:

CREATE TABLE hypo AS SELECT id, 'line ' || id AS val FROM generate_series(1, 10000) id;
ANALYZE hypo;
EXPLAIN SELECT * FROM hypo WHERE id = 1;
-- Seq Scan on hypo (cost=0.00..170.00 rows=1 width=15)

Create a hypothetical index:

SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');
--  indexrelid |      indexname
-- ------------+----------------------
--       13543 | <13543>btree_hypo_id

Check the plan with the hypothetical index:

EXPLAIN SELECT * FROM hypo WHERE id = 1;
-- Index Scan using <13543>btree_hypo_id on hypo (cost=0.04..8.06 rows=1 width=15)

List and manage hypothetical indexes:

SELECT * FROM hypopg_list_indexes();
SELECT * FROM hypopg_drop_index(13543);
SELECT * FROM hypopg_reset();

Limitations

• Only EXPLAIN without ANALYZE will consider hypothetical indexes
• Hypothetical indexes exist only in the current backend session
• Other concurrent connections are not affected
• Index names and some CREATE INDEX options are ignored