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