I created an index on an hstore function, fetchval(hstore, text), however when I use the -> infix operator which resolves to the very same function, this index is not used. It should be used.
I have included an example:
Table with hstore index:
de10keipt01939=> \d log_data
Table "public.log_data"
Column | Type | Modifiers
--------+--------------------------+-------------------------------------------------------
id | bigint | not null default nextval('log_data_id_seq'::regclass)
time | timestamp with time zone |
data | hstore |
Indexes:
"index_log_data_by_time" btree ("time")
"index_participant_id" btree (fetchval(data, 'participant_id'::text))
query with function notation:
de10keipt01939=> explain ANALYZE select * from log_data where (data->'participant_id')='2851' order by id desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Sort (cost=16432.56..16433.36 rows=1583 width=315) (actual time=198.643..198.777 rows=183 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 119kB
-> Seq Scan on log_data (cost=0.00..16415.74 rows=1583 width=315) (actual time=6.926..198.297 rows=183 loops=1)
Filter: ((data -> 'participant_id'::text) = '2851'::text)
Total runtime: 198.922 ms
(6 rows)
query with infix notation:
de10keipt01939=> explain ANALYZE select * from log_data where fetchval(data,'participant_id')='2851' order by id desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=341.14..341.23 rows=179 width=315) (actual time=0.724..0.841 rows=183 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 119kB
-> Bitmap Heap Scan on log_data (cost=2.35..339.80 rows=179 width=315) (actual time=0.091..0.489 rows=183 loops=1)
Recheck Cond: (fetchval(data, 'participant_id'::text) = '2851'::text)
-> Bitmap Index Scan on index_participant_id (cost=0.00..2.34 rows=179 width=0) (actual time=0.060..0.060 rows=183 loops=1)
Index Cond: (fetchval(data, 'participant_id'::text) = '2851'::text)
Total runtime: 1.010 ms
(8 rows)
—Will