Re: [HACKERS] Block level parallel vacuum

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.

> Note: Please don't use the top-posting style to reply. Here, we use
> inline reply.

Okay. I will follow inline reply.

Thanks and Regards
Mahendra Thalor
EnterpriseDB: http://www.enterprisedb.com