MIME-Version: 1.0
In-Reply-To: 
 <CAM3SWZQbjqippSdQU2RHFTOe_SyoMcH_MOCcUeYPA0NWPjYqLQ@mail.gmail.com>
References: <27299.1396989666@sss.pgh.pa.us>
 <CAM3SWZQGZyqSBzaFZ4gmtnQZpcXLE=ceUL_O29gtMgnP=AvC+g@mail.gmail.com>
 <28589.1396992841@sss.pgh.pa.us>
 <CAM3SWZRAxisGCdODhbgeiNL6tayqL18XMzun4W3xesASmKfwDA@mail.gmail.com>
 <29030.1396993582@sss.pgh.pa.us>
 <CAM3SWZQzOxmryVe_bdu9WhqbJTBoiqM7knvKm0oRYxvfsFnz7Q@mail.gmail.com>
 <534475B7.6020908@dunslane.net> <5344EAA4.1050605@vmware.com>
 <CAM3SWZS-12EHfqvDzTbtkEsQHfVLJxx8XP1=jrCXK2wh9uKW-Q@mail.gmail.com>
 <53450306.8040104@vmware.com>
 <CAM3SWZQbjqippSdQU2RHFTOe_SyoMcH_MOCcUeYPA0NWPjYqLQ@mail.gmail.com>
From: Alexander Korotkov <aekorotkov@gmail.com>
Date: Wed, 9 Apr 2014 19:12:31 +0400
Message-ID: 
 <CAPpHfduitYNNDA8jh6a2qYyxuxROyBeobHsBuqZf0LaMKqMHJA@mail.gmail.com>
Subject: Re: default opclass for jsonb (was Re: Call for
 GIST/GIN/SP-GIST opclass documentation)
To: Peter Geoghegan <pg@heroku.com>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>,
 Andrew Dunstan <andrew@dunslane.net>,
 Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary=001a11c13d64c50bf704f69d8880
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

--001a11c13d64c50bf704f69d8880
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Apr 9, 2014 at 12:40 PM, Peter Geoghegan <pg@heroku.com> wrote:

> On Wed, Apr 9, 2014 at 1:21 AM, Heikki Linnakangas
> <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.
>
> 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.

--001a11c13d64c50bf704f69d8880
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_extra"><div class=3D"gmail_quote">On W=
ed, Apr 9, 2014 at 12:40 PM, Peter Geoghegan <span dir=3D"ltr">&lt;<a href=
=3D"mailto:pg@heroku.com" target=3D"_blank">pg@heroku.com</a>&gt;</span> wr=
ote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style=
:solid;padding-left:1ex">

<div class=3D"">On Wed, Apr 9, 2014 at 1:21 AM, Heikki Linnakangas<br>
&lt;<a href=3D"mailto:hlinnakangas@vmware.com">hlinnakangas@vmware.com</a>&=
gt; wrote:<br>
&gt; I didn&#39;t say that. On the contrary, I think the shotgun approach j=
sonb_ops<br>
&gt; and jsonb_hash_ops take is too broad. It should be possible to specify=
 what<br>
&gt; to index in a more detailed fashion.<br>
<br>
</div>It is - use an expression index. That&#39;s by far the most important=
 way<br>
to specify what to index in a more detailed fashion. There are others,<br>
but that&#39;s the major one. Beyond that, yes, it&#39;s necessary to<br>
carefully write your query predicate a certain way. However, a similar<br>
situation exists in MongoDB, where there is a distinction between<br>
&quot;Indexes on embedded fields&quot; (which must be accessed using specia=
l<br>
&quot;dot notation&quot;) and &quot;indexes on subdocuments&quot; (which ca=
nnot be<br>
accessed using &quot;dot notation&quot;). It&#39;s late here, but I&#39;m p=
retty sure<br>
that&#39;s a feature and not a limitation.<br></blockquote><div><br></div><=
div>I believe that serious limitation we now have is that we actually speci=
fy kind of index to be used in the SQL query.=A0<br>For example you need to=
 find objects with active =3D true. You can write:</div>

<div><br></div><div>js @&gt; {&quot;active&quot;: true}<br></div><div><br><=
/div><div>then GIN index on js can be used. Also you can write:<br></div><d=
iv><br></div><div>js-&gt;&#39;active&#39; =3D true</div><div><br></div><div=
>

then btree expression index on (js-&gt;&#39;active&#39;) can be used. For s=
ure, one can do</div><div><br></div><div>js @&gt; {&quot;active&quot;: true=
} AND js-&gt;&#39;active&#39; =3D true<br></div><div><br></div><div>This qu=
ery can use any of indexes, but it is:</div>

<div>1) Cluge</div><div>2) Excess recheck</div><div>3) If both indexes pres=
ent, excess &quot;bitmap and&quot;.</div><div><br></div><div>Having to choo=
se 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 op=
inion is that we have to do something in planner to make it understand at l=
east this two kinds of queries to be equivalent.</div>

<div><br></div>------<br>With best regards,<br>Alexander Korotkov.<div>=A0<=
/div></div></div></div>

--001a11c13d64c50bf704f69d8880--