Re: Performance of complicated query

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