Subject: Documentation for create unique index is insuficient and (because of that) incorrect

From: PG Doc comments form <[email-masked]>
Date: Tue, 23 Oct 2018 18:29:55 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/static/indexes-unique.html
Description:

In https://www.postgresql.org/docs/11/static/indexes-unique.html there are
omited clausules

For example WHERE clausule is omited. 

In https://www.postgresql.org/docs/11/static/sql-createindex.html you can
see the right sintax with all the clausules.

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 19 Nov 2018 21:05:42 -0500

> see the right sintax with all the clausules.

The first URL is an example and is not intended to be the complete
syntax.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Tue, 20 Nov 2018 09:41:56 -0500

> The first URL is an example and is not intended to be the complete

In fairness, the "INCLUDE" clause was added, which is new to PostgreSQL
11, so it could raise the question as to why aren't other clauses there.

Perhaps we could add some more examples? For instance, why one might
want to use "INCLUDE" with a unique index. And perhaps a case of the
"partial unique index" (which I have used in production systems before).

Thanks!

Jonathan

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 20 Nov 2018 11:52:42 -0300

> > The first URL is an example and is not intended to be the complete

I think the OP should take a lengthier look at the whole of chapter 11
instead of looking at just one individual section within it.  It's the
whole chapter that should include details about other indexing features
-- and indeed the WHERE clause is described in 11.8 Partial Indexes.

Describing every feature within the section for every other feature
would be disastrous.

> 11, so it could raise the question as to why aren't other clauses there.

IMO that's actually a bug -- the INCLUDE clause should have gotten its
own section in chapter 11 instead of being mentioned in the section
about unique indexes.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

---

From: Tom Lane <[email-masked]>
Date: Tue, 20 Nov 2018 09:59:18 -0500

> 11, so it could raise the question as to why aren't other clauses there.

Yes.  That was a dumb idea; the correct fix is to take that out, because
it's not appropriate here.  There might be room for an additional section
later in the chapter that discusses INCLUDE, but we shouldn't be
cluttering the discussion of fundamental concepts like unique indexes
with that.

			regards, tom lane

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Tue, 20 Nov 2018 10:02:01 -0500

> cluttering the discussion of fundamental concepts like unique indexes

Shows how closely I read the docs. +1 on removing INCLUDE from UNIQUE
indexes.

Also +1 on having a section on covering indexes.

Jonathan

---

From: Tom Lane <[email-masked]>
Date: Tue, 20 Nov 2018 10:16:42 -0500

> Also +1 on having a section on covering indexes.

I see Alvaro is on the same page here.  I'll go write something
later today.

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Tue, 20 Nov 2018 12:57:45 -0500

> I see Alvaro is on the same page here.  I'll go write something

So looking closer ... not only is the unique-index section not a very
good fit, but there is already a section that this does fit into
pretty well, which is 11.11 on index-only scans.  Not only do you need
to understand index-only scans to understand why you'd want INCLUDE,
but there's already discussion there of making covering indexes the
hard way.

So what I think I should do is reformulate that discussion to talk
about making covering indexes with INCLUDE, and then mention in
passing that you can also do it without that as long as you don't mind
the payload columns being part of the index semantics.

I'm also wondering whether to move that section someplace earlier
in chapter 11.  Right now it's near the end because it's mostly
info about an implementation detail; but it wouldn't be hard to
make the argument that covering indexes are more important than,
say, indexes with custom collations.  Should we move it, and if
so to where?

			regards, tom lane

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 20 Nov 2018 15:08:31 -0300

> the payload columns being part of the index semantics.

That seems sensible.

> say, indexes with custom collations.  Should we move it, and if

I think right next to 11.5, which currently completes the topic of how
are indexes used, is a good place.

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Tue, 20 Nov 2018 13:13:51 -0500

>> the payload columns being part of the index semantics.

+1

> are indexes used, is a good place.

I would vote at least before 11.9. You could make arguments how
understand how multicolumn, expression indexes, partials, etc. could be
more important, or at least used for frequently in the wild (at least
for now). And once we've explained those types then you could understand
how to use covering indexes appropriately.

Jonathan

---

From: Tom Lane <[email-masked]>
Date: Tue, 20 Nov 2018 13:15:30 -0500

> are indexes used, is a good place.

Well, it has to stay after 11.6 (unique indexes), because we do need
to point out that the included columns don't participate in the
uniqueness constraint, and I don't want that statement to be a forward
reference.  Really this boils down to where you rank covering indexes
vs expression indexes and partial indexes in terms of usefulness.
I'd tend to put this after expression indexes, but I don't have a very
strong feeling about how to rank it versus partial indexes.

			regards, tom lane