BUG #11221: pg_restore unusable for expensive matviews

On Wed, Aug 20, 2014 at 02:15:30AM +0000, jbaum@cmcrc.com wrote:

The following bug has been logged on the website:

Bug reference: 11221
Logged by: Jiri Baum
Email address: jbaum@cmcrc.com
PostgreSQL version: 9.3.5
Operating system: Linux
Description:

When restoring a database with expensive materialized views, pg_restore
issues the REFRESH MATERIALIZED VIEW commands without regard to whether
indexes are valid yet or whether autoanalyze has completed.

As a result, if a materialized view is expensive and relies on indexes
and/or good query plans, the restore will take prohibitively long.

This is likely to be a common use case, since there's not much point
materializing views that are inexpensive.

Possible solution: pg_restore should have an option (default?) to wait for
indexes to be valid and run ANALYZE before issuing the REFRESH MATERIALIZED
VIEW commands, either in pg_restore itself or by adding commands to wait for
(relevant or all) indexes to be valid.

Workaround: Hack stuff up out of the -l and -L options to pg_restore.

Possibly duplicates:
/messages/by-id/1403794157042-5809367.post@n5.nabble.com
(not listed on the TODO wiki page)

I have added TODO entry:

Delay REFRESH MATERIALIZED VIEW until dependent indexes are created

o pg_restore unusable for expensive matviews

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs