On Thu, Jul 28, 2011 at 11:00 PM, Li Jin <ljin@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.
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/