Re: [SQL] qurey plan and indices

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