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?)
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Table statistics are not updated automatically.
They remain unchanged if refreshed with the
CONCURRENTLY, a new file is written for the table, and the basic counts
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.)