The following bug has been logged online:
Bug reference: 5797
Logged by: Maxim Boguk
Email address: Maxim.Boguk@gmail.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)
