From: Steve Crawford <scrawford@pinpointresearch.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance of complicated query
Date: Tue, 28 May 2013 09:04:38 -0700
Message-ID: <51A4D596.1010607@pinpointresearch.com> (raw)
In-Reply-To: <CAF8LAAUU3zjVK_=cQKfQUGxL9oEsHi_cJauf59WjzbWQiKh=TQ@mail.gmail.com>
References: <CAF8LAAUN7mJ3d-x2qAFvq07BUtQydBkDmRLk_mX0OCa0sFaJZw@mail.gmail.com>
<519E563A.4010401@pinpointresearch.com>
<CAF8LAAXxB=RKcrgd0ggiPhvZEaTjKj3Wk6dzCK6_e8w_Q2cvJg@mail.gmail.com>
<519E75AC.80103@pinpointresearch.com>
<CAF8LAAXnSd+2x495baxzjbZhT0_g5sSER_b_qqjCzdYj3QFn4g@mail.gmail.com>
<519EA49B.7030404@mansionfamily.plus.com>
<CAF8LAAUU3zjVK_=cQKfQUGxL9oEsHi_cJauf59WjzbWQiKh=TQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com
Subject: Re: Performance of complicated query
In-Reply-To: <51A4D596.1010607@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox