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