Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0G4wTV02942 for ; Tue, 15 Jan 2002 23:58:29 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id g0G4oGw15491 for pgsql-sql@postgresql.org; Tue, 15 Jan 2002 23:50:16 -0500 (EST) (envelope-from news) From: jboes@nexcerpt.com (Jeff Boes) X-Newsgroups: comp.databases.postgresql.sql Subject: Long-running DELETE Date: 15 Jan 2002 11:02:22 -0800 Organization: http://groups.google.com/ Lines: 37 Message-ID: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200201/173 X-Sequence-Number: 6259 Okay, this is really starting to fry me: I'm trying to delete 1000 rows from a table which is referenced by several different other tables as a foreign key. I've turned on debug_print_query to help track this down. When I do deletes from some tables, I can delete hundreds of rows per second. When I delete from the main table, it's about 1 row per second. I've turned off every trigger in the database: update pg_triggers set tgenabled=false; I read that such a change might require restarting the backend, so I've gone so far as to bounce the database after this step. What I see in the log is DEBUG: query: delete from a where id = 49; DEBUG: query: SELECT oid FROM ONLY "a_r" WHERE "id" = $1 FOR UPDATE OF "a_r" DEBUG: query: SELECT oid FROM ONLY "a_c" WHERE "id" = $1 FOR UPDATE OF "a_c" DEBUG: query: SELECT oid FROM ONLY "j" WHERE "id" = $1 FOR UPDATE OF "j" DEBUG: query: SELECT oid FROM ONLY "a_r" WHERE "id" = $1 FOR UPDATE OF "a_r" DEBUG: query: SELECT oid FROM ONLY "a_c" WHERE "id" = $1 FOR UPDATE OF "a_c" DEBUG: query: SELECT oid FROM ONLY "j" WHERE "id" = $1 FOR UPDATE OF "j" DEBUG: query: SELECT oid FROM ONLY "a_r" WHERE "id" = $1 FOR UPDATE OF "a_r" DEBUG: query: SELECT oid FROM ONLY "a_c" WHERE "id" = $1 FOR UPDATE OF "a_c" DEBUG: query: SELECT oid FROM ONLY "j" WHERE "id" = $1 FOR UPDATE OF "j" DEBUG: query: delete from a where id = 50; DEBUG: query: delete from a where id = 53; etc.