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

BUG #5797: Strange bug with hstore

From: "Maxim Boguk" <Maxim(dot)Boguk(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5797: Strange bug with hstore
Date: 2010-12-20 11:15:34
Message-ID: 201012201115.oBKBFYH9082710@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5797
Logged by:          Maxim Boguk
Email address:      Maxim(dot)Boguk(at)gmail(dot)com
PostgreSQL version: 8.4.4
Operating system:   Freebsd
Description:        Strange bug with hstore
Details: 

One day ago I analyzed slow query for one of my clients and found strange
query plan. After some digging I localized something which I think is bug.

The bug can be seen in these two explains:

Good explain:
billing=# EXPLAIN SELECT * from domains where
name='"name"=>"somedomain"'::hstore->'name';
                                  QUERY PLAN
----------------------------------------------------------------------------
--
 Index Scan using domains_name on domains  (cost=0.00..0.29 rows=1
width=230)
   Index Cond: ((name)::text = 'somedomain'::text)
(index used)

Bad explain:
billing=# EXPLAIN SELECT * from domains where
name='"name"=>"somedomain"'::text::hstore->'name';
                                             QUERY PLAN
----------------------------------------------------------------------------
------------------------
 Seq Scan on domains  (cost=0.00..7775.91 rows=1 width=230)
   Filter: ((name)::text = (('"name"=>"somedomain"'::text)::hstore ->
'name'::text))
(index not used)

As can be seen no index was used in second case. I tested some variants and
found conditions like field1=other_field::text::hstore->'key' never using
index on field1.

Ofcourse client case was much more complicated and contained 9 joins... but
troublesome part was looked like:

billing=# EXPLAIN SELECT
es.params
FROM services es
JOIN domains dm ON dm.name = (es.params::hstore)->'name'
WHERE
es.shortname = 'exchange_accepted_domain'
;
                                           QUERY PLAN
----------------------------------------------------------------------------
---------------------
 Nested Loop  (cost=0.00..27990293.56 rows=3289 width=32)
   Join Filter: ((dm.name)::text = ((es.params)::hstore -> 'name'::text))
   ->  Index Scan using services_shortname on services es  (cost=0.00..68.50
rows=2406 width=32)
         Index Cond: ((shortname)::text = 'exchange_accepted_domain'::text)
   ->  Seq Scan on domains dm  (cost=0.00..3918.31 rows=385760 width=15)
(5 rows)

Responses

pgsql-bugs by date

Next:From: H.Merijn BrandDate: 2010-12-20 11:31:38
Subject: Re: [Fwd: DBD::Pg on HP-UX 11.31 64bit]
Previous:From: VolkerDate: 2010-12-20 10:08:24
Subject: BUG #5796: Problem with history-files

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