Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7217A9FB545 for ; Wed, 18 Apr 2007 15:36:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 06652-03 for ; Wed, 18 Apr 2007 15:36:17 -0300 (ADT) X-Greylist: delayed 00:19:39.453583 by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 6F2689FB6FA for ; Wed, 18 Apr 2007 15:36:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mail.frii.com (phobos01.frii.com [216.17.128.161]) by svr4.postgresql.org (Postfix) with ESMTP id 60C515AF8A7 for ; Wed, 18 Apr 2007 18:16:45 +0000 (GMT) Received: from kyoto.home (smcgraw.ppp.frii.com [216.17.133.158]) by mail.frii.com (FRII) with ESMTP id 8A4B211B4690 for ; Wed, 18 Apr 2007 12:16:38 -0600 (MDT) Received: from maxwell (maxwell.home [10.1.65.135]) by kyoto.home (Postfix) with SMTP id D90272A65CA for ; Wed, 18 Apr 2007 12:16:37 -0600 (MDT) From: "Stuart McGraw" To: Subject: slowness when subselect uses DISTINCT Date: Wed, 18 Apr 2007 12:16:37 -0600 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="shift_jis" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.136 tagged_above=0 required=5 tests=BAYES_50=0.001, FORGED_RCVD_HELO=0.135 X-Spam-Level: X-Archive-Number: 200704/160 X-Sequence-Number: 28160 I have several times now run into what seems like similar performance problems with some of my postgresql queries. I have a view that runs reasonably quicky. I use this view in a subselect in another query and that query too runs reasonably quicky. The view returns some unwanted duplicate rows so I modify it using either DISTINCT or GROUP BY to eliminate them. View still runs reasonably quickly. I use the modified view as a subselect as above, but now the query runs 2-3 orders of magnitude more slowly than before. Before I go through the effort of putting together a specific and concise test case, has anyone seen this general pattern and have an explanation or advice? (PG-8.2.3)