On Thu, Jul 23, 2020 at 9:22 AM Amit Kapila <
amit.kapila16@gmail.com> wrote:
>
>>
>> I ran tests for partitioned use cases - results are similar to that of non partitioned cases[1].
>
>
> I could see the gain up to 10-11 times for non-partitioned cases [1], can we use similar test case here as well (with one of the indexes on text column or having gist index) to see its impact?
>
> [1] -
https://www.postgresql.org/message-id/CALj2ACVR4WE98Per1H7ajosW8vafN16548O2UV8bG3p4D3XnPg%40mail.gmail.com>
Thanks Amit! Please find the results of detailed testing done for partitioned use cases:
Range Partitions: consecutive rows go into the same partitions.
| parallel workers | test case 1(exec time in sec): copy from csv file, 2 indexes on integer columns and 1 index on text column, 4 range partitions | test case 2(exec time in sec): copy from csv file, 1 gist index on text column, 4 range partitions | test case 3(exec time in sec): copy from csv file, 3 indexes on integer columns, 4 range partitions |
| 0 | 1051.924(1X) | 785.052(1X) | 205.403(1X) |
| 2 | 589.576(1.78X) | 421.974(1.86X) | 114.724(1.79X) |
| 4 | 321.960(3.27X) | 230.997(3.4X) | 99.017(2.07X) |
| 8 | 199.245(5.23X) | 156.132(5.02X) | 99.722(2.06X) |
| 16 | 127.343(8.26X) | 173.696(4.52X) | 98.147(2.09X) |
| 20 | 122.029(8.62X) | 186.418(4.21X) | 97.723(2.1X) |
| 30 | 142.876(7.36X) | 214.598(3.66X) | 97.048(2.11X) |
>
> I think, when doing the performance testing for partitioned table, it would be good to also mention about the distribution of data in the input file. One possible data distribution could be that we have let's say 100 tuples in the input file, and every consecutive tuple belongs to a different partition.
>
To address Ashutosh's point, I used hash partitioning. Hope this helps to clear the doubt.
Hash Partitions: where there are high chances that consecutive rows may go into different partitions.
| parallel workers | test case 1(exec time in sec): copy from csv file, 2 indexes on integer columns and 1 index on text column, 4 hash partitions | test case 2(exec time in sec): copy from csv file, 1 gist index on text column, 4 hash partitions | test case 3(exec time in sec): copy from csv file, 3 indexes on integer columns, 4 hash partitions |
| 0 | 1060.884(1X) | 812.283(1X) | 207.745(1X) |
| 2 | 572.542(1.85X) | 418.454(1.94X) | 107.850(1.93X) |
| 4 | 298.132(3.56X) | 227.367(3.57X) | 83.895(2.48X) |
| 8 | 169.449(6.26X) | 137.993(5.89X) | 85.411(2.43X) |
| 16 | 112.297(9.45X) | 95.167(8.53X) | 96.136(2.16X) |
| 20 | 101.546(10.45X) | 90.552(8.97X) | 97.066(2.14X) |
| 30 | 113.877(9.32X) | 127.17(6.38X) | 96.819(2.14X) |
With Regards,
Bharath Rupireddy.
EnterpriseDB:
http://www.enterprisedb.com