10

Are we supposed to run ANALYZE myview; on a PostgreSQL 9.6+ materialized view after running a REFRESH MATERIALIZED VIEW CONCURRENTLY myview;?

Or is it useless (maybe index statistics are already updated on refresh?)

Improve this question

1 Answer 1

Reset to default
12

Table statistics are not updated automatically.

  • They remain unchanged if refreshed with the CONCURRENTLY option.

  • Without CONCURRENTLY, a new file is written for the table, and the basic counts relpages and reltuples in the system catalog pg_class are consequently reset to 0. Table statistics (per column) in pg_statistic still remain unchanged, though.

I ran a quick test in Postgres 9.6 to confirm.

In some cases (where query plans don't depend on current statistics much) you may want to save the time and not run ANALYZE at all. In all other cases you'll want / need to schedule a manual ANALYZE myview; right after the REFRESH. Or you wait until autovacuum kicks in, which only happens if enough rows were changed (CONCURRENTLY actually runs DML commands on the MV to only affect changed rows.)

Improve this answer
1
  • @poshest: Yes, thank you. Fixed.
    – Erwin Brandstetter
    Commented Jul 23, 2021 at 22:43

Your Answer

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

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