I am looking for a faster way to do a update query which includes a window function, and don't have any solid idea what might help.

I am "denormalizing" a bit of data, which means moving the grandparent id into the child, and adding a sequence number. Prior to this we had no "grandparent" (crate) level, only "parent" (box).

The schema is quite simple, in this case:

crate[id] => 
    box[id] => 
        unit[id, sequence_in_box]
           adding [crate_id, sequence] to `unit`

The query I have runs fairly slow (to me), only updating around 10k rows a second (200k rows, ~22 seconds). Since some instances of the database have 2M rows, I'm hoping to make this update go a bit swifter.

UPDATE unit dest SET
    crate_id = src.crate_id,
    sequence = src.sequence
         SELECT u.id, b.crate_id, row_number() OVER w AS sequence
         FROM   unit u INNER JOIN box b ON u.box_id = b.id
         WINDOW w AS (
             PARTITION BY b.crate_id ORDER BY b.id, u.sequence_in_box
     ) as src
    dest.id = src.id

Sample Existing data (with desired update) looks something like this, with some field names abbreviated.

crate    box            unit            
id       id, crate      id   box  seq   crate_id, sequence 
-----    ---------      --------------  -------------------
1        1   1          1    1    1     1         1
2        2   1          2    1    2     1         2
         3   2          3    2    1     1         3
                        4    2    2     1         4
                        5    3    1     2         1
                        6    3    2     2         2

I can't use UPDATE x FROM because window functions aren't allowed in an update statement (and I'm not sure that would be faster anyway.) I am prepared for a "nope, no better way", just hoping its not true :D

  • Have you looked at CTEs to get the result for your inner query (SELECT u.id, b.crate_id,...) and then perform the UPDATE from a main query? WITH upd AS (SELECT u.id, b.crate_id,...) UPDATE unit...
    – Vérace
    Aug 5, 2016 at 10:29
  • I tested it and it came out the same, essentially. I didn't think it would be faster, and I was not let down :D
    – Andrew
    Aug 5, 2016 at 10:44
  • What part of the whole query slowing down it: select or update itself?
    – Abelisto
    Aug 5, 2016 at 11:15
  • @AndrewBacker have you considered TRIGGERs?
    – Vérace
    Aug 5, 2016 at 14:30
  • Compare the times for 1) creating a temp table with just the results of the src select statement, 2) updating the main table using UPDATE...FROM <temptable> ..., and 3) your current single-statement update. If the time of (3) is about equal to the sum of (1) and (2), then look at which is greater between (1) and (2) and you will know where your time is most likely going.
    – jjanes
    Aug 8, 2016 at 17:26


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.