While executing a lot of INSERTs and DELETEs, I had some performance
problems which seemed to result from missing foreign key indexes.
Unfortunately, if doing an EXPLAIN DELETE myTab .... I'm getting only
the first stage of query plan, i.e. "seq scan on myTab". The database
accesses behind the scene to check foreign key constraints don't show
up, so there's no hint that an index might be missing.
Since I got some highly referenced tables, deleting might be a lengthy
process if any single row leads to a full table scan on dozens of other
big tables (example: deleting 4000 rows, duration 500 seconds -> 8rows/sec)
But this doesn't seem to be the whole truth. Actually, in the case
stated above, all referencing tables (about 80) are empty. I performed
this on a rather small database, imagine what happens if the table has
1.000.000 rows and referencing tables are filled too!