Paris, June 24 2015
DALIBO is proud to present the first release of HypoPG, an extension that adds hypothetical indexes in PostgreSQL.
A hypothetical index is an index that doesn't exists on disk. It's therefore almost instant to create and doesn't add any IO cost, whether at creation time or at maintenance time. The goal is obviously to check if an index is useful before spending too much 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.
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 !
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.