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
FROM (
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
WHERE
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
CTE
s to get the result for your inner query (SELECT u.id, b.crate_id,...
) and then perform theUPDATE
from a main query?WITH upd AS (SELECT u.id, b.crate_id,...) UPDATE unit...
– Vérace Aug 5 '16 at 10:29select
orupdate
itself? – Abelisto Aug 5 '16 at 11:15TRIGGER
s? – Vérace Aug 5 '16 at 14:30src
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 '16 at 17:26