Re: 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:
> 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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +