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 <mmonc...@gmail.com> wrote: >> On Sat, Jul 30, 2011 at 8:10 AM, Robert Klemme >> <shortcut...@googlemail.com> wrote: >>> On Thu, Jul 28, 2011 at 11:00 PM, Li Jin <l...@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/
