> On Oct 25, 2020, at 10:52 PM, Ayub M <hia...@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, 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
> <phi...@americanefficient.com> wrote:
>
>
> > On Oct 23, 2020, at 9:52 AM, Ravi Krishna <sravikris...@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 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
