I am currently having a problem with a query never finishing (or at
least not in a reasonable amount of time.) I have had similar problems
to this in Postgres over the past several years, and I have always found
workarounds. This time I'd like to actually understand what is happening.
I have two tables, tbl_claim and tbl_insured. tbl_claim has a column
insured_id which references the primary key in tbl_insured, also named
insured_id. Originally they were to have a one to many relationship
(one tbl_insured record could potentionally be referenced by multiple
records in tbl_claim). In practice, however, they're essentially 1 to
1. There are a few "stragglers", but it isn't really necessary to keep
the integrity. I want to move all data from tbl_insured into
tbl_claim. I added all pertinent columns to tbl_insured. However, when
I run the following query:
UPDATE tbl_claim SET
ins_lname = tbl_insured.ins_lname,
ins_fname = tbl_insured.ins_fname,
ins_mi = tbl_insured.ins_mi,
ins_add1 = tbl_insured.ins_add1,
ins_add2 = tbl_insured.ins_add2,
ins_city = tbl_insured.ins_city,
ins_state = tbl_insured.ins_state,
ins_zip = tbl_insured.ins_zip,
ins_phone = tbl_insured.ins_phone,
ins_altphone =tbl_insured.ins_altphone,
ins_cell = tbl_insured.ins_cell,
ins_pager = tbl_insured.ins_pager,
ins_fax = tbl_insured.ins_fax,
ins_email = tbl_insured.ins_email
FROM tbl_insured WHERE tbl_claim.insured_id = tbl_insured.insured_id;
it never comes back. I have left it running for 30 minutes or so, but
it never comes back (It is simply not acceptable to run this query for
30 minutes, as this is part of a much larger system overhaul...I can't
have my production system down for this long)
Here are my record counts:
select count(*) FROM tbl_insured;
count
--------
242083
(1 row)
select count(*) FROM tbl_claim;
count
--------
243121
select count(*) FROM tbl_claim INNER JOIN tbl_insured ON
tbl_claim.insured_id = tbl_insured.insured_id;
count
--------
243117
Here is my explain of the update query:
Hash Join (cost=11033.04..94030.41 rows=313765 width=596)
Hash Cond: ("outer".insured_id = "inner".insured_id)
-> Seq Scan on tbl_claim (cost=0.00..16240.29 rows=445829 width=442)
-> Hash (cost=4871.83..4871.83 rows=242083 width=158)
-> Seq Scan on tbl_insured (cost=0.00..4871.83 rows=242083
width=158)
(5 rows)
When I try an explain analyze, it just spins forever as well (I've never
let it run for more than 30 minutes).
I could easily write a little PHP script to copy over the data, but I'd
like to do it in SQL, and if not at least know WHY it isn't working.
I can't quite see why this is taking so long, and I don't know how I
would go about diagnosing the problem, since the explain doesn't really
seem like it should take too long, and explain analyze is hanging as
well. Note: I did try a VACUUM FULL ANALYZE prior to running the query.
