Re: BUG #11221: pg_restore unusable for expensive matviews

On Wed, Aug 20, 2014 at 02:15:30AM +0000, jbaum(at)cmcrc(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 11221
> Logged by: Jiri Baum
> Email address: jbaum(at)cmcrc(dot)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:
> http://www.postgresql.org/message-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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +