Re: [HACKERS] [6.5.2] join problems ...

> With respect to subqueries and PostgreSQL, as you
> know, the IN clause requires a nested scan. If you
> are going to use subqueries, correlated subqueries
> using EXISTS clauses can use indexes:
>
> SELECT c.id, c.name, c.url
> FROM aecCategory c
> WHERE EXISTS (
> SELECT a.status
> FROM aecEntMain a, aecWebEntry b
> WHERE a.status LIKE 'active:ALL%'
> AND a.representation LIKE '%:ALL%'
> AND b.status LIKE 'active:ALL%'
> AND b.indid='$indid'
> AND b.divid='$divid'
> AND (a.id,a.mid = b.id,b.mid)
> AND (b.catid,b.indid,b.divid = c.id,c.ppid,c.pid));
>
> Unfortunately, the lack of index support in IN
> subqueries affects more than just the IN subquery
> clause, since INTERSECT/EXCEPT uses the rewriter to
> rewrite such queries as UNIONS of two queries with
> an IN/NOT IN subquery, respectively. This makes the
> INTERSECT/EXCEPT feature functionally useless except
> on very small tables.

Yes, we are aware of that IN limitation, and I keep trying to get it
fixed.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026