EXPLAIN not helpful for DELETE

From: Andreas Pflug <Andreas(dot)Pflug(at)web(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: EXPLAIN not helpful for DELETE
Date: 2003-02-04 15:11:22
Message-ID: 3E3FD81A.3010307@web.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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!

Browse pgsql-performance by date

  From Date Subject
Next Message philip johnson 2003-02-05 10:54:44 how to configure my new server
Previous Message jeandre 2003-02-04 14:48:53 subscribe