The following bug has been logged online:
Bug reference: 6232
Logged by: Pierre Ducroquet
Email address: p.psql@pinaraf.info
PostgreSQL version: 9.1.1
Operating system: Linux Debian, amd64
Description: hstore operator ? no longer uses indexes
Details:
The following code, when executed with postgresql 8.4 or 9.0, uses the
idx_toto_h index, while it is unable to do so under postgresql 9.1
-- Sample code
DROP TABLE toto;
CREATE TABLE toto (id integer, h hstore);
INSERT INTO toto SELECT cast( random() * 1000 as integer) as i,
hstore(cast(cast( random() * 1000 as integer) as
text), 'a')
FROM generate_series(1,100000);
CREATE INDEX idx_toto_h ON toto USING gist(h);
ANALYZE toto;
EXPLAIN ANALYZE SELECT * from toto where h ? '500';
-- PostgreSQL 9.0 output
QUERY PLAN
----------------------------------------------------------------------------
-------------------------------------------
Bitmap Heap Scan on toto (cost=5.05..271.70 rows=100 width=20) (actual
time=0.627..1.273 rows=89 loops=1)
Recheck Cond: (h ? '500'::text)
-> Bitmap Index Scan on idx_toto_h (cost=0.00..5.03 rows=100 width=0)
(actual time=0.554..0.554 rows=669 loops=1)
Index Cond: (h ? '500'::text)
Total runtime: 1.373 ms
(5 rows)
-- postgresql 9.1 output
QUERY PLAN
----------------------------------------------------------------------------
--------------------------
Seq Scan on toto (cost=0.00..1887.00 rows=100 width=20) (actual
time=0.433..57.834 rows=91 loops=1)
Filter: (h ? '500'::text)
Total runtime: 57.929 ms
(3 rows)
Thanks