"David E. Wheeler" <[email protected]> writes:
> On Sep 15, 2023, at 20:36, Tom Lane <[email protected]> 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
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.
