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