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