Re: postgres materialized view refresh performance

> On Oct 25, 2020, at 10:52 PM, Ayub M <hiayub(at)gmail(dot)com> wrote:
>
> Thank you both.
>
> As for the mview refresh taking long --
> • The mview gets refreshed in a couple of mins sometimes and sometimes it takes hours. When it runs for longer, there are no locks and no resource shortage, the number of recs in the base table is 6m (7.5gb) which is not huge so why does it take so long to refresh the mview?
>
> Does the run time correlate with the number of changes being made?
>
> -- Almost the same number of records are present in the base table (6 million records). The base table gets truncated and reloaded everytime with almost the same number of records.
>
> And the mview is a simple select from this one base table.
>
> The mview has around 10 indexes, 1 unique and 9 non-unique indexes.
>
> Population of the base tables takes about 2 mins, using "insert into select from table", but when the mview is created for the first time it takes 16 minutes. Even when I remove all but one unique index it takes about 7 minutes. Any clue as to why it is taking longer than the create of the base table (which is 2 mins).

Do you know if it’s executing a different plan when it takes a long time? auto_explain can help with that.

>
> On Fri, Oct 23, 2020 at 10:53 AM Philip Semanchuk <philip(at)americanefficient(dot)com> wrote:
>
>
> > On Oct 23, 2020, at 9:52 AM, Ravi Krishna <sravikrishna(at)mail(dot)com> wrote:
> >
> >> My understanding is that when CONCURRENTLY is specified, Postgres implements the refresh as a series of INSERT, UPDATE,
> >> and DELETE statements on the existing view. So the answer to your question is no, Postgres doesn’t create another table and
> >> then swap it.
> >
> > The INSERTS/UPDATE/DELETE happens only for the difference. PG first creates a new temp table and then compares it with
> > the MV and detects the difference. That is why for CONCURRENTLY, a unique index is required on the MV.
>
> Yes, thank you, that’s what I understand too but I expressed it very poorly.
>
>
>
> --
> Regards,
> Ayub