Обсуждение: 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