Re: jsonb and nested hstore

On Fri, Mar 14, 2014 at 6:44 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> Well, depends on how you define useful. With the sample dataset
> 'delicious' (see Peter's post) I can do this:
>
>    SELECT doc FROM delicious
>           WHERE doc @> '{"title_detail" : {"value" : "TheaterMania"}}';
>
> with arbitrary paths, and I may create a GIN index to support such
> queries. And yes, it's much faster than GiST for example (by a factor of
> 1000).

If you know ahead of time the entire nested value you can. So, if you
attach some other data to the "TheaterMania" document, you had better
know that too if you hope to write a query like this. You also have to
index the entire table, where presumably with a little thought you
could get away with a much smaller index. That strikes me as not very
useful.

> Yes, the GIN index is quite large (~560MB for a ~1.2GB table).

With the default opclass, without an expressional index, 100% of the
data from the table appears in the index. Why do you think that's
quite large?

-- 
Peter Geoghegan