supabase/apps/docs/content/guides/database/extensions/hypopg.mdx

---
id: 'hypopg'
title: 'HypoPG: Hypothetical indexes'
description: 'Quickly check if an index can be used without creating it.'
---

`HypoPG` is Postgres 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 Postgres query planner.

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

## Enable the extension

<Tabs
  scrollable
  size="small"
  type="underlined"
  defaultActiveId="dashboard"
  queryGroup="database-method"
>
<TabPanel id="dashboard" label="Dashboard">

1. Go to the [Database](/dashboard/project/_/database/tables) page in the Dashboard.
2. Click on **Extensions** in the sidebar.
3. Search for `hypopg` and enable the extension.

</TabPanel>
<TabPanel id="sql" label="SQL">

{/* prettier-ignore */}
```sql
-- Enable the "hypopg" extension
create extension hypopg with schema extensions;

-- Disable the "hypopg" extension
drop extension if exists hypopg;
```

Even though the SQL code is `create extension`, this is the equivalent of enabling the extension.
To disable an extension you can call `drop extension`.

It's good practice to create the extension within a separate schema (like `extensions`) to keep the `public` schema clean.

</TabPanel>
</Tabs>

### Speeding up a query

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

{/* prettier-ignore */}
```sql
create table account (
  id int,
  address text
);

insert into account(id, address)
select
  id,
  id || ' main street'
from
  generate_series(1, 10000) id;
```

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

{/* prettier-ignore */}
```sql
explain select * from account where id=1;

                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on account  (cost=0.00..180.00 rows=1 width=13)
   Filter: (id = 1)
(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 Postgres connection that they were created in. Supabase connects to Postgres through a connection pooler so the `hypopg_create_index` statement and the `explain` statement should be executed in a single query.

{/* prettier-ignore */}
```sql
select * from hypopg_create_index('create index on account(id)');

explain select * from account where id=1;

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using <13504>btree_account_id on hypo  (cost=0.29..8.30 rows=1 width=13)
   Index Cond: (id = 1)
(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:

{/* prettier-ignore */}
```sql
create index on account(id);
```

## Functions

- [`hypo_create_index(text)`](https://hypopg.readthedocs.io/en/rel1_stable/usage.html#create-a-hypothetical-index): A function to create a hypothetical index.
- [`hypopg_list_indexes`](https://hypopg.readthedocs.io/en/rel1_stable/usage.html#manipulate-hypothetical-indexes): A View that lists all hypothetical indexes that have been created.
- [`hypopg()`](https://hypopg.readthedocs.io/en/rel1_stable/usage.html#manipulate-hypothetical-indexes): A function that lists all hypothetical indexes that have been created with the same format as `pg_index`.
- [`hypopg_get_index_def(oid)`](https://hypopg.readthedocs.io/en/rel1_stable/usage.html#manipulate-hypothetical-indexes): A function to display the `create index` statement that would create the index.
- [`hypopg_get_relation_size(oid)`](https://hypopg.readthedocs.io/en/rel1_stable/usage.html#manipulate-hypothetical-indexes): A function to estimate how large a hypothetical index would be.
- [`hypopg_drop_index(oid)`](https://hypopg.readthedocs.io/en/rel1_stable/usage.html#manipulate-hypothetical-indexes): A function to remove a given hypothetical index by `oid`.
- [`hypopg_reset()`](https://hypopg.readthedocs.io/en/rel1_stable/usage.html#manipulate-hypothetical-indexes): A function to remove all hypothetical indexes.

## Resources

- Official [HypoPG documentation](https://hypopg.readthedocs.io/en/rel1_stable/)