pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPrimary Key index
14+ messages / 4 participants
[nested] [flat]
* Primary Key index
@ 2010-08-18 15:52 Thom Brown <thom@linux.com>
2010-08-18 16:09 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 14+ messages in thread
From: Thom Brown @ 2010-08-18 15:52 UTC (permalink / raw)
To: pgsql-docs
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
Attachments:
primary_key_index.patch (application/octet-stream, 627B) download
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 9ad29e7..16e3e6f 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -623,6 +623,12 @@ CREATE TABLE example (
</para>
<para>
+ Adding a primary key will also result in a <firstterm>unique index</>
+ being created on the column or group of columns the primary key
+ consists of.
+ </para>
+
+ <para>
A table can have at most one primary key. (There can be any number
of unique and not-null constraints, which are functionally the same
thing, but only one can be identified as the primary key.)
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
@ 2010-08-18 16:09 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-18 16:23 ` Re: Primary Key index Thom Brown <thom@linux.com>
1 sibling, 1 reply; 14+ messages in thread
From: Kevin Grittner @ 2010-08-18 16:09 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; pgsql-docs
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 16:09 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-08-18 16:23 ` Thom Brown <thom@linux.com>
2010-08-18 17:00 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 14+ messages in thread
From: Thom Brown @ 2010-08-18 16:23 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-docs
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 16:09 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-18 16:23 ` Re: Primary Key index Thom Brown <thom@linux.com>
@ 2010-08-18 17:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 14+ messages in thread
From: Kevin Grittner @ 2010-08-18 17:00 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-docs
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
@ 2010-08-18 20:53 ` Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Re: Primary Key index Thom Brown <thom@linux.com>
1 sibling, 1 reply; 14+ messages in thread
From: Peter Eisentraut @ 2010-08-18 20:53 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-docs
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
@ 2010-08-18 21:38 ` Thom Brown <thom@linux.com>
2010-08-25 19:15 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 14+ messages in thread
From: Thom Brown @ 2010-08-18 21:38 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-docs
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Re: Primary Key index Thom Brown <thom@linux.com>
@ 2010-08-25 19:15 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-25 19:21 ` Re: Primary Key index Thom Brown <thom@linux.com>
0 siblings, 1 reply; 14+ messages in thread
From: Kevin Grittner @ 2010-08-25 19:15 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; Thom Brown <thom@linux.com>; +Cc: pgsql-docs
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-25 19:15 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-08-25 19:21 ` Thom Brown <thom@linux.com>
2010-08-26 17:50 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 14+ messages in thread
From: Thom Brown @ 2010-08-25 19:21 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-docs
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-25 19:15 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-25 19:21 ` Re: Primary Key index Thom Brown <thom@linux.com>
@ 2010-08-26 17:50 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 18:52 ` Re: Primary Key index Thom Brown <thom@linux.com>
0 siblings, 1 reply; 14+ messages in thread
From: Kevin Grittner @ 2010-08-26 17:50 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-docs
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
Attachments:
constraint-indexes-1.patch (text/plain, 2.7K) download
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
***************
*** 550,555 **** CREATE TABLE products (
--- 550,563 ----
</indexterm>
<para>
+ Adding a unique constraint will automatically create a unique btree
+ index on the column or group of columns declared for the constraint.
+ The index name will match the name given to the constraint; if no
+ constraint name is specified, it will be the table name suffixed
+ with <literal>_<replaceable>columnname_key</replaceable></literal>.
+ </para>
+
+ <para>
In general, a unique constraint is violated when there is more than
one row in the table where the values of all of the
columns included in the constraint are equal.
***************
*** 623,628 **** CREATE TABLE example (
--- 631,644 ----
</para>
<para>
+ Adding a primary key will automatically create a unique btree index
+ on the column or group of columns declared for the primary key.
+ The index name will match the name given to the constraint; if no
+ constraint name is specified, it will be the table name suffixed
+ with <literal>_pkey</literal>.
+ </para>
+
+ <para>
A table can have at most one primary key. (There can be any number
of unique and not-null constraints, which are functionally the same
thing, but only one can be identified as the primary key.)
***************
*** 832,837 **** CREATE TABLE order_items (
--- 848,863 ----
</para>
<para>
+ Since a <command>DELETE</command> of a row from a referenced table
+ or an <command>UPDATE</command> of a referenced column will require
+ a scan of the referencing table for rows matching the old value, it
+ is often a good idea to index the referencing columns. Because it
+ is not always needed, and there are many choices available on how
+ index, declaration of a foreign key constraint does not
+ automatically create an index.
+ </para>
+
+ <para>
More information about updating and deleting data is in <xref
linkend="dml">.
</para>
***************
*** 875,880 **** CREATE TABLE circles (
--- 901,915 ----
See also <link linkend="SQL-CREATETABLE-EXCLUDE"><command>CREATE
TABLE ... CONSTRAINT ... EXCLUDE</></link> for details.
</para>
+
+ <para>
+ Adding an exclusion constraint will automatically create an index
+ of the type specified in the constraint declaration. The index
+ name will match the name given to the constraint; if no constraint
+ name is specified, it will be the table name and column names used
+ in the constraint, separated by underscores, suffixed with
+ <literal>_excl</literal>.
+ </para>
</sect2>
</sect1>
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-25 19:15 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-25 19:21 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-26 17:50 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-08-26 18:52 ` Thom Brown <thom@linux.com>
2010-08-26 19:16 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 14+ messages in thread
From: Thom Brown @ 2010-08-26 18:52 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-docs
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-25 19:15 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-25 19:21 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-26 17:50 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 18:52 ` Re: Primary Key index Thom Brown <thom@linux.com>
@ 2010-08-26 19:16 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 19:19 ` Re: Primary Key index Thom Brown <thom@linux.com>
0 siblings, 1 reply; 14+ messages in thread
From: Kevin Grittner @ 2010-08-26 19:16 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-docs
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
Attachments:
constraint-indexes-2.patch (text/plain, 2.1K) download
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
***************
*** 550,555 **** CREATE TABLE products (
--- 550,560 ----
</indexterm>
<para>
+ Adding a unique constraint will automatically create a unique btree
+ index on the column or group of columns declared for the constraint.
+ </para>
+
+ <para>
In general, a unique constraint is violated when there is more than
one row in the table where the values of all of the
columns included in the constraint are equal.
***************
*** 623,628 **** CREATE TABLE example (
--- 628,638 ----
</para>
<para>
+ Adding a primary key will automatically create a unique btree index
+ on the column or group of columns declared for the primary key.
+ </para>
+
+ <para>
A table can have at most one primary key. (There can be any number
of unique and not-null constraints, which are functionally the same
thing, but only one can be identified as the primary key.)
***************
*** 832,837 **** CREATE TABLE order_items (
--- 842,857 ----
</para>
<para>
+ Since a <command>DELETE</command> of a row from a referenced table
+ or an <command>UPDATE</command> of a referenced column will require
+ a scan of the referencing table for rows matching the old value, it
+ is often a good idea to index the referencing columns. Because it
+ is not always needed, and there are many choices available on how
+ index, declaration of a foreign key constraint does not
+ automatically create an index.
+ </para>
+
+ <para>
More information about updating and deleting data is in <xref
linkend="dml">.
</para>
***************
*** 875,880 **** CREATE TABLE circles (
--- 895,905 ----
See also <link linkend="SQL-CREATETABLE-EXCLUDE"><command>CREATE
TABLE ... CONSTRAINT ... EXCLUDE</></link> for details.
</para>
+
+ <para>
+ Adding an exclusion constraint will automatically create an index
+ of the type specified in the constraint declaration.
+ </para>
</sect2>
</sect1>
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-25 19:15 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-25 19:21 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-26 17:50 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 18:52 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-26 19:16 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-08-26 19:19 ` Thom Brown <thom@linux.com>
2010-08-26 20:14 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 14+ messages in thread
From: Thom Brown @ 2010-08-26 19:19 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-docs
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-25 19:15 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-25 19:21 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-26 17:50 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 18:52 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-26 19:16 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 19:19 ` Re: Primary Key index Thom Brown <thom@linux.com>
@ 2010-08-26 20:14 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 21:09 ` Re: Primary Key index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Kevin Grittner @ 2010-08-26 20:14 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-docs
Thom Brown <thom@linux.com> wrote:
> Yeah, I think that covers it well. :)
I found a typo. :-( Another revision attached.
-Kevin
Attachments:
constraint-indexes-3.patch (text/plain, 2.1K) download
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
***************
*** 550,555 **** CREATE TABLE products (
--- 550,560 ----
</indexterm>
<para>
+ Adding a unique constraint will automatically create a unique btree
+ index on the column or group of columns declared for the constraint.
+ </para>
+
+ <para>
In general, a unique constraint is violated when there is more than
one row in the table where the values of all of the
columns included in the constraint are equal.
***************
*** 623,628 **** CREATE TABLE example (
--- 628,638 ----
</para>
<para>
+ Adding a primary key will automatically create a unique btree index
+ on the column or group of columns declared for the primary key.
+ </para>
+
+ <para>
A table can have at most one primary key. (There can be any number
of unique and not-null constraints, which are functionally the same
thing, but only one can be identified as the primary key.)
***************
*** 832,837 **** CREATE TABLE order_items (
--- 842,857 ----
</para>
<para>
+ Since a <command>DELETE</command> of a row from a referenced table
+ or an <command>UPDATE</command> of a referenced column will require
+ a scan of the referencing table for rows matching the old value, it
+ is often a good idea to index the referencing columns. Because it
+ is not always needed, and there are many choices available on how
+ to index, declaration of a foreign key constraint does not
+ automatically create an index.
+ </para>
+
+ <para>
More information about updating and deleting data is in <xref
linkend="dml">.
</para>
***************
*** 875,880 **** CREATE TABLE circles (
--- 895,905 ----
See also <link linkend="SQL-CREATETABLE-EXCLUDE"><command>CREATE
TABLE ... CONSTRAINT ... EXCLUDE</></link> for details.
</para>
+
+ <para>
+ Adding an exclusion constraint will automatically create an index
+ of the type specified in the constraint declaration.
+ </para>
</sect2>
</sect1>
* Re: Primary Key index
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 20:53 ` Re: Primary Key index Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-25 19:15 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-25 19:21 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-26 17:50 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 18:52 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-26 19:16 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 19:19 ` Re: Primary Key index Thom Brown <thom@linux.com>
2010-08-26 20:14 ` Re: Primary Key index Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-08-26 21:09 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 2010-08-26 21:09 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Thom Brown <thom@linux.com>; Peter Eisentraut <peter_e@gmx.net>; pgsql-docs
"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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2010-08-26 21:09 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-18 15:52 Primary Key index Thom Brown <thom@linux.com>
2010-08-18 16:09 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-18 16:23 ` Thom Brown <thom@linux.com>
2010-08-18 17:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-18 20:53 ` Peter Eisentraut <peter_e@gmx.net>
2010-08-18 21:38 ` Thom Brown <thom@linux.com>
2010-08-25 19:15 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-25 19:21 ` Thom Brown <thom@linux.com>
2010-08-26 17:50 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 18:52 ` Thom Brown <thom@linux.com>
2010-08-26 19:16 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 19:19 ` Thom Brown <thom@linux.com>
2010-08-26 20:14 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-26 21:09 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox