Re: jsonb and nested hstore

On Sat, Mar 15, 2014 at 1:44 AM, Tomas Vondra <tv@fuzzy.cz> wrote:
> Because otherwise I don't understand how the index could be used for
> queries with @> '{"a" : {"b" : "c"}}' conditions (i.e. path "[a,b]" with
> value "c").


Hm, some experimentation here shows it does indeed work for queries
like this and works quite nicely. I agree, this contradicts my
explanation so I'll need to poke in this some more to understand how
it is that this works so well:

explain  select j->'tags'->>'name' from osm where j @>
'{"tags":{"waterway":"dam"}}' ;                              QUERY PLAN
------------------------------------------------------------------------Bitmap Heap Scan on osm  (cost=139.47..19565.07
rows=6125width=95)  Recheck Cond: (j @> '{"tags": {"waterway": "dam"}}'::jsonb)  ->  Bitmap Index Scan on osmj
(cost=0.00..137.94rows=6125 width=0)        Index Cond: (j @> '{"tags": {"waterway": "dam"}}'::jsonb)Planning time:
0.147ms
 
(5 rows)

stark=#   select j->'tags'->>'name' from osm where j @>
'{"tags":{"waterway":"dam"}}' ;               ?column?
-----------------------------------------
Alpine DamBell Canyon DamBig Rock DamBriones DamCascade DamGordon Valley DamKimball Canyon DamMoore DamNicasio
DamNovatoCreek DamRyland DamVasona DamWarm Springs DamCrystal Dam
 
....
(248 rows)

Time: 6.126 ms


-- 
greg