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 <phi...@americanefficient.com> wrote: > > > > 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 > >
