Introducing HypoPG, hypothetical indexes for PostgreSQL

# Introducing HypoPG

Paris, June 24 2015

DALIBO is proud to present the first release of HypoPG, an extension
that adds hypothetical indexes in PostgreSQL.

An hypothetical index is an index which doesn't exists on disk. It's
thefore almost instant to create and doesn't add any IO cost, wether
at creation time or at maintenance time. The goal is obviously to
check if an index is useful before spending many time, I/O and disk
space to create it.

With this extension, you can create hypothetical indexes, and then
with EXPLAIN check if PostgreSQL would use them or not.

## " What if I had an Index on this ? "

Did you ever wonder how an index would increase the performances of
your server, but you couldn't afford the time to create it on disk
just for the sake of trying ?

Here's how HypoPG can help:

First let's create a simple use case:

# CREATE TABLE testable AS SELECT id, 'line ' || id val FROM
generate_series(1,1000000) id;
# ANALYZE testable ;

Now let's install HypoPG and create an hypothetical index on this new
table

# CREATE EXTENSION hypopg;
# SELECT hypopg_create_index('CREATE INDEX ON testable (id)');

You can now use EXPLAIN (without ANALYZE) to check if PostgreSQL would
use that index !

# EXPLAIN SELECT * FROM testable WHERE id = 1000 ;
QUERY PLAN

-----------------------------------------------------------------------------------------------
Index Scan using <41079>btree_testable_id on testable
(cost=0.05..8.07 rows=1 width=15)
Index Cond: (id = 1000)
(2 rows)

Yay ! If there were an index on the 'id' column, PostgreSQL would take
advantage of it !

## Links

* Repository : https://github.com/dalibo/hypopg
* Install with PGXN : http://pgxn.org/dist/hypopg

## About DALIBO :

DALIBO is the leading PostgreSQL company in France, providing support,
trainings and consulting to its customers since 2005. The company
contributes to the PostgreSQL community in various ways, including :
code, articles, translations, free conferences and workshops

Check out DALIBO's open source projects at http://dalibo.github.io.

http://www.dalibo.com

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org