Trigger for constraint source_t_id_fkey: time=0.557 calls=1 > Index Scan using source_t_id_idx on source The queries are as fast as they should be: Trigger for constraint source_t_id_fkey: time=153.804 calls=1ĬREATE INDEX source_t_id_idx ON source (t_id) > Index Scan using target_t_name_idx on target Looking up source rows via the link to target and deleting rows from target is unreasonably slow: SELECT i, (i - 1) % 500000 + 1, 'source ' || iĪDD FOREIGN KEY (t_id) REFERENCES target * set hint bits and collect statistics */ Without an index, this requires a sequential scan of the source table. It does so by searching if there are rows in the source table that would become orphaned by the data modification. Then PostgreSQL has to check if the foreign key constraint is still satisfied. You delete rows or update key columns in the target table. If there is an index on the columns at the source, PostgreSQL can use an efficient nested loop join.Ģ. You perform a join between the two tables where you explicitly search for the source rows referencing one or a few target rows. The typical cases where you need that are:ġ. However, such an index is quite useful for finding all source rows that reference a target row. In contrast to the above, PostgreSQL requires no index at the source of a foreign key. The index also comes handy if you want to find the row in the target table that matches a row in the source table. This is required so that there is always a well-defined row to which the foreign key points. Consequently, the target side of a foreign key is automatically indexed. Such constraints are implemented with unique indexes in PostgreSQL. The referenced columns in the target table must have a primary key or unique constraint. In the following, I will call the table, on which the foreign key constraint is defined, the source table and the referenced table the target table. This article will explain that and show you how to search for missing indexes. INNER JOIN information_schema.Foreign key index performance © Laurenz Albe 2018įoreign key constraints are an important tool to keep your database consistent while also documenting relationships between tables.Ī fact that is often ignored is that foreign keys need proper indexing to perform well. USING (constraint_catalog, constraint_schema, constraint_name) INNER JOIN information_nstraint_column_usage ccu The following query checks for a foreign key between the two tables without relying on the generated constraint name: SELECT 1įROM information_schema.table_constraints tc It's probably safer to use information_schema to check for the presence of a constraint that links the two columns. You seem to be relying on the default constraint name generation, which isn't ideal. IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey') THENįOREIGN KEY (contact_id) REFERENCES ntact_item(id) Use a DO block to execute it in PL/PgSQL. Or just ALTER TABLE common.client_contactĪDD FOREIGN KEY IF NOT EXISTS (contact_id) REFERENCES ntact_item(id)īut these two queries are produce syntax error. (SELECT * FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey')ĪDD CONSTRAINT client_contact_contact_id_fkeyįOREIGN KEY (contact_id) REFERENCES ntact_item(id) I check if this constraint exists in pg_constraint table: SELECT * FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey'Īnd now I need to combine them together. So, before creating new constraint, I need to check if it's exists. If I execute this code, I will get several foreign keys with different names (like client_contact_contact_id_fkey1, client_contact_contact_id_fkey2, client_contact_contact_id_fke圓 and so on). I have a table common.client_contact where I created foreign key using this code: ALTER TABLE common.client_contactĪDD FOREIGN KEY (contact_id) REFERENCES ntact_item(id)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |