Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhuW2-004fru-Ad for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Sep 2023 16:20:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qhuW0-00BPd1-VZ for pgsql-hackers@arkaria.postgresql.org; Sun, 17 Sep 2023 16:20:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhuW0-00BPcP-L8 for pgsql-hackers@lists.postgresql.org; Sun, 17 Sep 2023 16:20:56 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhuVx-0055st-Ql for pgsql-hackers@lists.postgresql.org; Sun, 17 Sep 2023 16:20:55 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 38HGKoXN4097443; Sun, 17 Sep 2023 12:20:50 -0400 From: Tom Lane To: "David E. Wheeler" cc: pgsql-hackers@lists.postgresql.org Subject: Re: JSON Path and GIN Questions In-reply-to: References: <15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com> <133696180.303713.1694566807910@office.mailbox.org> <640942d7-c996-0562-62c9-d69a4c1f9b1e@xs4all.nl> <3063408.1694666518@sss.pgh.pa.us> <3802532.1694812485@sss.pgh.pa.us> <8504C88E-A036-47F4-8FAE-9BA95F55CB03@justatheory.com> <3826235.1694824574@sss.pgh.pa.us> Comments: In-reply-to "David E. Wheeler" message dated "Sat, 16 Sep 2023 13:43:47 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <4097287.1694967560.0@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Sun, 17 Sep 2023 12:20:50 -0400 Message-ID: <4097442.1694967650@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="UTF-8" Content-ID: <4097287.1694967560.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit "David E. Wheeler" writes: > On Sep 15, 2023, at 20:36, Tom Lane 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? 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="v1-document-indexable-clauses-better.patch"; charset="us-ascii" Content-ID: <4097287.1694967560.2@sss.pgh.pa.us> Content-Description: v1-document-indexable-clauses-better.patch Content-Transfer-Encoding: quoted-printable 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. + + + In general, PostgreSQL indexes can be used + to optimize queries that contain one or more WHERE + or JOIN clauses of the form + + +indexed-column indexable-operator= comparison-value + + + Here, the indexed-column is whatever + column or expression the index has been defined on. + The indexable-operator is an operator that + is a member of the index's operator class for + the indexed column. (More details about that appear below.) + And the comparison-value can be any + expression that is not volatile and does not reference the index's + table. + + + + 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 + + +comparison-value operator indexed-column + + + then it can be flipped around into indexable form if the + original operator has a commutator + operator that is a member of the index's operator class. + = = @@ -120,7 +153,7 @@ CREATE INDEX test1_id_index ON test1 (id); B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension bloom. 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 CREATE INDEX command creates B-tree indexes, which fit the most common situations. ------- =_aaaaaaaaaa0--