Re: jsonb and nested hstore - Mailing list pgsql-hackers

From Tomas Vondra
Subject Re: jsonb and nested hstore
Date
Msg-id 53245D43.8030708@fuzzy.cz
Whole thread Raw
In response to Re: jsonb and nested hstore  (Peter Geoghegan <pg@heroku.com>)
List pgsql-hackers
On 15.3.2014 06:40, Peter Geoghegan wrote:
> 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.

Sure, I need to know some basic rules / do assumptions about the
structure of the json document. In other words, schemaless databases are
difficult to query.

For example when storing mail message headers (i.e. the example I've
used before), I do know that the json document is rather well structured
- it's not nested at all, and all the values are either scalar values
(mostly strings), or arrays of scalars.

So it looks like this
 {   "from" : "john.doe@example.com",   "to" : ["jane.doe@example.com", "jack.doe@example.com"],   ... }

So the schema is rather well defined (not the exact keys, but the
structure certainly is).

Let's say I want to allow arbitrary searches on headers - I can't
support that with expression indexes, because there's like a zillion of
possible headers and I'd have to create an expression index on each of
them separately.

But I can support that with a single GIN index ...


>> 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?

That wasn't meant as a complaint. I have no problem with the index size
(If we can make it smaller in the future, great! But I can live with the
current index sizes too.)


regards
Tomas



pgsql-hackers by date:

Previous
From: Mohsen SM
Date:
Subject: bpchar functinos
Next
From: Fujii Masao
Date:
Subject: Re: Archive recovery won't be completed on some situation.