Stephan Szabo wrote:
On Wed, 6 Dec 2006, Rafael Martinez wrote:
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';
I don't think this statement does what you expect. You're ending up with
two copies of mail in the above one as "mail" and one as "m". You probably
want to remove the mail m in FROM and use mail rather than m in the
where clause.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Worse yet I think your setting "spamcore" for EVERY row in mail to
'-5.026'. The above solution should fix it though.
-- Ted
*
* <http://www.blackducksoftware.com>
