The following bug has been logged online: Bug reference: 5797 Logged by: Maxim Boguk Email address: maxim.bo...@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) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs