This is an introduction to the hypopg PostgreSQL extension for YugabyteDB 2.15.3.0. Hypopg allows the creation of hypothetical indexes, so indexes that do not really exist. This means this allows you to see what an index would do if it were created, without it actually being created, and therefore not influencing anything on the database. YugabyteDB 2.15.3.0 is a preview version of the YugabyteDB database.

The description from the documentation:

An hypothetical -- or virtual -- index is an index that doesn't really exists, and thus doesn't cost CPU, disk or any resource to create. They're useful to know if specific indexes can increase performance for problematic queries, since you can know if PostgreSQL will use these indexes or not without having to spend resources to create them.

Installation

In YugabyteDB 2.15.3.0 the dynamic loadable library for the extension is already put in place. The only thing that needs to be done to add support for hypopg in the database is:

create extension hypopg;

Usage

(if you want to test this using the examples in this post, go to 'Setup test table' at the bottom of this post)

The up and down table has no indexes, but because it's defined with a primary key, it is ordered by, and the records can directly be retrieved using, the primary key:

yugabyte=# explain select * from up_and_down where up = 999;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using up_and_down_pkey on up_and_down  (cost=0.00..4.11 rows=1 width=8)
   Index Cond: (up = 999)

If we need to fetch something from the second field, this will result in a seqscan, because there is no index or other structure to directly lead us to a value in the 'down' field:

yugabyte=# explain select * from up_and_down where down = 999;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on up_and_down  (cost=0.00..102.50 rows=1000 width=8)
   Filter: (down = 999)

What if we would create an index for the down field? To see what that means, we can use hypopg:

yugabyte=# select * from hypopg_create_index('create index on up_and_down(down)');
 indexrelid |          indexname
------------+-----------------------------
      13283 | <13283>lsm_up_and_down_down

Now let's see what explain says:

yugabyte=# explain select * from up_and_down where down = 999;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Index Scan using <13283>lsm_up_and_down_down on up_and_down  (cost=0.00..4.01 rows=1000 width=8)
   Index Cond: (down = 999)

It would use it!

But the index is not really created, if you use 'explain analyze', postgres will actual run the SQL, and because the index does not exist, it will not take the hypothetical index into account:

yugabyte=# explain analyze select * from up_and_down where down = 999;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on up_and_down  (cost=0.00..102.50 rows=1000 width=8) (actual time=35.678..35.687 rows=1 loops=1)
   Filter: (down = 999)
   Rows Removed by Filter: 9999
 Planning Time: 0.041 ms
 Execution Time: 35.735 ms
 Peak Memory Usage: 0 kB

You can query the hypothetical indexes you created using the hypopg() function:

yugabyte=# select * from hypopg();
          indexname          | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
-----------------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <13283>lsm_up_and_down_down |      13283 |    16927 |       1 | f           | 2      | 0            | 9942     |           |          |         | 9900

And if you created multiple hypothetical indexes, you can drop a single hypothetical index using its indexrelid:

yugabyte=# select * from hypopg_drop_index(13283);
 hypopg_drop_index
-------------------
 t

This allows you to remove one of the hypothetical indexes if you created multiple. If you want to remove all of them, just logout/terminate the backend, and the hypothetical indexes are gone, because they didn't really exist outside of the backend.

Warning

Hypothetical indexes are a preview feature in 2.15.3.0. It will be made available for production usage in a future version.

Also, the index support in hypopg currently is basic. Even fairly normal things like including columns with an index is not supported with hypopg, let alone non-default index types.

PostgreSQL

The hypopg extension is a PostgreSQL extension, so this extension can obviously also be used on PostgreSQL. To do that, you should add the extension to your PostgreSQL installation, and make sure the library is loaded for the system functionality.

Setup test table

create table up_and_down (up int primary key, down int);
insert into up_and_down select a as up, 10001-a as down from generate_series(1,10000) a;