Re: Refresh materialized view vs recreate

On Tue, Nov 1, 2016 at 1:26 AM, Антон Мазунин
<mazuninanton(at)gmail(dot)com> wrote:

> We have a situation when after creation of new materialized view
> cpu utilization falls down (from about 50% to about 30%), at the
> same time we have a cron job, which does refresh of old
> materialized view, but it does no effect at performance.
> Can anyone explain why is it so?

I am not able to understand what you are saying here. Could you
perhaps show the commands you are using and their output (both to
create or refresh the materialized views and to measure impact)?

> what is the difference between refresh and create new?

In either case the query associated with the materialized view is
run, and the output saved to storage. For CREATE or for REFRESH
without CONCURRENTLY, it is saved to the permanent tablespace and
indexes are built from scratch. For REFRESH CONCURRENTLY the query
result is saved to a temporary workspace and this is "diffed"
against the existing permanent copy, which is modified to match the
new data through simple DML statements. No explicit index rebuild
is needed; entries are adjusted as part of running the DML.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company