From: PFC <lists@peufeu.com>
To: firerox@centrum.cz, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: slow full table update
Date: Sun, 16 Nov 2008 15:50:28 +0100
Message-ID: <op.ukpviefmcigqcu@soyouz> (raw)
In-Reply-To: <200811122347.21602@centrum.cz>
References: <200811101728.3048@centrum.cz>
<200811101729.14977@centrum.cz>
<200811101730.15902@centrum.cz>
<dcc563d10811121255h7d788032v4a8aa24c9db523b8@mail.gmail.com>
<200811122347.21602@centrum.cz>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com
Subject: Re: slow full table update
In-Reply-To: <op.ukpviefmcigqcu@soyouz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox