Database

HypoPG: Hypothetical indexes

HypoPG is PostgreSQL extension for creating hypothetical/virtual indexes. HypoPG allows users to rapidly create hypothetical/virtual indexes that have no resource cost (CPU, disk, memory) that are visible to the PostgreSQL query planner.

The motivation for HypoPG is to allow users to quickly search for an index to improve a slow query without consuming server resources or waiting for them to build.

Enable the extension

Speeding up a query

Given the following table and a simple query to select from the table by id:


_11
create table account (
_11
id int,
_11
address text
_11
);
_11
_11
insert into account(id, address)
_11
select
_11
id,
_11
id || ' main street'
_11
from
_11
generate_series(1, 10000) id;

We can generate an explain plan for a description of how the PostgreSQL query planner intends to execute the query.


_10
explain select * from account where id=1;
_10
_10
QUERY PLAN
_10
-------------------------------------------------------
_10
Seq Scan on account (cost=0.00..180.00 rows=1 width=13)
_10
Filter: (id = 1)
_10
(2 rows)

Using HypoPG, we can create a hypothetical index on the account(id) column to check if it would be useful to the query planner and then re-run the explain plan.

Note that the virtual indexes created by HypoPG are only visible in the PostgreSQL connection that they were created in. Supabase connects to PostgreSQL through a connection pooler so the hypopg_create_index statement and the explain statement should be executed in a single query.


_10
select * from hypopg_create_index('create index on account(id)');
_10
_10
explain select * from account where id=1;
_10
_10
QUERY PLAN
_10
------------------------------------------------------------------------------------
_10
Index Scan using <13504>btree_account_id on hypo (cost=0.29..8.30 rows=1 width=13)
_10
Index Cond: (id = 1)
_10
(2 rows)

The query plan has changed from a Seq Scan to an Index Scan using the newly created virtual index, so we may choose to create a real version of the index to improve performance on the target query:


_10
create index on account(id);

Functions

Resources