Re: Query plan and sub-queries

Steve Heaven wrote:
>
> At 08:24 08/08/00 -0400, you wrote:
> > A workaround is to replace IN with EXISTS:
>
> This still does a sequential rather that indexed scan:
>
> explain select * from books_fti where exists
>       (select R1684.stockno from R1684,books_fti where
> R1684.stockno=books_fti.stockno );

Firstly, a simple join would yield the same results:

SELECT books_fti.* FROM books_fti, R1684 WHERE
books_fti.stockno = R1684.stockno;

Secondly, you've listed the target table twice in the above
query, which might be causing a problem with the planner.
Instead, it should read:

SELECT * FROM books_fti WHERE EXISTS (
 SELECT R1684.stockno FROM R1684 WHERE R1684.stockno =
books_fti.stockno
);

That should result in 1 sequential scan on one of the tables, and
1 index scan on the inner table. The plan should look something
like:

Seq Scan on R1684  (cost=9.44 rows=165 width=12)
  SubPlan
    ->  Index Scan using allbooks_isbn on books_fti  (cost=490.59
rows=7552 width=12)


Hope that helps,

Mike Mascari