Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UhMOC-0007lb-2P for pgsql-performance@arkaria.postgresql.org; Tue, 28 May 2013 16:04:44 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UhMOB-0007yh-ID for pgsql-performance@arkaria.postgresql.org; Tue, 28 May 2013 16:04:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UhMOA-0007yc-QX for pgsql-performance@postgresql.org; Tue, 28 May 2013 16:04:42 +0000 Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UhMNy-00079H-R3 for pgsql-performance@postgresql.org; Tue, 28 May 2013 16:04:42 +0000 Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 4B973E00EC84 for ; Tue, 28 May 2013 09:04:30 -0700 (PDT) Message-ID: <51A4D596.1010607@pinpointresearch.com> Date: Tue, 28 May 2013 09:04:38 -0700 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130510 Thunderbird/17.0.6 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Performance of complicated query References: <519E563A.4010401@pinpointresearch.com> <519E75AC.80103@pinpointresearch.com> <519EA49B.7030404@mansionfamily.plus.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -3.0 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 05/23/2013 05:21 PM, Jonathan Morra wrote: > Sorry for the messy query, I'm very new to writing these complex > queries. I'll try and make it easier to read by using WITH clauses. > However, just to clarify, the WITH clauses only increase readability > and not performance in any way, right? It depends. The planner is a tricky beast and sometimes rewriting a seeming identical query will result in a much more (or less) efficient plan. A classic case was the difference between ....where foo in (select bar from...)... vs. where exists (select 1 from bar where...).... In an ideal world the planner would figure out that both are the same and optimize accordingly but there was a point where one was typically more efficient then it switched to the other being better for the planner. I don't recall the current state. Casting can be important - sometimes the planner needs a "nudge" to use an index on, say, a varchar column being compared to, perhaps, a text value or column in which case casting to the exact data-type being indexed can be a big win. Cheers, Steve -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance