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 <
mmoncure@gmail.com> wrote:
On Sat, Jul 30, 2011 at 8:10 AM, Robert Klemme
<shortcutter@googlemail.com> wrote:
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.
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/