X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 151439FB392 for ; Fri, 22 Sep 2006 02:49:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 70048-08 for ; Fri, 22 Sep 2006 05:49:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 16C589FB2EB for ; Fri, 22 Sep 2006 02:49:31 -0300 (ADT) Received: from [192.168.42.113] (natpool.bovine.net [67.100.216.14]) (using TLSv1 with cipher RC4-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id A567556408; Fri, 22 Sep 2006 00:49:30 -0500 (CDT) In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <256DACC7-6168-4615-BA7A-354BE59331DF@nasby.net> Cc: Content-Transfer-Encoding: 7bit From: Jim Nasby Subject: Re: Possible bug in planner (or planner not enough wise in some cases) Date: Thu, 21 Sep 2006 22:45:07 -0400 To: Boguk Maxim X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.478 tagged_above=0 required=5 tests=DATE_IN_PAST_03_06 X-Spam-Level: X-Archive-Number: 200609/236 X-Sequence-Number: 23150 On Sep 20, 2006, at 5:32 AM, Boguk Maxim wrote: > Hm i have no idea how to rewrite 'OR' conditions with subqueries as > joins > (exept using 'UNION' as writen end of my message) > Are you sure it is possible? > > WHERE > (parent_id IN (SELECT tt.id FROM el_comment as tt WHERE > tt.user_id=112 AND tt.status=1)) > OR > (text_id IN (SELECT el_text.id FROM el_text WHERE > el_text.user_id=112)) >>> somedb=# EXPLAIN ANALYZE SELECT count(*) FROM el_comment >> WHERE ((parent_id IN (SELECT tt.id FROM el_comment as tt >> WHERE tt.user_id = 112 AND tt.status=1)) OR (text_id IN >> (SELECT el_text.id FROM el_text WHERE el_text.user_id = >> 112))) AND status=1; ... FROM el_comment c, el_comment tt, el_text t WHERE ( ( c.parent_id = tt.id AND tt.user_id = 112 AND tt.status = 1 ) OR ( c.text_id = t.id AND t.user_id = 112 ) ) AND c.status=1; -- Jim Nasby jimn@enterprisedb.com EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)