I have a query that is underperforming, data is an hstore column:
SELECT "vouchers".* FROM "vouchers" WHERE "vouchers"."type" IN ('VoucherType') AND ((data -> 'giver_id')::integer = 1) AND ((data -> 'recipient_email') is NULL)
I've tried adding the following indexes:
CREATE INDEX free_boxes_recipient ON vouchers USING gin ((data->'recipient_email')) WHERE ((data->'recipient_email') IS NULL);
CREATE INDEX voucher_type_giver ON vouchers USING gin ((data->'giver_id')::int)
As well as an overall index: CREATE INDEX voucher_type_data ON vouchers USING gin (data)
Here's the current query plan:
Seq Scan on vouchers (cost=0.00..15158.70 rows=5 width=125) (actual time=122.818..122.818 rows=0 loops=1)
Filter: (((data -> 'recipient_email'::text) IS NULL) AND ((type)::text = 'VoucherType'::text) AND (((data -> 'giver_id'::text))::integer = 1))
Rows Removed by Filter: 335148
Planning time: 0.196 ms
Execution time: 122.860 ms
How can I index this hstore column to get it down to a more reasonable query?
For the documentation:
hstore has GiST and GIN index support for the @>, ?, ?& and ?| operators.
You are searching for an integer value so you can use simple btree index like this:
CREATE INDEX ON vouchers (((data->'giver_id')::int));
EXPLAIN ANALYSE
SELECT *
FROM vouchers
WHERE vtype IN ('VoucherType')
AND (data -> 'giver_id')::integer = 1
AND (data -> 'recipient_email') is NULL;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on vouchers (cost=4.66..82.19 rows=1 width=34) (actual time=0.750..0.858 rows=95 loops=1)
Recheck Cond: (((data -> 'giver_id'::text))::integer = 1)
Filter: (((data -> 'recipient_email'::text) IS NULL) AND (vtype = 'VoucherType'::text))
Heap Blocks: exact=62
-> Bitmap Index Scan on vouchers_int4_idx (cost=0.00..4.66 rows=50 width=0) (actual time=0.018..0.018 rows=95 loops=1)
Index Cond: (((data -> 'giver_id'::text))::integer = 1)
Planning time: 2.115 ms
Execution time: 0.896 ms
(8 rows)