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