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

BUG #6232: hstore operator ? no longer uses indexes

From: "Pierre Ducroquet" <p(dot)psql(at)pinaraf(dot)info>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6232: hstore operator ? no longer uses indexes
Date: 2011-09-28 20:50:10
Message-ID: 201109282050.p8SKoA4O084649@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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)




Thanks

Responses

pgsql-bugs by date

Next:From: HolecDate: 2011-09-28 21:18:58
Subject: BUG #6233: pg_dump hangs with Access Violation C0000005
Previous:From: Tom LaneDate: 2011-09-28 16:50:12
Subject: Re: Problems with ENUM type manipulation in 9.1

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