Thread: Primary Key index
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? Thanks -- Thom Brown Registered Linux user: #516935
Attachment
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
Attachment
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
Attachment
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
Attachment
"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