Re: Performance penalty when using WITH

On Thu, Jul 28, 2011 at 11:00 PM, Li Jin <ljin(at)tripadvisor(dot)com> wrote:
> I met with the problem that when I was using WITH clause to reuse a
> subquery, I got a huge performance penalty because of query planner.
> Here are the details, the original query is
> EXPLAIN ANALYZE WITH latest_identities AS
> (
>     SELECT DISTINCT ON (memberid) memberid, username, changedate
>     FROM t_username_history
>     WHERE memberid IN (SELECT memberid FROM t_member WHERE firstname || ' '
> || substring(lastname,1,1) = 'Eddie T')
>     ORDER BY memberid, changedate DESC
> )

Another observation: That criterion looks suspicious to me. I would
expect any RDBMS to be better able to optimize this:

WHERE firstname = 'Eddie' AND lastname like 'T%'

I know it's semantically not the same but I would assume this is good
enough for the common usecase. Plus, if there is an index on
(firstname, lastname) then that could be used.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/