Hi,
Apologies if this is the wrong list for this time of query (first time posting).
I'm currently experimenting with hstore on Posgtres 9.4rc1. I've created a table with an hstore column, with and index on that column (tried both gin and btree indexes) and the explain plan says that the index is never used for the lookup and falls to a sequential scan every time (table has 1 000 000 rows). The query plans and execution time for btree index, gin index and unindexed are the same. Is there something I'm doing wrong or missing in order to get indexes to work on hstore columns?
Details:
0) Postgres version:
barkerm=# select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4rc1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-7), 64-bit
(1 row)
1) Created table with hstore column and btree index.
barkerm=# \d audit
Table "public.audit"
Column | Type | Modifiers
---------------+-----------------------------+----------------------------------------------------
id | integer | not null default nextval('audit_id_seq'::regclass)
principal_id | integer |
created_at | timestamp without time zone |
root | character varying(255) |
template_code | character(3) |
attributes | hstore |
args | character varying(255)[] |
Indexes:
"audit_pkey" PRIMARY KEY, btree (id)
"audit_attributes_idx" btree (attributes)
2) Insert 1 000 000 rows
barkerm=# select count(*) from audit;
count
---------
1000000
(1 row)
3) Run analyse.
4) Pick a row somewhere in the middle:
barkerm=# select id, attributes from audit where id = 500000;
id | attributes
--------+---------------------------------------------------------
500000 | "accountId"=>"1879355460", "instrumentId"=>"1625557725"
(1 row)
5) Explain query using the attributes column in the where clause (uses Seq Scan).
barkerm=# explain analyse select * from audit where attributes->'accountId' = '1879355460';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on audit (cost=0.00..35409.00 rows=5000 width=133) (actual time=114.314..218.821 rows=1 loops=1)
Filter: ((attributes -> 'accountId'::text) = '1879355460'::text)
Rows Removed by Filter: 999999
Planning time: 0.074 ms
Execution time: 218.843 ms
(5 rows)
6) Rebuild the data using a gin index.
barkerm=# \d audit
Table "public.audit"
Column | Type | Modifiers
---------------+-----------------------------+----------------------------------------------------
id | integer | not null default nextval('audit_id_seq'::regclass)
principal_id | integer |
created_at | timestamp without time zone |
root | character varying(255) |
template_code | character(3) |
attributes | hstore |
args | character varying(255)[] |
Indexes:
"audit_pkey" PRIMARY KEY, btree (id)
"audit_attributes_idx" gin (attributes)
7) Again explain the selection of a single row using a constraint that references the hstore column. Seq Scan is still used.
barkerm=# explain analyse select * from audit where attributes->'accountId' = '1238334838';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on audit (cost=0.00..35409.00 rows=5000 width=133) (actual time=122.173..226.363 rows=1 loops=1)
Filter: ((attributes -> 'accountId'::text) = '1238334838'::text)
Rows Removed by Filter: 999999
Planning time: 0.164 ms
Execution time: 226.392 ms
(5 rows)
8) Drop index an query as a baseline.
barkerm=# explain analyse select * from audit where attributes->'accountId' = '1238334838';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on audit (cost=0.00..35409.00 rows=5000 width=133) (actual time=109.115..212.666 rows=1 loops=1)
Filter: ((attributes -> 'accountId'::text) = '1238334838'::text)
Rows Removed by Filter: 999999
Planning time: 0.113 ms
Execution time: 212.701 ms
(5 rows)
Regards,
Michael Barker.
