Re: Performance penalty when using WITH

Robert，

I've built an index on this expression firstname || ' ' || substring(lastname,1,1). I believe this is the best index for this particular query. Correct me if I am wrong.

Li

On Aug 3, 2011, at 3:18 AM, Robert Klemme wrote:

> 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/