Re: Performance penalty when using WITH

On Sat, Jul 30, 2011 at 8:10 AM, Robert Klemme
<shortcutter(at)googlemail(dot)com> wrote:
> 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.

disagree. just one of the ways that could be stymied would to change
the function behind the '||' operator.

merlin