Skip site navigation (1) Skip section navigation (2)

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

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Pierre Ducroquet <p(dot)psql(at)pinaraf(dot)info>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6232: hstore operator ? no longer uses indexes
Date: 2011-09-28 21:25:06
Message-ID: CAHyXU0xU4PCpEUwQddrCBx4GsGEvf3MkyiB362jVGShe_q0BZg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Wed, Sep 28, 2011 at 3:50 PM, Pierre Ducroquet <p(dot)psql(at)pinaraf(dot)info> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6232
> Logged by:          Pierre Ducroquet
> Email address:      p(dot)psql(at)pinaraf(dot)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)

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

merlin

In response to

pgsql-bugs by date

Next:From: Vikas MehtaDate: 2011-09-28 22:22:12
Subject: BUG #6234: Memory leak from PQexec
Previous:From: HolecDate: 2011-09-28 21:18:58
Subject: BUG #6233: pg_dump hangs with Access Violation C0000005

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group