In this QUERY:
SELECT keynameFROM markmainWHERE 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.
If I do a traditional join:
SELECT keyname FROM markmain , markaty WHERE markmain.mark_id = markaty.mark_id
I then get a hash join plan:Hash Join (cost=10768.51 size=90519 width=20) -> Seq Scan on markmain (cost=2051.43
size=45225width=16) -> Hash (cost=0.00 size=0 width=0) -> Seq Scan on markaty (cost=2017.41 size=52558
width=4)
Seems the optimizer could either hash the subquery, or us an index.
Certainly would be faster than a sequental scan, no?
-- 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