Nous vivons une époque de changement, où les entreprises doivent …

Read More

I have just heard of a massive outage that a localized IaaS Cloud Service Provider is experiencing: they have been down (at the time I am drafting this short blog post) 4 days and counting. When I get to publish this it may be they have been down 4 days or… god . . . → Read More: (IaaS) Cloud Outages: Get Over It

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 : github.com/dalibo/hypopg
• Install with PGXN : 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

Estamos viviendo en una época de disrupción, con …

Read More

As you’ll have seen on the blog earlier in the …

Read More

„Bei Hybridität geht es um das Einreißen von Barrieren und …

Read More