Subject: Re: JSON Path and GIN Questions

From: Tom Lane <[email-masked]>
Date: Thu, 14 Sep 2023 00:41:58 -0400

Erik Rijkers <[email-masked]> writes:
> p 9/13/23 om 22:01 schreef David E. Wheeler:
>> On Sep 13, 2023, at 01:11, Erik Rijkers <[email-masked]> wrote:
>>> "All use of json*() functions preclude index usage."

>> Where did that come from? Why wouldn’t JSON* functions use indexes? I see that the docs only mention operators; why would the corresponding functions behave the same?

> Sorry, perhaps my reply was a bit off-topic.
> But you mentioned perhaps touching the docs and
> the not-use-of-index is just so unexpected.

Unexpected to who?  I think the docs make it pretty plain that only
operators on indexed columns are considered as index qualifications.
Admittedly, 11.2 Index Types [1] makes the point only by not
discussing any other case, but when you get to 11.10 Operator Classes
and Operator Families [2] and discover that the entire index definition
mechanism is based around operators not functions, you should be able
to reach that conclusion.  The point is made even more directly in
38.16 Interfacing Extensions to Indexes [3], though I'll concede
that that's not material I'd expect the average PG user to read.
As far as json in particular is concerned, 8.14.4 jsonb Indexing [4]
is pretty clear about what is or is not supported.

			regards, tom lane

[1] https://www.postgresql.org/docs/current/indexes-types.html
[2] https://www.postgresql.org/docs/current/indexes-opclass.html
[3] https://www.postgresql.org/docs/current/xindex.html
[4] https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

---

From: "David E. Wheeler" <[email-masked]>
Date: Fri, 15 Sep 2023 16:13:22 -0400

> is pretty clear about what is or is not supported.

How do you feel about this note, then?

diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index b6c2ddbf55..7dda727f0d 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -413,6 +413,13 @@ SELECT doc-&gt;'site_name' FROM websites
     Two GIN <quote>operator classes</quote> are provided, offering different
     performance and flexibility trade-offs.
   </para>
+  <note>
+    <para>
+    As with all indexes, only operators on indexed columns are considered as
+    index qualifications. In other words, only <type>jsonb</type> operators can
+    take advantage of GIN indexes; <type>jsonb</type> functions cannot.
+    </para>
+  </note>
   <para>
     The default GIN operator class for <type>jsonb</type> supports queries with
     the key-exists operators <literal>?</literal>, <literal>?|</literal>

Best,

David

---

From: Tom Lane <[email-masked]>
Date: Fri, 15 Sep 2023 17:14:45 -0400

> How do you feel about this note, then?

I think it's unnecessary.  If we did consider it necessary,
why wouldn't just about every subsection in chapter 8 need
similar wording?

			regards, tom lane

---

From: "David E. Wheeler" <[email-masked]>
Date: Sat, 16 Sep 2023 13:43:47 -0400

On Sep 15, 2023, at 20:36, Tom Lane <[email-masked]> wrote:

> I think that that indicates that you're putting the info in the
> wrong place.  Perhaps the right answer is to insert something
> more explicit in section 11.2, which is the first place where
> we really spend any effort discussing what can be indexed.

Fair enough. How ’bout this?

--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -120,7 +120,7 @@ CREATE INDEX test1_id_index ON test1 (id);
    B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension <link
    linkend="bloom">bloom</link>.
    Each index type uses a different
-   algorithm that is best suited to different types of queries.
+   algorithm that is best suited to different types of queries and operators.
    By default, the <link linkend="sql-createindex"><command>CREATE
    INDEX</command></link> command creates
    B-tree indexes, which fit the most common situations.
@@ -132,6 +132,14 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable>
 </programlisting>
   </para>

+  <note>
+    <para>
+    Only operators on indexed columns are considered as index qualifications.
+    Functions never qualify for index usage, aside from
+    <link linkend="indexes-expressional">indexes on expressions</link>.
+    </para>
+  </note>
+
   <sect2 id="indexes-types-btree">
    <title>B-Tree</title>

---

From: Tom Lane <[email-masked]>
Date: Sun, 17 Sep 2023 12:20:50 -0400

> Fair enough. How ’bout this?

After thinking about it for awhile, I think we need some more
discursive explanation of what's allowed, perhaps along the lines
of the attached.  (I still can't shake the feeling that this is
duplicative; but I can't find anything comparable until you get
into the weeds in Section V.)

I put the new text at the end of section 11.1, but perhaps it
belongs a little further up in that section; it seems more
important than some of the preceding paras.

			regards, tom lane

diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 55122129d5..1a0b003fb0 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -109,6 +109,39 @@ CREATE INDEX test1_id_index ON test1 (id);
    Therefore indexes that are seldom or never used in queries
    should be removed.
   </para>
+
+  <para>
+   In general, <productname>PostgreSQL</productname> indexes can be used
+   to optimize queries that contain one or more <literal>WHERE</literal>
+   or <literal>JOIN</literal> clauses of the form
+
+<synopsis>
+<replaceable>indexed-column</replaceable> <replaceable>indexable-operator</replaceable> <replaceable>comparison-value</replaceable>
+</synopsis>
+
+   Here, the <replaceable>indexed-column</replaceable> is whatever
+   column or expression the index has been defined on.
+   The <replaceable>indexable-operator</replaceable> is an operator that
+   is a member of the index's <firstterm>operator class</firstterm> for
+   the indexed column.  (More details about that appear below.)
+   And the <replaceable>comparison-value</replaceable> can be any
+   expression that is not volatile and does not reference the index's
+   table.
+  </para>
+
+  <para>
+   In some cases the query planner can extract an indexable clause of
+   this form from another SQL construct.  A simple example is that if
+   the original clause was
+
+<synopsis>
+<replaceable>comparison-value</replaceable> <replaceable>operator</replaceable> <replaceable>indexed-column</replaceable>
+</synopsis>
+
+   then it can be flipped around into indexable form if the
+   original <replaceable>operator</replaceable> has a commutator
+   operator that is a member of the index's operator class.
+  </para>
  </sect1>

@@ -120,7 +153,7 @@ CREATE INDEX test1_id_index ON test1 (id);
    B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension <link
    linkend="bloom">bloom</link>.
    Each index type uses a different
-   algorithm that is best suited to different types of queries.
+   algorithm that is best suited to different types of indexable clauses.
    By default, the <link linkend="sql-createindex"><command>CREATE
    INDEX</command></link> command creates
    B-tree indexes, which fit the most common situations.

---

From: "David E. Wheeler" <[email-masked]>
Date: Sun, 17 Sep 2023 18:09:36 -0400

> important than some of the preceding paras.

I think this is useful, but also that it’s worth calling out explicitly that functions do not count as indexable operators. True by definition, of course, but I at least had assumed that since an operator is, in a sense, syntax sugar for a function call, they are in some sense the same thing.

A header might be useful, something like “What Counts as an indexable expression”.

Best,

David

---

From: "David E. Wheeler" <[email-masked]>
Date: Sun, 17 Dec 2023 12:55:05 -0500

> A header might be useful, something like “What Counts as an indexable expression”.

Hey Tom, are you still thinking about adding this bit to the docs? I took a quick look at master and didn’t see it there.

Thanks,

David

---

From: Tom Lane <[email-masked]>
Date: Sun, 17 Dec 2023 16:08:45 -0500

> Hey Tom, are you still thinking about adding this bit to the docs? I took a quick look at master and didn’t see it there.

I'd waited because the discussion was still active, and then it
kind of slipped off the radar.  I'll take another look and push
some form of what I suggested.  That doesn't really address the
jsonpath oddities you were on about, though.

			regards, tom lane

---

From: "David E. Wheeler" <[email-masked]>
Date: Sun, 17 Dec 2023 18:30:10 -0500

On Dec 17, 2023, at 16:08, Tom Lane <[email-masked]> wrote:

> some form of what I suggested.

Right on.

> That doesn't really address the

No, I attempted to address those in [a patch][1].

  [1]: https://commitfest.postgresql.org/45/4624/

Best,

David