Re: Performance penalty when using WITH

On Wed, Aug 3, 2011 at 2:18 AM, Robert Klemme
<shortcutter(at)googlemail(dot)com> 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