UPDATE fails on large table - Mailing list pgsql-bugs

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        :    Kostis Pangalos
Your email address    :    pgsql@pobox.gr


System Configuration
---------------------
  Architecture (example: Intel Pentium)      :    Dual AMD Athlon

  Operating System (example: Linux 2.0.26 ELF)     :    SuSE 7.2 (upgraded kernel 2.4.12 SMP)

  PostgreSQL version (example: PostgreSQL-7.1.3):   PostgreSQL-7.1.3

  Compiler used (example:  gcc 2.95.2)        :   2.95.3


Please enter a FULL description of your problem:
------------------------------------------------
A simple UPDATE on a largish table after taking a couple of hours fails to complete.
update order set customer_id = customer.id where customer.name = order.customer_name;

In general, the simplest UPDATEs even without WHERE clauses on large tables take unbelievably long to complete
This is a Dual Athlon machine with 512Mb DDR RAM, SCSI160 and a 4.9ms SCSI Drive for the DB alone!
I find that sometimes the only way to do UPDATEs on large tables is to 'COPY' the table out to a dump file process it
with Perl and dump it in again. :-(

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:

Please  note the table names and column names have been changed for the sake of confidentiality but the statistics are
fromthe real tables which basically have a couple more columns each which I do not list since the where being not
UPDATEdnor included in the WHERE clause. 
----------------------------------------------------------------------
shop=# \d order
                                       Table "order"
  Attribute  |           Type           |                        Modifier
-------------+--------------------------+--------------------------------------------------------
id          | integer                  | not null default nextval('"order_id_seq"'::text)
customer_name        | character varying(50)    |
customer_id     | integer                  |
product_id | integer | not null
quantity | integer | not null
stamp       | timestamp with time zone | not null default timeofday()
Indices: order_customer_name_in,
            order_customer_id_key,
            order_pkey,

shop=# \d customer
                                    Table "host"
 Attribute |         Type          |                    Modifier
-----------+-----------------------+-------------------------------------------------
id        | integer               | not null default nextval('"customer_id_seq"'::text)
name      | character varying(50) |
email     | character varying(50) |
Indices: customer_id_key,
           customer_name_in


shop=# select count(*) from order;
 count
--------
 724104
(1 row)

shop=# select count(*) from customer;
 count
-------
 30074
(1 row)



shop=# explain update order set customer_id = customer.id where customer.name = order.customer_name;
NOTICE:  QUERY PLAN:

Hash Join  (cost=767.92..89821.05 rows=724104 width=140)
  ->  Seq Scan on order  (cost=0.00..33402.04 rows=724104 width=124)
  ->  Hash  (cost=545.74..545.74 rows=30074 width=16)
        ->  Seq Scan on customer  (cost=0.00..545.74 rows=30074 width=16)


shop=# update order set customer_id = customer.id where customer.name = order.customer_name;
(....a long time goes by.... then:)
ERROR:  Deadlock detected.
        See the lock(l) manual page for a possible cause.


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Sorry. Not a clue. I am in trouble too. I upgraded to 7.1.3 'cause I really needed TOAST and now it's too late to go
back.
HEEEEEEELP!