Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)
| От | Alexander Korotkov |
|---|---|
| Тема | Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation) |
| Дата | |
| Msg-id | CAPpHfduitYNNDA8jh6a2qYyxuxROyBeobHsBuqZf0LaMKqMHJA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation) (Peter Geoghegan <pg@heroku.com>) |
| Список | pgsql-hackers |
On Wed, Apr 9, 2014 at 12:40 PM, Peter Geoghegan <pg@heroku.com> wrote:
------
With best regards,
Alexander Korotkov.
On Wed, Apr 9, 2014 at 1:21 AM, Heikki LinnakangasIt is - use an expression index. That's by far the most important way
<hlinnakangas@vmware.com> wrote:
> 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.
to specify what to index in a more detailed fashion. There are others,
but that's the major one. Beyond that, yes, it's necessary to
carefully write your query predicate a certain way. However, a similar
situation exists in MongoDB, where there is a distinction between
"Indexes on embedded fields" (which must be accessed using special
"dot notation") and "indexes on subdocuments" (which cannot be
accessed using "dot notation"). It's late here, but I'm pretty sure
that's a feature and not a limitation.
I believe that serious limitation we now have is that we actually specify kind of index to be used in the SQL query.
For example you need to find objects with active = true. You can write:
For example you need to find objects with active = true. You can write:
js @> {"active": true}
then GIN index on js can be used. Also you can write:
js->'active' = true
then btree expression index on (js->'active') can be used. For sure, one can do
js @> {"active": true} AND js->'active' = true
This query can use any of indexes, but it is:
1) Cluge
2) Excess recheck
3) If both indexes present, excess "bitmap and".
Having to choose index in SQL-query we make our SQL more imperative and less declarative. Similar things can happen without json/hstore (user have to rewrite SQL in order to use expression index), but now it could become very common. My opinion is that we have to do something in planner to make it understand at least this two kinds of queries to be equivalent.
With best regards,
Alexander Korotkov.
В списке pgsql-hackers по дате отправления: