Re: jsonb and nested hstore

On 03/14/2014 06:44 PM, Tomas Vondra wrote:
> Stupid question - so if I have a json like this:

Not a stupid question, actually. In fact, I expect to answer it 400 or
500 times over the lifespan of 9.4.

> { "a" : { "b" : "c"}}
>
> the GIN code indexes {"b" : "c"} as a single value? And then takes "c"
> and indexes it as a single value too?

I don't know that "c" is indexed separately.

> 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").
>
> Hmmmm, if that's how it works, removing the size limit would be
> certainly more difficult than I thought.

Precisely. Hence, the Russian plans for VODKA.

> 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).
>
> Yes, the GIN index is quite large (~560MB for a ~1.2GB table).

State of the art, actually. In MongoDB, the indexes are frequently
several times larger than the raw data. So if ours are 50% the size,
we're doing pretty good.

On 15.3.2014 02:03, Greg Stark wrote:
>> I don't think Josh is right to say it'll be "fixed" in 9.5. It'll be
>> "better" in 9.5 because we have ambitious plans to continue
>> improving in this direction. But it'll be even better in 9.6 and
>> better again in 9.7. It'll never be "fixed".

Oh, no doubt. The important thing is that 9.4 will significantly
broaden the class of applications for which our JSON support is useful,
and allow us to remain relevant to an increasingly NoSQLish developer
base. We're both showing progress and delivering features which are
actually useful, even if they still have major limitations.

Plus, you know, those features are useful to *me*, so I'm keen on them
personally.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com