I'm using PostgreSQL to host some data that comes from external source, the normal operation of the database is read-only with periodic updates. Updates though are kinda huge e.g. in a 50 million row table 10 million rows will be updated and 1 million inserted. Inserting via COPY is fast enough but updating takes a very long time. Since the database can be easily recreated what could be done to increase update performance (the query is basically UPDATE items SET name = items_import.name FROM items_import WHERE items.id = items_import.id) by reducing reliability?

Query plan, nothing unusual here (both tables have primary key)

                                           QUERY PLAN
 Update on items_import_full i  (cost=487140.65..5258198.17 rows=8124429 width=210)
   ->  Hash Join  (cost=487140.65..5258198.17 rows=8124429 width=210)
         Hash Cond: (i.id = ii.id)
         ->  Seq Scan on items_import_full i  (cost=0.00..1753813.88 rows=45392988 width=178)
         ->  Hash  (cost=322112.29..322112.29 rows=8124429 width=36)
               ->  Seq Scan on items_import_inc ii  (cost=0.00..322112.29 rows=8124429 width=36)
  • 2
    Use an unlogged table and turn off fsync. You might also want to show us the execution plan of the update statement (wiki.postgresql.org/wiki/Slow_Query_Questions) – a_horse_with_no_name Feb 18 '15 at 13:10
  • One technique often used when deleteing a lot of rows is to create a new table consisting of just the remaining rows and then "swapping" this table with the original. I wonder if the same technique used here (creating a new table and then "swapping", the result of which is the same as applying the update to the original table) would be faster too? – Colin 't Hart Feb 18 '15 at 13:14
  • @Colin'tHart I use this approach to perform "full import" (create new tables, bulk insert via COPY, rename, drop old) but I don't see how it could speed up incremental imports. – synapse Feb 18 '15 at 13:21
  • 2
    A "create table as select" where the result of the select would be the same as applying the update to the table. And definitely use unlogged tables etc like @a-horse-with-no-name suggests. – Colin 't Hart Feb 18 '15 at 13:31
  • @Colin'tHart turned out that CREATE AS SELECT is way faster than upserting WITH upd AS (UPDATE ...) INSERT ... LEFT JOIN upd. Thanks. – synapse Feb 18 '15 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.