Learning PostgreSQL 11 - Third Edition

Indexing an hstore

An hstore data type can be indexed using the GIN and GIST indexes, and picking the right index type depends on several factors, such as the number of rows in the table, available space, index search and update performance, and the queries pattern. To properly pick up the right index, it's good to perform benchmarking.

The following example shows the effect of using the GIN index to retrieve a record that has a certain key. The ? operator returns true if hstore contains a key:

CREATE INDEX ON features USING GIN (features);

The index function can be tested by using the following code:

SET enable_seqscan to off;car_portal=# EXPLAIN SELECT features->'Engine' FROM features WHERE features ? 'Engine';                                     QUERY PLAN ------------------------------------------------------------------------------------ ...