Re: jsonb and nested hstore

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