Received: from sunpine.krs.ru (SunPine.krs.ru [195.161.16.37]) by hub.org (8.9.3/8.9.3) with ESMTP id WAA47910 for ; Thu, 5 Aug 1999 22:12:32 -0400 (EDT) (envelope-from vadim@krs.ru) Received: from krs.ru (dune.krs.ru [195.161.16.38]) by sunpine.krs.ru (8.8.8/8.8.8) with ESMTP id KAA22361; Fri, 6 Aug 1999 10:09:10 +0800 (KRSS) Sender: root@sunpine.krs.ru Message-ID: <37AA43C4.5DDBE65F@krs.ru> Date: Fri, 06 Aug 1999 10:09:08 +0800 From: Vadim Mikheev Organization: OJSC Rostelecom (Krasnoyarsk) X-Mailer: Mozilla 4.5 [en] (X11; I; FreeBSD 3.0-RELEASE i386) X-Accept-Language: ru, en MIME-Version: 1.0 To: Tom Lane CC: Patrick Giagnocavo , pgsql-sql@postgreSQL.org Subject: Re: [SQL] qurey plan and indices References: <4175.933863966@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote: > > 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... Not in all cases. First, to use InitPlan you would have to add DISTINCT to subquery to avoid duplicates. Second, there is another way of optimization - cache/hash subquery results to avoid re-execution of subquery plan. Planner should make decision what way to follow. Vadim