Postgresql 9.3: Creating an index on a JSON attribute

Recently I’ve discovered some very interesting new features in the PostgreSQL 9.3 database.
First of all, a new data type has been introduced: JSON. Together with this new data type, new functions were also introduced.

These new features now simply for instance saving web forms in your Postgresql database. Or actually any kind of dynamic data, such as for instance Perl hashes. Plus, thanks to the new JSON functions, this data can be easily searched and indexed.

Let’s start with creating a test table.

I’ve inserted into this table 100k rows of test data with a very simple Perl script.

Now let’s assume that the JSON data we are going to insert (or have inserted) always contains the attribute field ‘name’. On this attribute we will create the following database index:

The above example creates a multi-column index.

Now let’s a make our first test.
The first test will not use the index we have created previously.

130ms for searching through 100k rows, is actually quite ok.

Now let’s see how we can speed up this query by using the index we’ve created.

0.084ms! That’s is about 1625 times faster! What makes this index extremely interesting is that the index has only been created on one attribute of the JSON data and not on the entire JSON data. This will keep the index data small and thus will be kept longer in your database’ memory.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.