On Wed, Aug 3, 2011 at 2:18 AM, Robert Klemme <shortcutter@xxxxxxxxxxxxxx> wrote: >>> 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. Yes, but your assuming that it is safe and generally advantageous to do that. Both assumptions I think are false. The || operator is trivially hacked: create or replace function funky_concat(l text, r text) returns text as $$ select textcat(textcat($1, 'abc'), $2); $$ language sql immutable ; update pg_operator set oprcode = 'funky_concat' where oid = 654; postgres=# select 'a' || 'b'; ?column? ---------- aabcb (1 row) Also even ignoring the above it's not free to have the database try and analyze every instance of the || operator to see if it can be decomposed to boolean field operations. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
