Re: [SQL] qurey plan and indices

Patrick Giagnocavo <a222(at)redrose(dot)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