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. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
