On 15.3.2014 06:40, Peter Geoghegan wrote: > On Fri, Mar 14, 2014 at 6:44 PM, Tomas Vondra <t...@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....@example.com", "to" : ["jane....@example.com", "jack....@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers