Re: BUG #6232: hstore operator ? no longer uses indexes

On Wed, Sep 28, 2011 at 3:50 PM, Pierre Ducroquet <p.psql@pinaraf.info> wro=
te:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A06232
> Logged by: =A0 =A0 =A0 =A0 =A0Pierre Ducroquet
> Email address: =A0 =A0 =A0p.psql@pinaraf.info
> PostgreSQL version: 9.1.1
> Operating system: =A0 Linux Debian, amd64
> Description: =A0 =A0 =A0 =A0hstore 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,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0hstore(cast(cast( random()=
 * 1000 as integer) as
> text), 'a')
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 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
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN
>
> -------------------------------------------------------------------------=
---
> -------------------------------------------
> =A0Bitmap Heap Scan on toto =A0(cost=3D5.05..271.70 rows=3D100 width=3D20=
) (actual
> time=3D0.627..1.273 rows=3D89 loops=3D1)
> =A0 Recheck Cond: (h ? '500'::text)
> =A0 -> =A0Bitmap Index Scan on idx_toto_h =A0(cost=3D0.00..5.03 rows=3D10=
0 width=3D0)
> (actual time=3D0.554..0.554 rows=3D669 loops=3D1)
> =A0 =A0 =A0 =A0 Index Cond: (h ? '500'::text)
> =A0Total runtime: 1.373 ms
> (5 rows)
>
>
> -- postgresql 9.1 output
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0QUERY PLAN
>
> -------------------------------------------------------------------------=
---
> --------------------------
> =A0Seq Scan on toto =A0(cost=3D0.00..1887.00 rows=3D100 width=3D20) (actu=
al
> time=3D0.433..57.834 rows=3D91 loops=3D1)
> =A0 Filter: (h ? '500'::text)
> =A0Total runtime: 57.929 ms
> (3 rows)

confirmed the problem (even with seq scan disable). note @> is still
working, and ? operator is still being loaded into the operator class.
hm.

merlin