PostgreSQL slow sql update command on Amazon EC2

Question

I am running a PostgreSQL 9.1 server on a Amazon EC2 medium instance (with an EBS storage). I am trying to run a simple UPDATE statement on about 3 millions row. It seems to take forever.

This is the update statement:

UPDATE "ab_device" SET "last_seen" = "ab_device"."registered_at";

The table:

                                   Table "public.ab_device"
    Column     |           Type           |                       Modifiers                        
---------------+--------------------------+--------------------------------------------------------
 id            | integer                  | not null default nextval('ab_device_id_seq'::regclass)
 uuid          | character varying(255)   | not null
 registered_at | timestamp with time zone | not null
 app_id        | integer                  | not null
 last_seen     | timestamp with time zone | not null
Indexes:
    "ab_tempdevice_pkey" PRIMARY KEY, btree (id)
    "ab_device_app_id_56238bcbd52372f_uniq" UNIQUE CONSTRAINT, btree (app_id, uuid)
    "ab_device_uuid" btree (uuid)
    "ab_tempdevice_app_id" btree (app_id)
Foreign-key constraints:
    "app_id_refs_id_9cb306ef" FOREIGN KEY (app_id) REFERENCES ab_app(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "ab_trial" CONSTRAINT "device_id_refs_id_050eed1f" FOREIGN KEY (device_id) REFERENCES ab_device(id) DEFERRABLE INITIALLY DEFERRED

The "explain" of the request:

                                QUERY PLAN                                
--------------------------------------------------------------------------
 Update on ab_device  (cost=0.00..78210.65 rows=2761865 width=59)
   ->  Seq Scan on ab_device  (cost=0.00..78210.65 rows=2761865 width=59)
(2 rows)

The CPU iowait is high when I run the request. Is it normal that the request takes a lot of time? Is it because of the I/O of the machine are poor?

Thank you for your insight.

A performance of IO on Amazon is usually terrible - mainly for longer tasks. — Pavel Stehule, Nov 22 '13 at 10:19

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

I think this could be because of Postgresql's MVCC implementation. It makes this kind of single updates expensive. In these cases, each row needs to be copied as a new version, and the old version will be marked as deleted.

If you can, change some parameters before the update and do a VACUUM FULL to force table rewrite. Refer to: Slow simple update query on PostgreSQL database with 3 million rows for more info.

Hope it helps.

edited May 23 '17 at 11:56

Community

1
1

answered Nov 22 '13 at 10:09

Ricardo

7,785
8
40
60

I am not sure about the other post you refer. It's not completely clear for me about what to do. It talks about HOT but does not show how to use it. – poiuytrez Nov 22 '13 at 10:34
You should try this: http://stackoverflow.com/a/3100232/362298. The HOT update is done automatically by postgres depending on the indexes and type of update. You can check whether it was hot updated by issuing the command: `pg_stat_user_tables.n_tup_hot_upd`. Also, refer to source docs for details, if you are interested: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;hb=HEAD – Ricardo Nov 22 '13 at 10:44

PostgreSQL slow sql update command on Amazon EC2

1 Answers1