pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Refresh materialized view vs recreate 2+ messages / 2 participants [nested] [flat]
* Refresh materialized view vs recreate @ 2016-11-01 06:26 Антон Мазунин <mazuninanton@gmail.com> 2016-11-01 15:25 ` Re: Refresh materialized view vs recreate Kevin Grittner <kgrittn@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Антон Мазунин @ 2016-11-01 06:26 UTC (permalink / raw) To: pgsql-performance Hello! 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? And what is the difference between refresh and create new? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Refresh materialized view vs recreate 2016-11-01 06:26 Refresh materialized view vs recreate Антон Мазунин <mazuninanton@gmail.com> @ 2016-11-01 15:25 ` Kevin Grittner <kgrittn@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Kevin Grittner @ 2016-11-01 15:25 UTC (permalink / raw) To: Антон Мазунин <mazuninanton@gmail.com>; +Cc: pgsql-performance On Tue, Nov 1, 2016 at 1:26 AM, Антон Мазунин <mazuninanton@gmail.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2016-11-01 15:25 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-11-01 06:26 Refresh materialized view vs recreate Антон Мазунин <mazuninanton@gmail.com> 2016-11-01 15:25 ` Kevin Grittner <kgrittn@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox