On Tue, Aug 2, 2011 at 11:48 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On Sat, Jul 30, 2011 at 8:10 AM, Robert Klemme > <shortcutter@xxxxxxxxxxxxxx> wrote: >> On Thu, Jul 28, 2011 at 11:00 PM, Li Jin <ljin@xxxxxxxxxxxxxxx> 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/ -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
