Is there a documented problem with optimizing integer key fields
in 7.3.3?
I have two tables. One was extracted from the other and has
the following four columns.
mtranmemid varchar(8),
mtranseq integer,
mtransts char,
mtranmemtp varchar(2)
mtranseq is a unique index on both tables and contains no nulls.
When I try to do an update, it takes several hours. Here's what
explain says about the query:
explain update missing_ids
set mtransts = a.mtransts,
mtranmemtp = a.mtranmemtp
from memtran as a
where a.mtranmemid = missing_ids.mtranmemid
and a.mtranseq = missing_ids.mtranseq
QUERY PLAN
--------------------------------------------------------------------------------
Hash Join (cost=9231.64..58634.93 rows=1 width=48)
Hash Cond: ("outer".mtranseq = "inner".mtranseq)
Join Filter: ("outer".mtranmemid = "inner".mtranmemid)
-> Seq Scan on memtran a (cost=0.00..22282.57 rows=714157 width=26)
-> Hash (cost=6289.91..6289.91 rows=351891 width=22)
-> Seq Scan on missing_ids (cost=0.00..6289.91 rows=351891 width=22)
Incidentally, why can't you define an alias on the primary table in
an update query? That would make the above a bit easier to write.
--
Mike Nolan
