Re: [HACKERS] Subqueries and indexes

Bruce Momjian wrote:
>
> In this QUERY:
>
> SELECT keyname
> FROM markmain
> WHERE mark_id NOT IN(SELECT mark_id
> FROM markaty)
>
> I have an index on markaty.mark_id, and have vacuum analyzed. EXPLAIN
> shows:
>
> Seq Scan on markmain (cost=2051.43 size=45225 width=12)
> SubPlan
> -> Seq Scan on markaty (cost=2017.41 size=52558 width=4)
>
> Vadim, why isn't this using the index? Each table has 50k rows. Is it
> NOT IN that is causing the problem? IN produces the same plan, though.
....
>
> 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.

Vadim