X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D9EE1475FC5 for ; Tue, 4 Feb 2003 10:16:25 -0500 (EST) Received: from smtp.web.de (smtp03.web.de [217.72.192.158]) by postgresql.org (Postfix) with ESMTP id CE45E475F53 for ; Tue, 4 Feb 2003 10:11:11 -0500 (EST) Received: from p50818a9b.dip0.t-ipconnect.de ([80.129.138.155] helo=web.de) by smtp.web.de with asmtp (WEB.DE(Exim) 4.93 #1) id 18g4jD-0000ZU-00 for pgsql-performance@postgresql.org; Tue, 04 Feb 2003 16:11:15 +0100 Message-ID: <3E3FD81A.3010307@web.de> Date: Tue, 04 Feb 2003 16:11:22 +0100 From: Andreas Pflug User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3a) Gecko/20021212 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: EXPLAIN not helpful for DELETE Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/13 X-Sequence-Number: 1069 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!