From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Steve Heaven <steve(at)thornet(dot)co(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query plan and sub-queries |
Date: | 2000-08-08 14:17:23 |
Message-ID: | 39901673.DCED032E@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Don Baccus | 2000-08-08 14:21:08 | Re: Re: [GENERAL] Trouble with float4 afterupgrading from 6.5.3 to 7.0.2 |
Previous Message | The Hermit Hacker | 2000-08-08 14:14:07 | Re: Release date for 7.1? |
From | Date | Subject | |
---|---|---|---|
Next Message | Don Baccus | 2000-08-08 14:21:08 | Re: Re: [GENERAL] Trouble with float4 afterupgrading from 6.5.3 to 7.0.2 |
Previous Message | Tom Lane | 2000-08-08 14:04:44 | Re: [HACKERS] Re: Trouble with float4 afterupgrading from 6.5.3 to 7.0.2 |