Re: Query plan and sub-queries

At 10:17 08/08/00 -0400, Mike Mascari wrote:
>
>Firstly, a simple join would yield the same results:
>
>SELECT books_fti.* FROM books_fti, R1684 WHERE
>books_fti.stockno = R1684.stockno;

Yes that gives me:
Nested Loop (cost=2093.00 rows=1024706 width=172)
-> Seq Scan on r1689 (cost=43.00 rows=1000 width=12)
-> Index Scan using allbooks_isbn on books_fti (cost=2.05 rows=1024705
width
=160)

But the 'EXISTS' sub-query you suggest still doesnt use the index.

>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)
>

No actually I'm getting:
Seq Scan on books_fti (cost=79300.27 rows=1024705 width=160)
SubPlan
-> Seq Scan on r1684 (cost=43.00 rows=2 width=12)
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk