Subject: Problems with an update-from statement and  pg-8.1.4
From: Rafael Martinez <r.m.guerrero@usit.uio.no>
To: pgsql-performance@postgresql.org
Content-Type: text/plain
Organization: University of Oslo
Date: Wed, 06 Dec 2006 20:10:43 +0100
Message-Id: <1165432244.9606.18.camel@bbking.linux>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Hello

We are having some problems with an UPDATE ... FROM sql-statement and
pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the
table 'mail', this table is over 6GB without indexes, and when we send
thousands of this type of statement, the server has a very high iowait
percent.

How can we get rid of this Seq Scan?

I send the output of an explain and table definitions:
-------------------------------------------------------------------------

mailstats=# EXPLAIN update mail SET spamscore = '-5.026'  FROM mail m,
mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id =
'1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47';
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..932360.78 rows=7184312 width=57)
   ->  Nested Loop  (cost=0.00..6.54 rows=1 width=0)
         ->  Index Scan using received_queue_id_index on mail_received
mr  (cost=0.00..3.20 rows=1 width=4)
               Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
               Filter: (mailhost = '129.240.10.47'::inet)
         ->  Index Scan using mail_pkey on mail m  (cost=0.00..3.32
rows=1 width=4)
               Index Cond: ("outer".mail_id = m.mail_id)
   ->  Seq Scan on mail  (cost=0.00..860511.12 rows=7184312 width=57)
(8 rows)

mailstats=# \d mail
                                Table "public.mail"
   Column   |     Type     |                       Modifiers
------------+--------------+--------------------------------------------------------
 mail_id    | integer      | not null default
nextval('mail_mail_id_seq'::regclass)
 size       | integer      |
 message_id | text         | not null
 spamscore  | numeric(6,3) |
Indexes:
    "mail_pkey" PRIMARY KEY, btree (mail_id)
    "mail_message_id_key" UNIQUE, btree (message_id)

mailstats=# \d mail_received
                                            Table "public.mail_received"
    Column     |            Type             |
Modifiers
---------------+-----------------------------+----------------------------------------------------------------------
 reception_id  | integer                     | not null default
nextval('mail_received_reception_id_seq'::regclass)
 mail_id       | integer                     | not null
 envelope_from | text                        |
 helohost      | text                        |
 from_host     | inet                        |
 protocol      | text                        |
 mailhost      | inet                        |
 received      | timestamp without time zone | not null
 completed     | timestamp without time zone |
 queue_id      | character varying(16)       | not null
Indexes:
    "mail_received_pkey" PRIMARY KEY, btree (reception_id)
    "mail_received_queue_id_key" UNIQUE, btree (queue_id, mailhost)
    "mail_received_completed_idx" btree (completed)
    "mail_received_mailhost_index" btree (mailhost)
    "mail_received_received_index" btree (received)
    "received_id_index" btree (mail_id)
    "received_queue_id_index" btree (queue_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (mail_id) REFERENCES mail(mail_id)
-------------------------------------------------------------------------

Thanks in advance.
regards,
-- 
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/