Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA39001 for ; Thu, 5 Aug 1999 10:40:10 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA04177; Thu, 5 Aug 1999 10:39:26 -0400 (EDT) To: Patrick Giagnocavo cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] qurey plan and indices In-reply-to: Your message of Thu, 05 Aug 1999 10:18:55 -0400 <37A99D4F.610E6F7C@redrose.net> Date: Thu, 05 Aug 1999 10:39:26 -0400 Message-ID: <4175.933863966@sss.pgh.pa.us> From: Tom Lane Patrick Giagnocavo 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