Обсуждение: Foreign keys
I have general question about FOREIGN KEYs:
- Suppose I have table A with primary key X, and another table B with field Y.
- 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.
- However, the PostgreSQL documentation seems to indicate that it's a good idea to also separately create an index on Y.
- Why, and why is the FOREIGN KEY index different from the ones on X and Y in any way but trivial?
- 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:Perhaps you want http://www.postgresql.org/docs/9.3/static/sql-altertable.html
- Suppose I have table A with primary key X, and another table B with field Y.
- 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.
- However, the PostgreSQL documentation seems to indicate that it's a good idea to also separately create an index on Y.
- Why, and why is the FOREIGN KEY index different from the ones on X and Y in any way but trivial?
- 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.
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
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 initiallyvalid. 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 itbefore 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.