Re: Partition-wise join for join between (declaratively) partitioned tables

On Fri, Sep 15, 2017 at 2:09 PM, Rafia Sabih
<rafia(dot)sabih(at)enterprisedb(dot)com> wrote:
>>
> On TPC-H benchmarking of this patch, I found a regression in Q7. It
> was taking some 1500s with the patch and some 900s without the patch.
> Please find the attached pwd_reg.zip for the output of explain analyse
> on head and with patch.
>
> The experimental settings used were,
> commit-id = 0c504a80cf2e6f66df2cdea563e879bf4abd1629
> patch-version = v26
>
> Server settings:
> work_mem = 1GB
> shared_buffers = 10GB
> effective_cache_size = 10GB
> max_parallel_workers_per_gather = 4
>
> Partitioning information:
> Partitioning scheme = by range
> Number of partitions in lineitem and orders table = 106
> partition key for lineitem = l_orderkey
> partition key for orders = o_orderkey
>
> Apart from these there is a regression case on a custom table, on head
> query completes in 20s and with this patch it takes 27s. Please find
> the attached .out and .sql file for the output and schema for the test
> case respectively. I have reported this case before (sometime around
> March this year) as well, but I am not sure if it was overlooked or is
> an unimportant and expected behaviour for some reason.
>

On completing the benchmark for all queries for the above mentioned
setup, following performance improvement can be seen,
Query | Patch | Head
3 | 1455 | 1631
4 | 499 | 4344
5 | 1464 | 1606
10 | 1475 | 1599
12 | 1465 | 1790

Note that all values of execution time are in seconds.
To summarise, apart from Q4, all other queries are showing somewhat
10-20% improvement. Though it is good but honestly, I expected more
from this feature atleast on this scale factor. I am yet to compare
these performances with the unpartitioned version of the database.

Please find attached file for the output of explain analyse for all
the queries on head and with patch.

--
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/