X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 049A4329E12 for ; Fri, 10 Sep 2004 04:07:20 +0100 (BST) 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 86915-09 for ; Fri, 10 Sep 2004 03:07:16 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D79EA329D15 for ; Fri, 10 Sep 2004 04:07:14 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i8A37BT3013335; Thu, 9 Sep 2004 23:07:11 -0400 (EDT) To: Neil Conway Cc: Markus Schaber , PostgreSQL Performance List Subject: Re: Multiple Uniques In-reply-to: <4140E1EF.1040303@samurai.com> References: <20040902154340.5b1a669a@kingfisher.intern.logi-track.com> <19516.1094134957@sss.pgh.pa.us> <4140E1EF.1040303@samurai.com> Comments: In-reply-to Neil Conway message dated "Fri, 10 Sep 2004 09:06:23 +1000" Date: Thu, 09 Sep 2004 23:07:11 -0400 Message-ID: <13334.1094785631@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/58 X-Sequence-Number: 8186 Neil Conway writes: > Tom Lane wrote: >> Because the issue doesn't come up often enough to justify expending >> cycles to check for it. > How many cycles are we really talking about, though? I have a patch > which I'll send along in a few days which implements a similar > optimization: if a subselect is referenced by EXISTS or IN, we can > discard DISTINCT and ORDER BY clauses in the subquery I don't think either of those is worth doing. ORDER BY in a sub-select isn't even legal SQL, much less probable, so why should we expend even a nanosecond to optimize it? The DISTINCT is more of a judgment call, but my thought when I looked at it originally is that it would give people a possible optimization knob. If you write DISTINCT in an IN clause then you can get a different plan (the IN reduces to an ordinary join) that might or might not be better than without it. We shouldn't take away that possibility just on the grounds of nanny-ism. regards, tom lane