Re: jsonb and nested hstore

Thanks, looks like a bug.

On Tue, Mar 4, 2014 at 12:38 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> Hi Oleg,
>
> On Mon, Mar 3, 2014 at 7:17 AM, Oleg Bartunov <obartunov(at)gmail(dot)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