BUG #11221: pg_restore unusable for expensive matviews

From: jbaum(at)cmcrc(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11221: pg_restore unusable for expensive matviews
Date: 2014-08-20 02:15:30
Message-ID: 20140820021530.2534.43156@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

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

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:
(not listed on the TODO wiki page)


Browse pgsql-bugs by date

  From Date Subject
Next Message Eduard Wulff 2014-08-20 08:01:44 Re: BUG #11211: regexp_matches acts like a WHERE
Previous Message Tom Lane 2014-08-20 01:28:33 Re: BUG #11207: empty path will segfault jsonb #>