Thread: 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=3D# EXPLAIN SELECT * from domains where >> name=3D'"name"=3D>"somedomain"'::text::hstore->'name'; >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0QUERY PLAN >> ------------------------------------------------------------------------= ---- >> ------------------------ >> =C2=A0Seq Scan on domains =C2=A0(cost=3D0.00..7775.91 rows=3D1 width=3D2= 30) >> =C2=A0 =C2=A0Filter: ((name)::text =3D (('"name"=3D>"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. =C2=A0This is fixed in 9.0. > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0regards, 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=3D# EXPLAIN SELECT * from domains where >>> name=3D'"name"=3D>"somedomain"'::text::hstore->'name'; >>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0QUERY PLAN >>> -----------------------------------------------------------------------= ----- >>> ------------------------ >>> =A0Seq Scan on domains =A0(cost=3D0.00..7775.91 rows=3D1 width=3D230) >>> =A0 =A0Filter: ((name)::text =3D (('"name"=3D>"somedomain"'::text)::hst= ore -> >>> '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. =A0This is fixed in 9.0. >> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, 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: > =A0ALTER 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. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company