X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DE7BAD1D174 for ; Tue, 17 Feb 2004 12:38:20 +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 30229-10 for ; Tue, 17 Feb 2004 08:38:22 -0400 (AST) Received: from expasy-ng.isb-sib.ch (expasy-f.unige.ch [192.33.215.142]) by svr1.postgresql.org (Postfix) with ESMTP id 8A76AD1D202 for ; Tue, 17 Feb 2004 08:38:18 -0400 (AST) Received: from caliente (router.isb-sib.ch [192.33.215.254]) (authenticated bits=0) by expasy-ng.isb-sib.ch (8.12.10/8.12.8) with ESMTP id i1HCcKYn006984 for ; Tue, 17 Feb 2004 13:38:20 +0100 Message-ID: <00ed01c3f552$ec054e80$c300000a@caliente> From: "Eric Jain" To: "pgsql-performance" Subject: UPDATE with subquery too slow Date: Tue, 17 Feb 2004 13:38:19 +0100 Organization: Swiss Institute of Bioinformatics MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-sib-MailScanner-Information: Please contact the ISP for more information X-sib-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/181 X-Sequence-Number: 5705 I can't get the following statement to complete with reasonable time. I've had it running for over ten hours without getting anywhere. I suspect (hope) there may be a better way to accomplish what I'm trying to do (set fields containing unique values to null): UPDATE requests SET session = NULL WHERE session IN ( SELECT session FROM requests GROUP BY session HAVING COUNT(*) = 1 ); Output of EXPLAIN: Nested Loop (cost=170350.16..305352.37 rows=33533 width=98) -> HashAggregate (cost=170350.16..170350.16 rows=200 width=8) -> Subquery Scan "IN_subquery" (cost=169728.12..170261.30 rows=35545 width=8) -> HashAggregate (cost=169728.12..169905.85 rows=35545 width=8) Filter: (count(*) = 1) -> Seq Scan on requests (cost=0.00..139207.75 rows=6104075 width=8) -> Index Scan using requests_session_idx on requests (cost=0.00..672.92 rows=168 width=106) Index Cond: (requests."session" = "outer"."session") If I drop the index on requests(session): Hash Join (cost=170350.66..340414.12 rows=33533 width=98) Hash Cond: ("outer"."session" = "inner"."session") -> Seq Scan on requests (cost=0.00..139207.75 rows=6104075 width=106) -> Hash (cost=170350.16..170350.16 rows=200 width=8) -> HashAggregate (cost=170350.16..170350.16 rows=200 width=8) -> Subquery Scan "IN_subquery" (cost=169728.12..170261.30 rows=35545 width=8) -> HashAggregate (cost=169728.12..169905.85 rows=35545 width=8) Filter: (count(*) = 1) -> Seq Scan on requests (cost=0.00..139207.75 rows=6104075 width=8) The subquery itself requires 5-10 min to run on its own, and may return several million rows. Using EXISTS rather than IN (I'm using 7.4-RC2, not sure if IN queries were already improved in this release): UPDATE requests SET session = NULL WHERE NOT EXISTS ( SELECT r.session FROM requests r WHERE r.session = session AND NOT r.id = id ); With and without index: Result (cost=227855.74..415334.22 rows=8075449 width=101) One-Time Filter: (NOT $0) InitPlan -> Seq Scan on requests r (cost=0.00..227855.74 rows=201 width=8) Filter: (("session" = "session") AND (id <> id)) -> Seq Scan on requests (cost=0.00..187478.49 rows=8075449 width=101) I've been running this for more than an hour so far, and no end in sight, either... Any ideas?