Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)

On 04/09/2014 10:40 AM, Peter Geoghegan wrote:
> On Tue, Apr 8, 2014 at 11:37 PM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>> As the code stands, you don't have a choice on any of those things. The
>> decisions have been made by us, PostgreSQL developers. The only choice you
>> have is between jsonb_ops and jsonb_hash_ops, with a strange combination of
>> tradeoffs in both. Sure, they're still useful, if not optimal, for a
>> wide-range of applications. For more complicated cases, you will have to
>> resort to expression indexes. It bugs me greatly that the underlying indexam
>> could do all those things, we're just not exposing the capability.
>
> Why would you ever not have to use expression indexes? Idiomatic usage
> of jsonb involves expression indexes because it's desirable to index
> only a expression. People will want to do things like only index the
> nested "tags" array far more frequently then they'll only want to
> index keys (that is, Object pair keys) in the entire document. I don't
> get why you'd say that they'd "resort" to expression indexes, like
> they're a kludge.

Expression indexes are definitely nice, but you have to be careful to
formulate the query in exactly the same way to match the index.

> Have you ever tried out one of the new document
> databases? I suggest you do. Expression indexes on jsonb map pretty
> closely onto how you're frequently expected to index data in those
> systems. That's something that they make heavy use of. Why would you
> ever not really have to consider ahead of time what is important
> enough to be indexed, and what is not?

I didn't say that. On the contrary, I think the shotgun approach
jsonb_ops and jsonb_hash_ops take is too broad. It should be possible to
specify what to index in a more detailed fashion.

- Heikki