public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Korotkov <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)
Date: Wed, 9 Apr 2014 19:12:31 +0400
Message-ID: <CAPpHfduitYNNDA8jh6a2qYyxuxROyBeobHsBuqZf0LaMKqMHJA@mail.gmail.com> (raw)
In-Reply-To: <CAM3SWZQbjqippSdQU2RHFTOe_SyoMcH_MOCcUeYPA0NWPjYqLQ@mail.gmail.com>
References: <[email protected]>
<CAM3SWZQGZyqSBzaFZ4gmtnQZpcXLE=ceUL_O29gtMgnP=AvC+g@mail.gmail.com>
<[email protected]>
<CAM3SWZRAxisGCdODhbgeiNL6tayqL18XMzun4W3xesASmKfwDA@mail.gmail.com>
<[email protected]>
<CAM3SWZQzOxmryVe_bdu9WhqbJTBoiqM7knvKm0oRYxvfsFnz7Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAM3SWZS-12EHfqvDzTbtkEsQHfVLJxx8XP1=jrCXK2wh9uKW-Q@mail.gmail.com>
<[email protected]>
<CAM3SWZQbjqippSdQU2RHFTOe_SyoMcH_MOCcUeYPA0NWPjYqLQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-hackers>
On Wed, Apr 9, 2014 at 12:40 PM, Peter Geoghegan <[email protected]> wrote:
> On Wed, Apr 9, 2014 at 1:21 AM, Heikki Linnakangas
> <[email protected]> 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.
>
> It is - use an expression index. That's by far the most important way
> 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:
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.
view thread (65+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)
In-Reply-To: <CAPpHfduitYNNDA8jh6a2qYyxuxROyBeobHsBuqZf0LaMKqMHJA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox