I have this update query:

UPDATE articlestockhistory
SET articlerotationnature = cross_address_histories.rotation
FROM (
    SELECT
        articlestockhistory.id,
        articlestockhistory.delta,
        CASE
            WHEN articlestockhistory.delta > 0 THEN 'RECEPTION_ONLY_IN_SPECIFIC_STOCK_ADDRESS'
            ELSE 'CONSUMPTION_ONLY_IN_SPECIFIC_STOCK_ADDRESS' END
        AS rotation
    FROM stockmovementline
    INNER JOIN stocklocation source_location on stockmovementline.sourcephysicallocation_id = source_location.id
    INNER JOIN stockwarehouse source_warehouse ON source_location.warehouse_id = source_warehouse.id
    INNER JOIN stocklocation target_location on stockmovementline.targetphysicallocation_id = target_location.id
    INNER JOIN stockwarehouse target_warehouse ON target_location.warehouse_id = target_warehouse.id
    INNER JOIN articlestockhistory ON articlestockhistory.stockchangecauserid = stockmovementline.id
    WHERE
            source_warehouse.address_id != target_warehouse.address_id
      AND articlerotationoverriddenby_id IS NULL
) AS cross_address_histories
WHERE articlestockhistory.id = cross_address_histories.id;

This hits about 3M records of the 27M records I have in articlestockhistory.

Now, I’ve tried running this and have kept in running for 40 minutes. I see a lot of CPU and disk usage by postgres the whole time, but the query doesn’t finish.

If I dropped all indexes on the articlestockhistory table, it finished in about 4 minutes. But this is not feasible in production.

I then converted this UPDATE query to application code:

• Run the SELECT query, selecting all 3M rows to application memory
• Execute UPDATE queries using JDBC’s PreparedStatement with methods addBatch and executeBatch every 10000 records.

This finished in about 15 minutes, and I could view progress while it was running.

This is on a local database which received no other queries.

I don’t understand why I’m forced to write application code to do the same updates efficiently. Any insights?