BUG #5797: Strange bug with hstore
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)
"Maxim Boguk" <Maxim.Boguk@gmail.com> writes:
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)
The reason for this is that hstore fails to label its I/O functions as
immutable (or even stable) in 8.4. This is fixed in 9.0.
regards, tom lane
On Tue, Dec 21, 2010 at 7:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Maxim Boguk" <Maxim.Boguk@gmail.com> writes:
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)The reason for this is that hstore fails to label its I/O functions as
immutable (or even stable) in 8.4. This is fixed in 9.0.regards, tom lane
Sorry for late responce and thank you for clarification.
My research of that problem leads me to conclusion that can be fixed
on 8.4.4 with:
ALTER FUNCTION hstore_in(cstring) IMMUTABLE;
but is it really safe?
Or text->hstore conversion in 8.4 is really VOLATILE as labeled in
hstrore module?
Regards, Maxim
On Sun, Dec 26, 2010 at 12:29 AM, Maxim Boguk <maxim.boguk@gmail.com> wrote:
On Tue, Dec 21, 2010 at 7:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Maxim Boguk" <Maxim.Boguk@gmail.com> writes:
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)The reason for this is that hstore fails to label its I/O functions as
immutable (or even stable) in 8.4. This is fixed in 9.0.regards, tom lane
Sorry for late responce and thank you for clarification.
My research of that problem leads me to conclusion that can be fixed
on 8.4.4 with:
ALTER FUNCTION hstore_in(cstring) IMMUTABLE;
but is it really safe?
Or text->hstore conversion in 8.4 is really VOLATILE as labeled in
hstrore module?
I think your proposed change is safe.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company