Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 9529F64FD63
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 29 Dec 2008 09:30:19 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 53617-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 29 Dec 2008 09:30:16 -0400 (AST)
Received: from mx3.hub.org (mx3.hub.org [206.223.169.73])
	by mail.postgresql.org (Postfix) with ESMTP id 9726E64FD49
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Dec 2008 09:30:16 -0400 (AST)
X-Greylist: delayed 00:17:47.070902 by SQLgrey-1.7.6
Received: from viefep18-int.chello.at (viefep15-int.chello.at [62.179.121.35])
	by mx3.hub.org (Postfix) with ESMTP id D53A737B7D5
	for <pgsql-performance@postgresql.org>;
	Mon, 29 Dec 2008 09:30:15 -0400 (AST)
Received: from edge05.upc.biz ([192.168.13.212]) by viefep12-int.chello.at
	(InterMail vM.7.09.01.00 201-2219-108-20080618) with ESMTP
	id <20081229131152.IOEK1315.viefep12-int.chello.at@edge05.upc.biz>;
	Mon, 29 Dec 2008 14:11:52 +0100
Received: from [192.168.1.104] ([89.134.230.11]) by edge05.upc.biz with edge
	id x1Bo1a07U0FQLHV051BqYV; Mon, 29 Dec 2008 14:11:52 +0100
X-SourceIP: 89.134.230.11
Message-ID: <4958CC94.6020103@shopzeus.com>
Date: Mon, 29 Dec 2008 14:11:48 +0100
From: Laszlo Nagy <gandalf@shopzeus.com>
User-Agent: Thunderbird 2.0.0.18 (X11/20081125)
MIME-Version: 1.0
To: Gregory Williamson <Gregory.Williamson@digitalglobe.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org,
	Daniel Fekete <danieleff@gmail.com>
Subject: Re: Slow table update - SOLVED!
References: <494F749A.5090603@shopzeus.com> <494F7B48.6070204@shopzeus.com>
	<8B319E5A30FF4A48BE7EEAAF609DB233021F389A@COMAIL01.digitalglobe.com>
	<494F8DFC.1000001@shopzeus.com> <7032.1229952622@sss.pgh.pa.us>
	<49589BCE.5080201@shopzeus.com>
	<8B319E5A30FF4A48BE7EEAAF609DB233021F38C2@COMAIL01.digitalglobe.com>
In-Reply-To: 
 <8B319E5A30FF4A48BE7EEAAF609DB233021F38C2@COMAIL01.digitalglobe.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.125 tagged_above=0 required=5
	tests=DNS_FROM_RFC_BOGUSMX=2.125
X-Spam-Level: **
X-Archive-Number: 200812/223
X-Sequence-Number: 32259


>
> Inf 8.3 the HOT feature may help if the columns being updated are 
> indexed ... what version of PostgreSQL is this again ? (Forgive my 
> lack of memory -- the last few days I've forgotten a lot, heh heh.)
>
8.3.5.

The colum that was being updated is part of one small index only.

>
> Any chances to reduce those to a bare minimum, perhaps using 
> conditional index strategies or even some form of replication, so the 
> primary uses indexes related to the updates and the mirror uses 
> indexes related to the read-only / reporting needs ? Perhaps some form 
> of staging table with no indexes to load, check data, etc. and then 
> insert.
>
> Any way to reduce those ? Check the usage via the system stats on 
> table/index use and try removing some and testing to see what makes a 
> difference.
>
We tried to remove all indexes on a test system and the update was 
speedy. We are going to try to reduce the row size also move static 
description/name/textual data into a separate table, and leave 
frequently updated data in the original one. We tested this theoretical 
version:

Query returned successfully: 182752 rows affected, 56885 ms execution time.

This is much faster. However, this table is used by hundreds of 
programs. Anyway, I got the answer to my question.

Thank you!

   Laszlo