> 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.
