> 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@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026