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
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for
hypopg
and enable the extension.
Speeding up a query
Given the following table and a simple query to select from the table by id
:
We can generate an explain plan for a description of how the PostgreSQL query planner intends to execute the query.
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.
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:
Functions
hypo_create_index(text)
: A function to create a hypothetical index.hypopg_list_indexes
: A View that lists all hypothetical indexes that have been created.hypopg()
: A function that lists all hypothetical indexes that have been created with the same format aspg_index
.hypopg_get_index_def(oid)
: A function to display thecreate index
statement that would create the index.hypopg_get_relation_size(oid)
: A function to estimate how large a hypothetical index would be.hypopg_drop_index(oid)
: A function to remove a given hypothetical index byoid
.hypopg_reset()
: A function to remove all hypothetical indexes.
Resources
- Official HypoPG documentation