Patrick Giagnocavo <a222@redrose.net> writes:
> MESZAROS Attila wrote:
>>
>> I've experienced a brutal speedup (order of 2) separateing the following
>> subquery and making it manually:
>>
>> explain select name,description
>> from descriptions
>> where in (select name
>> from descriptions
>> where description like '%Bankverbindung%');
> Correct me if I am wrong, however if using
> LIKE '%something%'
> (which means, the field contains 'something' somewhere in the field)
> there is never a chance to use the index you have created - a
> sequential table scan MUST be made, thus you have to read all 3575
> rows to return the set.
Yes. The inner query must happen by sequential scan. But the outer
query can use an index if it is in the form... where (name = 'a') or (name = 'b') or (name = 'c');
which is what "where name in ('a','b','c')" gets translated to.
However, the *real* problem here is that the inner query is treated
as a subplan, which means it is re-evaluated for each tuple scanned
by the outer query. This is not very bright; the system ought to
notice that the inner query does not depend on the state of the
outer query, and do it only once. There is already a notion of
InitPlan vs. SubPlan. I'm not sure why this case is being classified
as a SubPlan, but it sure looks like it ought to be done as an
InitPlan...
regards, tom lane
