Re: postgres materialized view refresh performance

It's a simple sequential scan plan of one line, just reading the base table
sequentially.

On Mon, Oct 26, 2020, 9:21 AM Philip Semanchuk <philip(at)americanefficient(dot)com>
wrote:

>
>
> > 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
>
>