Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 899BD1337B5B for ; Tue, 2 Nov 2010 19:30:17 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 25372-03 for ; Tue, 2 Nov 2010 22:30:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 17E821337B2F for ; Tue, 2 Nov 2010 19:30:09 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oA2MU9aY017302; Tue, 2 Nov 2010 18:30:09 -0400 (EDT) To: Yaocl cc: pgsql-performance@postgresql.org Subject: Re: A query become very slow after upgrade from 8.1.10 to 8.4.5 In-reply-to: References: Comments: In-reply-to Yaocl message dated "Tue, 02 Nov 2010 10:50:22 +0800" Date: Tue, 02 Nov 2010 18:30:08 -0400 Message-ID: <17301.1288737008@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/21 X-Sequence-Number: 41045 Yaocl writes: > SELECT t_a.id FROM t_a > WHERE EXISTS ( SELECT t_b.id FROM t_b, t_c > WHERE t_b.id = t_a.id AND t_c.flag = 'f') I have some hopes for fixing this in 9.1, but nothing is going to happen in 8.4 or 9.0. In the meantime, is it intentional that there is no join clause between t_b and t_c? That'd be a lot more efficient as two separate EXISTS tests, ie WHERE EXISTS ( SELECT 1 FROM t_b WHERE t_b.id = t_a.id ) AND EXISTS ( SELECT 1 FROM t_c WHERE t_c.flag = 'f') but I wonder whether this query doesn't simply reflect a logic error on the client side. regards, tom lane