...

или внеочередные заметки


Previous Entry Share Next Entry
Nested hstore (hstore 2.0) git repository
trekking, Himalaya
obartunov
We saved for historical reason our nested hstore project at github. I don't recommend to use it, since all development resourse now are concentrated on jsonb.

Expect better jsonb indexing like structural queries support and jsonb query language in 9.5 or 9.6.
AFAIK, Mongodb doesn't support such (subtree, wildcard) queries.

postgres=# explain analyze select count(*) from jb where jb @>> '{"term":"NYC"}'::jsonb;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4740.72..4740.73 rows=1 width=0) (actual time=0.764..0.764 rows=1 loops=1)
-> Bitmap Heap Scan on jb (cost=41.71..4737.59 rows=1253 width=0) (actual time=0.201..0.735 rows=285 loops=1)
Recheck Cond: (jb @>> '{"term": "NYC"}'::jsonb)
Heap Blocks: exact=285
-> Bitmap Index Scan on gin_jb_bloom_idx (cost=0.00..41.40 rows=1253 width=0) (actual time=0.162..0.162 rows=285 loops=1)
Index Cond: (jb @>> '{"term": "NYC"}'::jsonb)
Planning time: 0.048 ms
Total runtime: 0.799 ms
(8 rows)

postgres=# explain analyze select count(*) from jb where jb @>> '"NYC"'::jsonb;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4740.72..4740.73 rows=1 width=0) (actual time=0.648..0.648 rows=1 loops=1)
-> Bitmap Heap Scan on jb (cost=41.71..4737.59 rows=1253 width=0) (actual time=0.148..0.618 rows=285 loops=1)
Recheck Cond: (jb @>> '"NYC"'::jsonb)
Heap Blocks: exact=285
-> Bitmap Index Scan on gin_jb_bloom_idx (cost=0.00..41.40 rows=1253 width=0) (actual time=0.119..0.119 rows=285 loops=1)
Index Cond: (jb @>> '"NYC"'::jsonb)
Planning time: 0.051 ms
Total runtime: 0.672 ms
(8 rows)
Tags: , ,

You are viewing obartunov