Query runs too long for indexed tables

"Andrus" <eetasoft@xxxxxxxxx> · Tue, 4 Apr 2006 22:37:18 +0300

I have relatively small tables (toode and rid) in fast server.
Both tables are indexed on toode field.

Following query takes long time to run.
toode field type is char(20). It is difficult to change this field type.

Any idea how to speed up this query ?

UPDATE firma1.rid SET toode=NULL
       WHERE toode IS NOT NULL AND
       toode NOT IN (SELECT TOODE      FROM firma1.TOODE);

Query returned successfully: 0 rows affected, 594813 ms execution time.

explain window shows:

Seq Scan on rid  (cost=2581.07..20862553.77 rows=51848 width=1207)
  Filter: ((toode IS NOT NULL) AND (NOT (subplan)))
  SubPlan
    ->  Materialize  (cost=2581.07..2944.41 rows=14734 width=84)
          ->  Seq Scan on toode  (cost=0.00..2350.34 rows=14734 width=84)

Andrus. 