1

I have a table with about 80 million records. I have added a new attribute (heigth_category) which I want to fill based on another attribute (heigth).
If the heigth is between 0 and 6, I want to fill the heigth_category attribute with 1.

UPDATE schema.table t1 SET heigth_category = '1'
WHERE heigth >= 0 
AND heigth< 6;

This took about 13 hours. Is that normal for such a query?

The explain gives this:

Update on bomen t1  (cost=13353.34..1932816.30 rows=579346 width=316)
  ->  Bitmap Heap Scan on bomen t1  (cost=13353.34..1932816.30 rows=579346 width=316)
        Recheck Cond: ((boom_hoogt >= 0::numeric) AND (boom_hoogt < 6::numeric))
        ->  Bitmap Index Scan on boom_hoogt_idx  (cost=0.00..13208.50 rows=579346 width=0)
              Index Cond: ((boom_hoogt >= 0::numeric) AND (boom_hoogt < 6::numeric))

I working with PostgreSQL 9.1.12

  • Do you actually need that additional column? Everything in the question indicates it is 100% redundant and should not be added to begin with ..? Consider a "generated column" or a view instead. – Erwin Brandstetter Feb 27 '14 at 23:41
  • The attribute is later used for joins, sums and average. So that is why I thought I add a new column. Would a view be fast? – Stefan Mar 3 '14 at 7:42
  • 1
    It depends, but yes - even slightly faster in most cases, since the size of your table stays smaller and the most important factor for performance is how many pages have to read to satisfy queries. – Erwin Brandstetter Mar 3 '14 at 10:01
4

In general mass-updates like this can take a long time, independent of the RDBMS used.

Often a lot of time can be saved by running the update in batches of a few thousand rows at a time. Something like:

UPDATE schema.table set ...
 WHERE id >=0 and id < 10000

UPDATE schema.table set ...
 WHERE id >=10000 and id < 20000

...

Instead of manually typing the different update statements you certainly can use a loop too. Just make sure that there is no overarching transaction, as that would negate the effort.

  • To clarify for the general public: looping in a server-side procedure is automatically wrapped in a single transaction, which would render the operation void. The looping Sebastian refers to would have to be done in a client, with separate transactions. Or one could use dblink, like instructed in this answer on SO. – Erwin Brandstetter Mar 8 '14 at 1:10

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.