Re: jsonb and nested hstore - Mailing list pgsql-hackers

Thanks, looks like a bug.

On Tue, Mar 4, 2014 at 12:38 PM, Peter Geoghegan <pg@heroku.com> wrote:
> Hi Oleg,
>
> On Mon, Mar 3, 2014 at 7:17 AM, Oleg Bartunov <obartunov@gmail.com> wrote:
>> you can always look at our development repository:
>
> I think I found a bug:
>
> [local]/postgres=# \d+ bar
>                          Table "public.bar"
>  Column | Type  | Modifiers | Storage  | Stats target | Description
> --------+-------+-----------+----------+--------------+-------------
>  i      | jsonb |           | extended |              |
> Indexes:
>     "f" gin (i)
> Has OIDs: no
>
> [local]/postgres=# insert into bar values ('{
>     "firstName": "John",
>     "lastName": "Smith",
>     "age": 25,
>     "address": {
>         "streetAddress": "21 2nd Street",
>         "city": "New York",
>         "state": "NY",
>         "postalCode": 10021
>     },
>     "phoneNumbers": [
>         {
>             "type": "home",
>             "number": "212 555-1234"
>         },
>         {
>             "type": "fax",
>             "number": "646 555-4567"
>         }
>     ]
> }');
> INSERT 0 1
> Time: 7.635 ms
> [local]/postgres=# select * from bar where i @> '{"age":25.0}'::jsonb;
>  i
> ---
> (0 rows)
>
> Time: 2.443 ms
> [local]/postgres=# explain select * from bar where i @> '{"age":25.0}'::jsonb;
>                            QUERY PLAN
> -----------------------------------------------------------------
>  Bitmap Heap Scan on bar  (cost=16.01..20.02 rows=1 width=32)
>    Recheck Cond: ((i)::hstore @> '"age"=>25.0'::hstore)
>    ->  Bitmap Index Scan on f  (cost=0.00..16.01 rows=1 width=0)
>          Index Cond: ((i)::hstore @> '"age"=>25.0'::hstore)
>  Planning time: 0.161 ms
> (5 rows)
>
> [local]/postgres=# set enable_bitmapscan = off;
> SET
> Time: 6.052 ms
> [local]/postgres=# select * from bar where i @> '{"age":25.0}'::jsonb;
> -[ RECORD 1
]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> i | {"age": 25, "address": {"city": "New York", "state": "NY",
> "postalCode": 10021, "streetAddress": "21 2nd Street"}, "lastName":
> "Smith", "firstName": "John", "phoneNumbers": [{"type": "home",
> "number": "212 555-1234"}, {"type": "fax", "number": "646 555-4567"}]}
>
> Time: 6.479 ms
> [local]/postgres=# explain select * from bar where i @> '{"age":25.0}'::jsonb;
>                      QUERY PLAN
> -----------------------------------------------------
>  Seq Scan on bar  (cost=0.00..26.38 rows=1 width=32)
>    Filter: ((i)::hstore @> '"age"=>25.0'::hstore)
>  Planning time: 0.154 ms
> (3 rows)
>
> Time: 6.565 ms
>
> --
> Peter Geoghegan