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 DAEA8D1E12F for ; Wed, 18 Feb 2004 19:12:32 +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 54813-02 for ; Wed, 18 Feb 2004 15:12:27 -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 8DB08D1D315 for ; Wed, 18 Feb 2004 15:12:24 -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 i1IJC1Yn032358 for ; Wed, 18 Feb 2004 20:12:02 +0100 Message-ID: <013401c3f653$14f95970$c300000a@caliente> From: "Eric Jain" To: "pgsql-performance" References: <00ed01c3f552$ec054e80$c300000a@caliente> Subject: Re: UPDATE with subquery too slow Date: Wed, 18 Feb 2004 20:11:58 +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/209 X-Sequence-Number: 5733 > I can't get the following statement to complete with reasonable time. Upgraded to 7.4.1, and realized that NOT IN is far more efficient than IN, EXISTS or NOT EXISTS, at least for the amount and distribution of data that I have. Here are some numbers from before and after performing the problematic clean up operation: | Before | After ------------------------+-----------+----------- COUNT(*) | 6'104'075 | 6'104'075 COUNT(session) | 5'945'272 | 3'640'659 COUNT(DISTINCT session) | 2'865'570 | 560'957 The following query completes within less than three hours on a machine with a high load, versa many many hours for any of the alternatives: UPDATE requests SET session = NULL WHERE session NOT IN ( SELECT r.session FROM requests r WHERE r.session IS NOT NULL GROUP BY r.session HAVING COUNT(*) > 1 ); Note that in order to correctly reverse an IN subquery, IS NOT NULL needs to be added. Interestingly, the query planner believes that using EXISTS would be more efficient than NOT IN, and IN only slightly less efficient; I assume the query planner is not able to accurately estimate the number of rows returned by the subquery. EXISTS 351'511 NOT IN 376'577 IN 386'780 LEFT JOIN 18'263'826 NOT EXISTS 7'241'815'330