X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 27C59D1D189 for ; Sun, 25 Jan 2004 02:13:03 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 88310-01 for ; Sat, 24 Jan 2004 22:12:41 -0400 (AST) Received: from alvarezp.ods.org (unknown [200.77.201.28]) by svr1.postgresql.org (Postfix) with ESMTP id D7B3FD1D168 for ; Sat, 24 Jan 2004 22:12:31 -0400 (AST) Received: (from apache@localhost) by alvarezp.ods.org (8.11.6/8.11.6) id i0P2ChK07966; Sat, 24 Jan 2004 18:12:43 -0800 X-Authentication-Warning: alvarezp.ods.org: apache set sender to alvarezp@alvarezp.ods.org using -f Received: from 192.168.0.64 (SquirrelMail authenticated user alvarezp) by alvarezp.ods.org with HTTP; Sat, 24 Jan 2004 18:12:42 -0800 (PST) Message-ID: <2150.192.168.0.64.1074996762.squirrel@alvarezp.ods.org> Date: Sat, 24 Jan 2004 18:12:42 -0800 (PST) Subject: Re: Slow delete times?? From: "Octavio Alvarez" To: pgsql-performance@postgresql.org Reply-To: alvarezp@alvarezp.ods.org User-Agent: SquirrelMail/1.4.0-1.7.x MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/261 X-Sequence-Number: 5435 First of, thanks, Tom. Although I've been very careful on this kind of things, looks like I missed one index on a referencing column. Still, I don't allow an entire delete of a table if it has referencing columns with values, so at the moment of the deletion, it has no rows at all. I checked datatype mismatches, and there are none. All my FKs are integers, like the referenced column of the referenced table. I was thinking on dropping the indexes before doing the deletes, but Joshua suggested using TRUNCATE instead. Thanks. Octavio. Tom Lane said: > "Octavio Alvarez" writes: >> Please tell me if this timing makes sense to you for a Celeron 433 w/ RAM=256MB dedicated testing server. I expected some slowness, but not this >> high. > > I'll bet you have foreign keys referencing this table, and the > referencing columns do not have indexes. PG will let you do that ... but it makes updates and deletes horribly slow. You generally want to add those indexes. > > If they *are* indexed, check for datatype mismatches. That's > another thing that kills performance ... > > regards, tom lane > -- Octavio Alvarez Piza. E-mail: alvarezp@alvarezp.ods.org -- Octavio Alvarez Piza. E-mail: alvarezp@alvarezp.ods.org