Thread: Re: [HACKERS] Subqueries and indexes

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

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