Re: Performance penalty when using WITH

On Tue, Aug 2, 2011 at 11:48 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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.

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/