search

sraoss / pg_ivm

IVM (Incremental View Maintenance) implementation as a PostgreSQL extension
Other
862 stars 24 forks source link

Batch update takes too long #34

Closed solarizeDark closed 1 year ago

solarizeDark commented 1 year ago

I have two tables named t0 and t1 each 1 million records with structure: CREATE TABLE t0 ( id integer, num numeric )

and materialized view: SELECT t0.id AS t0_id, t1.id AS t1_id, (t0.num + t1.num) AS sum FROM (t0 JOIN t1 ON ((t1.id = t0.id))) With index created on t0_id for view.

Batch update of num field in both two tables takes ~2 minutes for 100000 rows updated, ~10 minutes for 500000 rows updated and ~20 minutes for 1 million rows

What am I doing wrong? Im using postgres 15.1 running on ubuntu 20.04 on virtual box with 4 GB RAM, usually updates end up system falling down

yugo-n commented 1 year ago

In the situation where many rows are updated, pg_ivm doesn't work efficiently because overhead of calculating and applying deltas is very high. So, I recommend to disable IVM using refresh_immv('immv_name', false) before batch update, and after that, populate it again by refresh_immv('immv_name', true). If such batch update is common, unfortunately use of pg_ivm doesn't is not suitable to that workload.

solarizeDark commented 1 year ago

Thank you for response a lot