> On Oct 25, 2020, at 10:52 PM, Ayub M <hiayub@gmail.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,
thereare no locks and no resource shortage, the number of recs in the base table is 6m (7.5gb) which is not huge so why
doesit 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
andreloaded 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
createdfor the first time it takes 16 minutes. Even when I remove all but one unique index it takes about 7 minutes.
Anyclue 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@americanefficient.com> wrote:
>
>
> > On Oct 23, 2020, at 9:52 AM, Ravi Krishna <sravikrishna@mail.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
tableand
> >> 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