X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B4999D8107 for ; Wed, 7 Sep 2005 14:57:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99996-10 for ; Wed, 7 Sep 2005 17:57:50 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 79FBDD80F9 for ; Wed, 7 Sep 2005 14:57:48 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j87Hvav1028615; Wed, 7 Sep 2005 13:57:36 -0400 (EDT) To: "Steinar H. Gunderson" Cc: pgsql-performance@postgresql.org Subject: Re: Query take 101 minutes, help, please In-reply-to: <20050907165754.GA12319@uio.no> References: <20050907165754.GA12319@uio.no> Comments: In-reply-to "Steinar H. Gunderson" message dated "Wed, 07 Sep 2005 18:57:54 +0200" Date: Wed, 07 Sep 2005 13:57:36 -0400 Message-ID: <28614.1126115856@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200509/113 X-Sequence-Number: 14421 "Steinar H. Gunderson" writes: > (I'm not sure how optimized UNION inside an IN/NOT IN is.) NOT IN is pretty nonoptimal, period. It'd help a lot to boost work_mem to the point where the planner figures it can use a hashtable (look for EXPLAIN to say "hashed subplan" rather than just "subplan"). Of course, if there's enough stuff in the UNION that that drives you into swapping, it's gonna be painful anyway. Using UNION ALL instead of UNION might save a few cycles too. If you're willing to rewrite the query wholesale, you could try the old trick of a LEFT JOIN where you discard rows for which there's a match, ie, the righthand join value isn't NULL. regards, tom lane