Primary Key index

Thom Brown <thom@linux.com> wrote:

In response to a user asking a question about indexes on primary
keys

(http://archives.postgresql.org/pgsql-performance/2010-08/msg00194.php)

I attach a patch to add information to the Primary Keys section of
the Constraints page. While the information already exists on the
CREATE TABLE, I don't think a brief mention on the page specifically
concerning primary keys could hurt.

So here's a patch to add it. Worth adding?

I think so, but I think we should cover UNIQUE constraints, too. I
was also thinking about possibly mentioning it in the index overview
page, and adding an entry or two to the documentation index, but
maybe that's overkill.

-Kevin

On 18 August 2010 17:09, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

Thom Brown <thom@linux.com> wrote:

In response to a user asking a question about indexes on primary
keys

(http://archives.postgresql.org/pgsql-performance/2010-08/msg00194.php)

I attach a patch to add information to the Primary Keys section of
the Constraints page.  While the information already exists on the
CREATE TABLE, I don't think a brief mention on the page specifically
concerning primary keys could hurt.

So here's a patch to add it.  Worth adding?

I think so, but I think we should cover UNIQUE constraints, too.  I
was also thinking about possibly mentioning it in the index overview
page, and adding an entry or two to the documentation index, but
maybe that's overkill.

Well I guess the question is: "where will most people first look to
find that piece of information out?"

As long as the information isn't digressing from the topic it's
mentioned in, I don't see the problem. :)

--
Thom Brown
Registered Linux user: #516935

Thom Brown <thom@linux.com> wrote:

Well I guess the question is: "where will most people first look
to find that piece of information out?"

The OP mentioned looking in the Indexes section of the documentation
for the answer.

As long as the information isn't digressing from the topic it's
mentioned in, I don't see the problem. :)

The Introduction to the Indexes section mentions how to create and
drop indexes, without any mention of indexes tied to these
constraints.

-Kevin

On ons, 2010-08-18 at 16:52 +0100, Thom Brown wrote:

In response to a user asking a question about indexes on primary keys
(http://archives.postgresql.org/pgsql-performance/2010-08/msg00194.php)
I attach a patch to add information to the Primary Keys section of the
Constraints page. While the information already exists on the CREATE
TABLE, I don't think a brief mention on the page specifically
concerning primary keys could hurt.

So here's a patch to add it. Worth adding?

<firstterm> is probably not appropriate here, because you are not
defining the term for the first time.

On 18 August 2010 21:53, Peter Eisentraut <peter_e@gmx.net> wrote:

On ons, 2010-08-18 at 16:52 +0100, Thom Brown wrote:

In response to a user asking a question about indexes on primary keys
(http://archives.postgresql.org/pgsql-performance/2010-08/msg00194.php)
I attach a patch to add information to the Primary Keys section of the
Constraints page.  While the information already exists on the CREATE
TABLE, I don't think a brief mention on the page specifically
concerning primary keys could hurt.

So here's a patch to add it.  Worth adding?

<firstterm> is probably not appropriate here, because you are not
defining the term for the first time.

That is true.
--
Thom Brown
Registered Linux user: #516935

Thom Brown <thom@linux.com> wrote:

On 18 August 2010 21:53, Peter Eisentraut <peter_e@gmx.net> wrote:

On ons, 2010-08-18 at 16:52 +0100, Thom Brown wrote:

I attach a patch to add information to the Primary Keys section
of the Constraints page. While the information already exists
on the CREATE TABLE, I don't think a brief mention on the page
specifically concerning primary keys could hurt.

So here's a patch to add it. Worth adding?

<firstterm> is probably not appropriate here, because you are not
defining the term for the first time.

That is true.

It looks like discussion died here. Do you want to propose a new
patch? (I'd be happy to give it a shot if you'd rather.) In any
event, we should probably mention this for all three constraint
types which automatically create an index: PRIMARY KEY, UNIQUE, and
EXCLUSION. It might even be worth mentioning in the FOREIGN KEY
section that in PostgreSQL these are *not* created automatically,
and it is often wise to do so manually. I've seen a few posts from
people who don't understand why their deletes run so slowly, because
they're used to other database products which automatically create
an index on the referencing side of a foreign key.

-Kevin

On 25 August 2010 20:15, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

Thom Brown <thom@linux.com> wrote:

On 18 August 2010 21:53, Peter Eisentraut <peter_e@gmx.net> wrote:

On ons, 2010-08-18 at 16:52 +0100, Thom Brown wrote:

I attach a patch to add information to the Primary Keys section
of the Constraints page.  While the information already exists
on the CREATE TABLE, I don't think a brief mention on the page
specifically concerning primary keys could hurt.

So here's a patch to add it.  Worth adding?

<firstterm> is probably not appropriate here, because you are not
defining the term for the first time.

That is true.

It looks like discussion died here.  Do you want to propose a new
patch?  (I'd be happy to give it a shot if you'd rather.)

Sure, go for it. :)

 In any
event, we should probably mention this for all three constraint
types which automatically create an index: PRIMARY KEY, UNIQUE, and
EXCLUSION.

Agreed, and I didn't actually think about the fact that exclusion
constraints add indexes.

--
Thom Brown
Registered Linux user: #516935

Thom Brown <thom@linux.com> wrote:

Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

It looks like discussion died here. Do you want to propose a new
patch? (I'd be happy to give it a shot if you'd rather.)

Sure, go for it. :)

Initial stab at it attached.

I'm torn on whether the paragraph I added to the foreign key
constraint section should be in a warning block -- we do see
complaints from time-to-time from people who are surprised by slow
deletes from referenced tables; in some cases they have come from
database products which automatically create an index on the
referencing columns and are surprised that they need to choose
whether and how to do so in PostgreSQL.

I think we may want to link to these sections from the appropriate
sections of CREATE TABLE (and possibly ALTER TABLE), but that seems
like it could be a separate patch.

Exclusion constraint documentation relies rather more heavily on the
CREATE TABLE page, and is skimpy on the constraints page. I think
that the CREATE TABLE page should focus on syntax and an overview,
and link to the constraints page for any in-depth information.
Again, that seems like it could be addressed separately, but it
seemed worth mentioning, since I stumbled across it.

-Kevin

On 26 August 2010 18:50, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

Thom Brown <thom@linux.com> wrote:

Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

It looks like discussion died here.  Do you want to propose a new
patch?  (I'd be happy to give it a shot if you'd rather.)

Sure, go for it. :)

Initial stab at it attached.

I'm torn on whether the paragraph I added to the foreign key
constraint section should be in a warning block -- we do see
complaints from time-to-time from people who are surprised by slow
deletes from referenced tables; in some cases they have come from
database products which automatically create an index on the
referencing columns and are surprised that they need to choose
whether and how to do so in PostgreSQL.

I think we may want to link to these sections from the appropriate
sections of CREATE TABLE (and possibly ALTER TABLE), but that seems
like it could be a separate patch.

Exclusion constraint documentation relies rather more heavily on the
CREATE TABLE page, and is skimpy on the constraints page.  I think
that the CREATE TABLE page should focus on syntax and an overview,
and link to the constraints page for any in-depth information.
Again, that seems like it could be addressed separately, but it
seemed worth mentioning, since I stumbled across it.

-Kevin

Looks good. Do we usually got into fine details such as the name of
the index? They'll see the index name returned when they create the
table or add the constraint anyway, and if they missed it they only
need to do a "\dt tablename" to find out what it was.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom Brown <thom@linux.com> wrote:

Looks good. Do we usually got into fine details such as the name
of the index? They'll see the index name returned when they
create the table or add the constraint anyway, and if they missed
it they only need to do a "\dt tablename" to find out what it was.

Hmmm... Perhaps that is overkill. It seemed like a good idea at
the time, but I'm not inclined to argue about it if it seems too
detailed to you. Revised patch attached.

-Kevin

On 26 August 2010 20:16, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

Thom Brown <thom@linux.com> wrote:

Looks good.  Do we usually got into fine details such as the name
of the index?  They'll see the index name returned when they
create the table or add the constraint anyway, and if they missed
it they only need to do a "\dt tablename" to find out what it was.

Hmmm...  Perhaps that is overkill.  It seemed like a good idea at
the time, but I'm not inclined to argue about it if it seems too
detailed to you.  Revised patch attached.

-Kevin

Yeah, I think that covers it well. :)

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom Brown <thom@linux.com> wrote:

Yeah, I think that covers it well. :)

I found a typo. :-( Another revision attached.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Thom Brown <thom@linux.com> wrote:

Yeah, I think that covers it well. :)

I found a typo. :-( Another revision attached.

Applied to HEAD and 9.0, with a couple of trivial editorial adjustments.

regards, tom lane