Re: jsonb and nested hstore

On 14.3.2014 22:54, Peter Geoghegan wrote:
> On Fri, Mar 14, 2014 at 2:21 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>> I'm not awfully familiar with the GIN code, but based on Alexander's
>> feedback I presume fixing the GIN length limit (or rather removing it,
>> as it's a feature, not a bug) is quite straightforward. Why not to at
>> least consider that for 9.4, unless it turns more complex than expected?
>
> Alexander said nothing about removing that limitation, or if he did I
> missed it. Which, as I said, I don't consider to be much of a

Sure he did, see this:

http://www.postgresql.org/message-id/CAPpHfds4xmg5zOP+1CtrrqnM6wxhh2A7j11nnJeosa76UoWxyg@mail.gmail.com

Although it doesn't mention how complex change it would be.

> limitation, because indexing the whole nested value doesn't mean it
> can satisfy a query on some more nested subset of an indexed value
> datum (i.e. a value in the sense of a value in a key/value pair).

OK, I'm getting lost in the nested stuff. The trouble I'm running into
are rather unlerated to nesting. For example indexing this fails if the
string is sufficiently long (~1350B if random, more if compressible).

{"key" : "... string ..."}

How's that related to nesting?

Anyway, I'm not talking about exact matches on subtrees. I'm talking
about queries like this:

SELECT doc FROM delicious
WHERE doc @> '{"title_detail" : {"value" : "TheaterMania"}}';

which does exactly the same thing like this query:

SELECT doc FROM delicious
WHERE doc->'title_detail'->>'value' = 'TheaterMania';

Except that the first query can use a GIN index created like this:

CREATE INDEX delicious_idx ON delicious USING GIN (doc);

while the latter does sequential scan. It can use a GiST index too, but
it takes 140ms with GiST and only ~0.3ms with GIN. Big difference.

> Alexander mentioned just indexing keys (object keys, or equivalently
> array elements at the jsonb level), which is a reasonable thing, but
> can be worked on later. I don't have much interest in working on
> making it possible to index elaborate nested values in key/value
> pairs, which is what you're suggesting if I've understood correctly.

I never asked for indexing elaborate nested values in key/value pairs.
All I'm asking for is indexing of json values containing long strings.

regards
Tomas