Re: [HACKERS] Subqueries and indexes

> Bruce Momjian wrote:
> > 
> > > >
> > > > Seems the optimizer could either hash the subquery, or us an index.
> > > > Certainly would be faster than a sequental scan, no?
> > >
> > > Optimizer should hash the subquery, but I didn't implement this -:(
> > > Try to rewrite query using NOT EXISTS and index will be used.
> > 
> > How hard would it be to implement it?  I know you are deep into MVCC,
> > but doing a nested loop to join a subquery is really bad.
> 
> Not very hard, for un-correlated subqueries at least.
> I have no time to do this for 6.5...


Is it possible before 6.5 final?


> > All queries can't be rewritten as EXISTS.
> 
> All except of subqueries with aggregates in target list.

I am confused.  How do I rewrite this to use exists?
        SELECT keyname        FROM markmain        WHERE mark_id NOT IN(SELECT mark_id                             FROM
markaty)


Even if I use IN instead of NOT IN, I don't see how to do it without
making it a correlated subquery.
        SELECT keyname        FROM markmain        WHERE EXISTS (SELECT mark_id                      FROM markaty
   WHERE markmain.mark_id = markaty.mark_id)
 

This is a correlated subquery.  It did not use hash, but it did use the
index on markaty:
Seq Scan on markmain  (cost=16.02 size=334 width=12)  SubPlan    ->  Index Scan using i_markaty on markaty  (cost=2.10
size=3width=4)
 

While the index usage is good, the fact is the subquery is executed for
every row of markmain, isn't it?  That's one query executed for each row
in markmain, isn't it?

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