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