Re: jsonb and nested hstore

On Sat, Mar 15, 2014 at 1:44 AM, Tomas Vondra <tv(at)fuzzy(dot)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=6125 width=95)
Recheck Cond: (j @> '{"tags": {"waterway": "dam"}}'::jsonb)
-> Bitmap Index Scan on osmj (cost=0.00..137.94 rows=6125 width=0)
Index Cond: (j @> '{"tags": {"waterway": "dam"}}'::jsonb)
Planning time: 0.147 ms
(5 rows)

stark=# select j->'tags'->>'name' from osm where j @>
'{"tags":{"waterway":"dam"}}' ;
?column?
-----------------------------------------

Alpine Dam
Bell Canyon Dam
Big Rock Dam
Briones Dam
Cascade Dam
Gordon Valley Dam
Kimball Canyon Dam
Moore Dam
Nicasio Dam
Novato Creek Dam
Ryland Dam
Vasona Dam
Warm Springs Dam
Crystal Dam
....
(248 rows)

Time: 6.126 ms

--
greg