3

I'm playing around with postgresql 9.3's hstore. I'm trying to use and index for an hstore column just like documentation states. MY problem is that the index appear not to be used. Let me give you an example:

I created a table 'Person':

=# CREATE TABLE Person (Id BIGSERIAL PRIMARY KEY NOT NULL, Values hstore);

And inserted a test value:

=# INSERT INTO Person (Values, 'a=>1,b=>3');

Then if I EXPLAIN a SELECT query which uses operator "@>" on 'Values' column, I unsurprisingly get:

=# EXPLAIN SELECT P.* FROM Person AS P WHERE P.Values @> hstore('a', '1');
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on person p  (cost=0.00..24.50 rows=1 width=40)
   Filter: ("values" @> '"a"=>"1"'::hstore)

No index < - > sequential scan. Makes sense. Anyway, it doesn't matter if I create a GIN or GIST index, the explain keeps talking about sequential scan:

=# CREATE INDEX IX_GIN_VALUES ON Person USING GIN (values);
CREATE INDEX

=# EXPLAIN SELECT P.* FROM Person P WHERE P.values @> hstore('a', '1');

                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on person p  (cost=0.00..1.01 rows=1 width=246)
   Filter: ("values" @> '"age"=>"2"'::hstore)

Maybe I'm missing something obvious?

Fabzter
  • 505
  • 5
  • 16

2 Answers2

7

If you're just playing with it, be sure to add enough data for the index scan to make sense. If you only have a few rows, or if many row contains similar values (i.e. your where criteria isn't selective enough), a seq scan will usually be faster than an index scan.

Also, be sure to analyze your table after filling it with your test data.

Some extra reading for @maxm:

(Performance has greatly improved since the latter was written.)

Why isn't his/her index being used?

Because it's faster for Postgres to seq scan the entire table (which has a single row) and filter the row out of a single disk page, than it is to do the index look up, and then seq scan the table likewise in order to retrieve the row's data.

Is there an issue w/ how the asker is creating their index?

None, but see the above link on when it's better to use normalized data.

And prefer json or jsonb rather than hstore.

Querying the hstore column? What needs to be fixed so that the SELECT query uses such an index?

Nothing, but again see the above link on when it's better to use normalized data.

Community
  • 1
  • 1
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • 1
    Why prefer json or jsonb rather than hstore? I'll go read about them, but what reason(s) did you have in mind? – maxm Feb 15 '15 at 09:33
  • @maxm: it's more flexible, and the serialized data can be used as is in javascript. – Denis de Bernardy Feb 15 '15 at 09:40
  • While it may be more convenient, isn't it less performant? These slides from 2013 have benchmarks that suggest that: http://thebuild.com/presentations/pg-as-nosql-pgday-fosdem-2013.pdf – maxm Feb 15 '15 at 09:48
  • @maxm: It is indeed, but there have been improvements to Gist/GIN as well as to the json type, and the new jsonb type is fairly interesting. [They all have their strengths](http://www.pgcon.org/2014/schedule/attachments/313_xml-hstore-json.pdf), but versatility should lead you to pick json/jsonb if you're doing the same kind of stuff as OP. – Denis de Bernardy Feb 15 '15 at 11:05
3

In a nutshell: when there are few pages in a table, Postgres's planner prefers to skip the indexes and just load and scan the rows.

CREATE SCHEMA stackoverflow20589058;
--- CREATE SCHEMA

SET search_path TO stackoverflow20589058,"$user",public;
--- SET

CREATE EXTENSION hstore;
--- CREATE EXTENSION

CREATE TABLE Person (Id BIGSERIAL PRIMARY KEY NOT NULL, Values hstore);
--- CREATE TABLE

WITH Vals(n) AS (SELECT * FROM generate_series(1,10))
INSERT INTO Person (
  SELECT n AS Id, hstore('a=>'||n||', b=>'||n) AS Values FROM Vals
);
--- INSERT 0 10

EXPLAIN SELECT P.* FROM Person AS P WHERE P.Values @> hstore('a', '1');
---                         QUERY PLAN                        
--- ----------------------------------------------------------
---  Seq Scan on person p  (cost=0.00..24.50 rows=1 width=40)
---    Filter: ("values" @> '"a"=>"1"'::hstore)
--- (2 rows)

CREATE INDEX IX_GIN_VALUES ON Person USING GIN (values);
--- CREATE INDEX

------------------------- When there are few values, a sequential scan is
------------------------- often the best search strategy. Grabbing a few
------------------------- pages in sequence can be cheaper than making an
------------------------- extra disk seek to load the index.
EXPLAIN SELECT P.* FROM Person AS P WHERE P.Values @> hstore('a', '1');
---                        QUERY PLAN                        
--- ---------------------------------------------------------
---  Seq Scan on person p  (cost=0.00..1.12 rows=1 width=40)
---    Filter: ("values" @> '"a"=>"1"'::hstore)
--- (2 rows)

TRUNCATE Person;
--- TRUNCATE TABLE

WITH Vals(n) AS (SELECT * FROM generate_series(1,100000))
INSERT INTO Person (
  SELECT n AS Id, hstore('a=>'||n||', b=>'||n) AS Values FROM Vals
);
--- INSERT 0 100000

------------------------- When there are many rows, using the index can
------------------------- allow us to skip quite a lot of I/O; so
------------------------- Postgres's planner makes use of the index.
EXPLAIN SELECT P.* FROM Person AS P WHERE P.Values @> hstore('a', '1');
---                                    QUERY PLAN                                   
--- --------------------------------------------------------------------------------
---  Bitmap Heap Scan on person p  (cost=916.83..1224.56 rows=107 width=40)
---    Recheck Cond: ("values" @> '"a"=>"1"'::hstore)
---    ->  Bitmap Index Scan on ix_gin_values  (cost=0.00..916.80 rows=107 width=0)
---          Index Cond: ("values" @> '"a"=>"1"'::hstore)
--- (4 rows)

DROP SCHEMA stackoverflow20589058 CASCADE;
--- NOTICE:  drop cascades to 2 other objects
--- DETAIL:  drop cascades to extension hstore
--- drop cascades to table person
--- DROP SCHEMA
solidsnack
  • 1,631
  • 16
  • 26