RE: speeding up planning with partitions

Amit-san,

On Wed, Mar 20, 2019 at 0:42 AM, Amit Langote wrote:
> On 2019/03/19 20:13, Imai, Yoshikazu wrote:
> > Thanks for new patches.
> > I looked over them and there are little comments. 
> >
> > ...
> >
> > I have no more comments about codes other than above :)
> 
> I have fixed all.  Attached updated patches.

Thanks for quick modification.

I did performance test again. I did four tests in the below.
There might be some point we can improve performance more from the results of last test in the below.

(1)
v33-0003 slows down queries when there are inherited tables in UPDATE/DELETE's FROM clause.
v33-0004 fixes this problem.

* rt with 32 partitions.
* jrt with 32 partitions.
* update rt set c = jrt.c from jrt where rt.b = jrt.b;

patch     TPS
-----   -----
master   66.8 (67.2, 66.8, 66.4)
0003     47.5 (47.2, 47.6, 47.7)
0004     68.8 (69.2, 68.9, 68.4)

It seems fixing the performance problem correctly.

(2)
v33-0005 speeds up UPDATE/DELETE by removing useless copy of child target RTEs in adjust_appendrel_attrs().

* rt with 32 partitions.
* update rt set b = b + 1;

patch     TPS
-----   -----
master    986 (999, 984, 974)
0005    1,589 (1608, 1577, 1582)

It seems speeding up the performance as expected.

(3)
v33-0006, 0007, 0008 speeds up the case when few partitions are scanned among many partitions.

* rt with 4096 partitions, partitioned by column 'a'.
* select rt where rt.a = :a (:a ranges from 1 to 4096)

patch     TPS
-----   -----
master   22.4 (22.4, 22.5, 22.2)
0005     20.9 (20.9, 21.2, 20.6)
0006    2,951 (2958, 2958, 2937)
0007    3,141 (3146, 3141, 3136)
0008    6,472 (6434, 6565, 6416)

Certainly, it seems patches speed up the performance of the case.

(4)
We expect the performance does not depend on the number of partitions after applying all patches, if possible.

num of part    TPS
-----------  -----
1024         7,257 (7274, 7246, 7252)
2048         6,718 (6627, 6780, 6747)
4096         6,472 (6434, 6565, 6416) (quoted from above (3)'s results)
8192         6,008 (6018, 5999, 6007)

It seems the performance still depend on the number of partitions. At the moment, I don't have any idea what cause this problem but can we improve this more?


--
Yoshikazu Imai