Known issue, see FAQ item 4.23. Short form, use exists rather
than in.
On Tue, 8 May 2001, [KOI8-R] wrote:
> Leonid (leo(at)rusmedia(dot)net) reports a bug with a severity of 2(?)
>
> Short Description
> Terrible perfomance during nested "... where x in (select ...)" operator
>
> Long Description
> PostgreSQL 7.1, FreeBSD 4.2-STABLE
>
> Analyzing the reasons for terrible perfomance of my query I've found
> a very strange thing. Here is sublimation.
>
> Queries like
> select distinct i from t1 where i in
> (select distinct i from t2 where j in
> (select distinct j from t3));
> is much-much slower than
> select distinct t1.i from t1,t2,t3 where t1.i=t2.i and t2.j=t3.j;