Foreign keys
I have general question about FOREIGN KEYs:
1. Suppose I have table A with primary key X, and another table B with
field Y.
2. When I 'ALTER TABLE "B" ADD FOREIGN KEY( "Y" ) REFERENCES "A" ON
UPDATE CASCADE ON DELETE CASCADE', that clearly spends some time
building a separate index. Since there is already a unique index on
X, presumably (?) the index being built is on Y.
3. However, the PostgreSQL documentation seems to indicate that it's a
good idea to also separately create an index on Y.
4. Why, and why is the FOREIGN KEY index different from the ones on X
and Y in any way but trivial?
5. If I need the separate index on Y, should it be built before or
after the FOREIGN KEY constraint?
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
On 12/18/2013 11:02 AM, Dean Gibson (DB Administrator) wrote:
I have general question about FOREIGN KEYs:
1. Suppose I have table A with primary key X, and another table B
with field Y.
2. When I 'ALTER TABLE "B" ADD FOREIGN KEY( "Y" ) REFERENCES "A"
ON UPDATE CASCADE ON DELETE CASCADE', that clearly spends some
time building a separate index. Since there is already a unique
index on X, presumably (?) the index being built is on Y.
3. However, the PostgreSQL documentation seems to indicate that it's
a good idea to also separately create an index on Y.
4. Why, and why is the FOREIGN KEY index different from the ones on X
and Y in any way but trivial?
5. If I need the separate index on Y, should it be built before or
after the FOREIGN KEY constraint?--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
Perhaps you
wanthttp://www.postgresql.org/docs/9.3/static/sql-altertable.html
add table_constraint_using_index
Dean Gibson (DB Administrator) <postgresql@ultimeth.com> wrote:
I have general question about FOREIGN KEYs:
1. Suppose I have table A with primary key X, and another
table B with field Y.
2. When I 'ALTER TABLE "B" ADD FOREIGN KEY( "Y" ) REFERENCES
"A" ON UPDATE CASCADE ON DELETE CASCADE', that clearly
spends some time building a separate index.
No it doesn't. If you are observing activity at that time, it is
probably from validating that the constraint is initially valid.
3. However, the PostgreSQL documentation seems to indicate
that it's a good idea to also separately create an index on
Y.
It *often* is, but there are various reasons you might not want
such an index, which is why its creation is not automatic.
5. If I need the separate index on Y, should it be built
before or after the FOREIGN KEY constraint?
In some cases it may allow faster initial validation of the
constraint; if I wanted the index I would probably build it before
adding the constraint.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 2013-12-18 10:41, Kevin Grittner wrote:
Dean Gibson (DB Administrator) <postgresql@ultimeth.com> wrote:
... that clearly spends some time building a separate index.
No it doesn't. If you are observing activity at that time, it is probably from validating that the constraint is initially valid.
Ah ha! That's what's consuming the time! Thanks!
5. If I need the separate index on Y, should it be built before or after the FOREIGN KEY constraint?
In some cases it may allow faster initial validation of the constraint; if I wanted the index I would probably build it before adding the constraint.
Again, that's what I needed to know! Thanks again!
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general