Date: Sun, 16 Nov 2008 15:50:28 +0100
To: firerox@centrum.cz, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
Subject: Re: slow full table update
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <200811101728.3048@centrum.cz> <200811101729.14977@centrum.cz>
	<200811101730.15902@centrum.cz>
	<dcc563d10811121255h7d788032v4a8aa24c9db523b8@mail.gmail.com>
	<200811122347.21602@centrum.cz>
Content-Transfer-Encoding: 7bit
Message-ID: <op.ukpviefmcigqcu@soyouz>
In-Reply-To: <200811122347.21602@centrum.cz>
User-Agent: Opera Mail/9.62 (Linux)


> update songs set views = 0;
> UPDATE 54909
> Time: 101907.837 ms
> time is actually less than 10 minutes, but it is still very long :(

	Wow.

test=> CREATE TABLE test (id SERIAL PRIMARY KEY, value INTEGER);
test=> INSERT INTO test (value) SELECT n FROM generate_series( 1,100000 )  
AS n;
Temps : 1706,495 ms
test=> UPDATE test SET value=0;
Temps : 1972,420 ms

	Note this is 8.3.3 on a desktop PC with the database and xlog on a Linux  
Software RAID1 of rather slow drives (about 50 MB/s).
	Anyway your 10 minutes are really wrong.

	First thing to check is if there is a problem with your IO subsystem, try  
the example queries above, you should get timings in the same ballpark. If  
you get 10x slower than that, you have a problem.

	Are the rows large ? I would believe so, because a "songs" table will  
probably contain things like artist, title, comments, and lots of other  
information in strings that are too small to be TOAST'ed. Perhaps your  
problem is in index updates, too.

	So, make a copy of the songs table, without any indices, and no foreign  
keys :

	CREATE TABLE songs2 AS SELECT * FROM songs;

	Then try your UPDATE on this. How slow is it ?

	Now drop this table, and recreate it with the foreign keys. Test the  
update again.
	Now drop this table, and recreate it with the foreign keys and indexes.  
Test the update again.

	This will give you some meaningful information.

	You will probably update the 'views' column quite often, it will even  
probably be the most often updated column in your application. In this  
case, you could try moving it to a separate table with just (song_id,  
view), that way you will update a very small table.