Re: [HACKERS] Block level parallel vacuum

On Tue, Nov 12, 2019 at 3:14 PM Mahendra Singh <mahi6run(at)gmail(dot)com> wrote:
>
> On Mon, 11 Nov 2019 at 16:36, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Mon, Nov 11, 2019 at 2:53 PM Mahendra Singh <mahi6run(at)gmail(dot)com> wrote:
> > >
> > >
> > > For small indexes also, we gained some performance by parallel vacuum.
> > >
> >
> > Thanks for doing all these tests. It is clear with this and previous
> > tests that this patch has benefit in wide variety of cases. However,
> > we should try to see some worst cases as well. For example, if there
> > are multiple indexes on a table and only one of them is large whereas
> > all others are very small say having a few 100 or 1000 rows.
> >
>
> Thanks Amit for your comments.
>
> I did some testing on the above suggested lines. Below is the summary:
> Test case:(I created 16 indexes but only 1 index is large, other are very small)
> create table test(a int, b int, c int, d int, e int, f int, g int, h int);
> create index i3 on test (a) where a > 2000 and a < 3000;
> create index i4 on test (a) where a > 3000 and a < 4000;
> create index i5 on test (a) where a > 4000 and a < 5000;
> create index i6 on test (a) where a > 5000 and a < 6000;
> create index i7 on test (b) where a < 1000;
> create index i8 on test (c) where a < 1000;
> create index i9 on test (d) where a < 1000;
> create index i10 on test (d) where a < 1000;
> create index i11 on test (d) where a < 1000;
> create index i12 on test (d) where a < 1000;
> create index i13 on test (d) where a < 1000;
> create index i14 on test (d) where a < 1000;
> create index i15 on test (d) where a < 1000;
> create index i16 on test (d) where a < 1000;
> insert into test select i,i,i,i,i,i,i,i from generate_series(1,1000000) as i;
> delete from test where a %2=0;
>
> case 1: vacuum without using parallel workers.
> vacuum test;
> 228.259 ms
>
> case 2: vacuum with 1 parallel worker.
> vacuum (parallel 1) test;
> 251.725 ms
>
> case 3: vacuum with 3 parallel workers.
> vacuum (parallel 3) test;
> 259.986
>
> From above results, it seems that if indexes are small, then parallel vacuum is not beneficial as compared to normal vacuum.
>

Right and that is what is expected as well. However, I think if
somehow disallow very small indexes to use parallel worker, then it
will be better. Can we use min_parallel_index_scan_size to decide
whether a particular index can participate in a parallel vacuum?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com