Re: Performance penalty when using WITH

On Tue, Aug 2, 2011 at 11:48 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Sat, Jul 30, 2011 at 8:10 AM, Robert Klemme

<shortcutter@googlemail.com> wrote:

On Thu, Jul 28, 2011 at 11:00 PM, Li Jin <ljin@tripadvisor.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.

disagree. just one of the ways that could be stymied would to change

the function behind the '||' operator.

I don't understand what you mean.  Can you please elaborate?

To explain my point a bit: I meant that by querying individual fields
separately instead of applying a criterion on a function of the two
the RDBMS has a better chance to use indexes and come up with a better
plan for this part of the query.

Kind regards

robert

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