[GENERAL] Foreign keys and indexes
Dear list, This might be too basic for a question but I just couldn't find the answer so far. Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? Thank you, Marc Compte ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Foreign keys and indexes
2007/6/5, Marc Compte [EMAIL PROTECTED]: Dear list, This might be too basic for a question but I just couldn't find the answer so far. Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? FK is just a constraint, you wil have to create indexes manually if you need them. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Foreign keys and indexes
On þri, 2007-06-05 at 11:49 +0200, Marc Compte wrote: Does PostgreSQL create an implicit index also for foreign keys? no or must I create it explicitly? if you want one, yes. not everyone wants an index on all their foreign keys, but they can be useful in some circumstances. gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Foreign keys and indexes
On Tue, Jun 05, 2007 at 11:49:20AM +0200, Marc Compte wrote: Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? PostgreSQL doesn't create an index on the referencing column(s) of a foreign key constraint; if you want an index then you'll need to create it yourself. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Foreign keys and indexes
Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? No, you foreign keys are not automatically indexed. They only way they would be is if the FK is part of a composite unique or primary key. So you will probably have to create your one indexes on FKs. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Foreign keys and indexes
am Tue, dem 05.06.2007, um 11:49:20 +0200 mailte Marc Compte folgendes: Dear list, This might be too basic for a question but I just couldn't find the answer so far. Does PostgreSQL create an implicit index also for foreign keys? No, only for primary keys to enforce the uniqueness. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Foreign keys and indexes
Marc Compte [EMAIL PROTECTED] writes: Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? It won't allow you to create a foreign key that points to a column without a unique index on it. postgres=# create table b (i integer references a(i)); ERROR: there is no unique constraint matching given keys for referenced table a However if you ever update or delete the referenced records then it also helps performance to have an index on the referencing column which Postgres doesn't enforce. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Foreign keys and indexes
Thanks to everyone for the prompt reply :) Good thing about answers is when they raise up new questiosn, so you can keep on learning all the time. This one answer, for instance, brings me another question. Does having a composite primary mean the system will create an individual index on each of the fields? or is the index created on the composition only? For instance, in the implementation of a N:M relationship, declaring the primary as (foreign1, foreign2) will create two indexes? or just one? Thanks again Marc Compte En/na Richard Broersma Jr ha escrit: Does PostgreSQL create an implicit index also for foreign keys? or must I create it explicitly? No, you foreign keys are not automatically indexed. They only way they would be is if the FK is part of a composite unique or primary key. So you will probably have to create your one indexes on FKs. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign keys and indexes
Marc Compte wrote: Thanks to everyone for the prompt reply :) Good thing about answers is when they raise up new questiosn, so you can keep on learning all the time. This one answer, for instance, brings me another question. Does having a composite primary mean the system will create an individual index on each of the fields? or is the index created on the composition only? For instance, in the implementation of a N:M relationship, declaring the primary as (foreign1, foreign2) will create two indexes? or just one? Just one (and please don't top post. :) ) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Foreign keys and indexes
On Tue, 2007-06-05 at 17:07 +0200, Marc Compte wrote: For instance, in the implementation of a N:M relationship, declaring the primary as (foreign1, foreign2) will create two indexes? or just one? Just one -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do you want to know God? http://www.lfix.co.uk/knowing_god.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
