John McCawley <nospam@hardgeus.com> writes:
> In looking at the "\d tbl_claim" output, there is something odd I
> notice. I have many foreign keys (the claim_id in tbl_claim is
> referenced by 12 or so other tables, and tbl_claim references about 6 or
> so tables by their _id)
It seems a good bet that the poor performance is due to lack of indexes
on the columns that reference tbl_claim from other tables. PG enforces
an index on the referenced side of an FK constraint, but not on the
referencing side. This is OK if you mostly update the referencing
table, but it hurts for updates and deletes on the referenced table.
Try creating those indexes. (You'll likely need to start a fresh
psql session afterwards to make sure that the RI mechanism notices
the new indexes.)
> Which matches the syntax I used to create them, however all of my older
> foreign keys are under the Triggers section and are defined as follows:
> "RI_ConstraintTrigger_23354821" AFTER INSERT OR UPDATE ON tbl_claim
> FROM tbl_agents NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_check_ins"('fk_tbl_claim_tbl_agents_fk', 'tbl_claim',
> 'tbl_agents', 'UNSPECIFIED', 'agent_id', 'agent_id')
These are probably inherited from some pre-7.3-or-so schema? I'd
suggest dropping those triggers and recreating the constraints with
ALTER TABLE ADD CONSTRAINT. You could also look at contrib/adddepend/
which is alleged to fix such things automatically (but I wouldn't
trust it too much, because it's not been maintained since 7.3).
This won't make any difference to performance, but it'll clean up your
schema into a more future-proof form.
regards, tom lane