Re: jsonb and nested hstore

On Wed, Mar 12, 2014 at 6:20 AM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> I'm still not sure how would that look. Does that mean I'd have to create
> multiple GIN indexes - one for each possible key or something like that?
> Can you give an example?

It could mean that you're obliged to create multiple indexes, yes. For
an example, and to get a better sense of what I mean, look at the
documentation in the patch.

The idea that you're going to create one index on a jsonb, and it's
going to be able to usefully index a lot of different queries doesn't
seem practical for most use-cases. Mostly, people will have fairly
homogeneous json documents, and they'll want to index certain nested
fields common to all or at least a large majority of those documents.

By indexing entire jsonb datums, do you hope to get much benefit out
of the indexed values (as opposed to keys) being stored (in serialized
form) in the GIN index? Because you *are* indexing a large nested
structure as a value. Is that large nested structure going to appear
in your query predicate, or are you just going to subscript the jsonb
to get to the level that's of interest to query that? I'm pretty sure
that people want the latter. Are you sure that your complaint isn't
just that the default GIN opclass indexes values (as distinct from
keys) that are large and unwieldy, and not terribly useful?

I don't think expressional indexes are some kind of unfortunate work
around for a jsonb limitation. I think that they're the natural way to
approach indexing a nested structure in Postgres. MongoDB, for
example, does not magically index everything. You're still required to
make choices about indexing that consider the access patterns.

--
Peter Geoghegan