4

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?)

6

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 DDL commands on the MV to only affect changed rows.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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