On Tue, Nov 12, 2019 at 3:14 PM Mahendra Singh <mahi6...@gmail.com> wrote: > > On Mon, 11 Nov 2019 at 16:36, Amit Kapila <amit.kapil...@gmail.com> wrote: > > > > On Mon, Nov 11, 2019 at 2:53 PM Mahendra Singh <mahi6...@gmail.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
