> > 
> > 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=3 width=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?

I just tried this with NOT EXISTS, and it was VERY fast.  Can we discuss
the issues, and perhaps auto-rewrite these as exists.  Is that always
better than hash?


-- 
  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, Pennsylvania 19026

Wed, 17 Mar 1999 13:32:28 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Subqueries and indexes
Bruce Momjian wrote:
> 
> >
> > 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?
> 
> I just tried this with NOT EXISTS, and it was VERY fast.  Can we discuss
> the issues, and perhaps auto-rewrite these as exists.  Is that always
> better than hash?

Not always, but there is no hashing currently, so you could try
re-writing for IN/NOT IN subqueries without aggregates...

Keep in mind that expression subqueries must return <= 1 rows,
so it's probably better don't rewrite them (or you'll have to
add this check to EXISTS code).

Vadim

Thu, 18 Mar 1999 09:34:16 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Subqueries and indexes