In backup and restore its taking 1 hour extra time than usual postgres

If it is a complicated query, maybe the problem is that autoanalyze didn't have time yet to calculate table statistics. You can interrupt the statement with pg_cancel_backend() and recreate the materialized view later, perhaps after an ANALYZE, if it helps you to bring the rest of the database up quickly. Materialized views don't necessarily have better performance than the underlying query for the first access. The complexity of assembly of the data may be trivial. A number of joins that use indices would be much less intensive than a complex set of criteria, or something that requires functional processing or sorting for example. What you have found in this instance, is that the underlying query is just as good as a materialized view for the same dataset. You might want to consider a simple view for this case.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
You need to check (make sure) that you stop using wrong tools (like dumps) for DR. ;)
So, do you? Or, why / what for do you use dumps?
Pranam Bhat5y ago
If it is a complicated query, maybe the problem is that autoanalyze didn't have time yet to calculate table statistics. You can interrupt the statement with pg_cancel_backend() and recreate the materialized view later, perhaps after an ANALYZE, if it helps you to bring the rest of the database up quickly. Materialized views don't necessarily have better performance than the underlying query for the first access. The complexity of assembly of the data may be trivial. A number of joins that use indices would be much less intensive than a complex set of criteria, or something that requires functional processing or sorting for example. What you have found in this instance, is that the underlying query is just as good as a materialized view for the same dataset. You might want to consider a simple view for this case.