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

On Wed, Sep 20, 2017 at 3:13 PM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

> On Wed, Sep 20, 2017 at 9:44 AM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> > 2. What queries in the 0008 patch are hitting lines that 0007 doesn't
> hit?
> >
> > I thought about how to answer questions like this and came up with a
> > shell script that (1) makes computers run really hot for quite a long
> > time and (2) tells you which blocks of SQL hit which lines of C.
> > Please find attached the shell script and its output. The .sql files
> > have been annotated with "block" numbers (blocks being chunks of SQL
> > stuff separated by blank lines), and the C files annotated with
> > references to those block numbers where A<n> = block <n>
> > partition_join.sql and B<n> = block <n> in partition_join_extras.sql.
> >
> > Then to find lines that B queries hit but A queries don't and know
> > which particular queries hit them, you might use something like:
> >
> > grep -v 'SQL blocks: .*A[0-9]' < joinpath.c.aggregated_coverage | \
> > grep 'SQL blocks: .*B[0-9]'
> >
>
> Thanks for this. It generates a lot of output (970 lines over all the
> coverage files). It will take some time for getting anything
> meaningful out of this. May be there's some faster way by looking at
> the lines that are covered by B but not A. BTW, I checked those lines
> to see if there could be any bug there. But I don't see what could go
> wrong with those lines.
>
> I have also tried to find test cases in B which hits some extra line which
is not
hitting by A, with the help of results attached by Thomas in
coverage.tarball_FILES.
It took lot of time but I am able to find some test cases. which if adding
in partition_join.sql
increasing no of lines hit by 14. but for hitting these 14 extra line
attached patch is doing
900+ line inserts in partition_join.sql and partition_join.out file.

I have used gcov-lcov to find coverage for files changed by
partition-wise-join patches
with and without attached patch which is below.

*with existing partition_join.sql* *partition_join.sql + some test cases of
partition_join_extra.sql*
*Modifed Files* *Line Coverage* *Functions* *Line Coverage* *Functions*
src/backend/optimizer/geqo 79.4 % 269/339 96.6 % 28/29 79.4 % 269/339 96.6 %
28/29
src/backend/optimizer/path/allpaths.c 92.3 % 787 / 853 95.5 % 42 / 44
92.6 % 790
/ 853 95.5 % 42 / 44
src/backend/optimizer/path/costsize.c 96.8 % 1415 / 1462 98.4 % 61 / 62
96.9 % 1416 / 1462 98.4 % 61 / 62
src/backend/optimizer/path/joinpath.c 95.5 % 404 / 423 100.0 % 16 / 16
95.5 % 404 / 423 100.0 % 16 / 16
src/backend/optimizer/path/joinrels.c 92.5 % 422 / 456 100.0 % 16 / 16
93.0 % 424 / 456 100.0 % 16 / 16
src/backend/optimizer/plan/createplan.c 90.9 % 1928 / 2122 96.3 % 103 / 107
91.0 % 1930 / 2122 96.3 % 103 / 107
src/backend/optimizer/plan/planner.c 94.9 % 1609 / 1696 97.6 % 41 / 42
94.9 % 1609 / 1696 97.6 % 41 / 42
src/backend/optimizer/plan/setrefs.c 91.3 % 806 / 883 94.3 % 33 / 35 91.3 % 806
/ 883 94.3 % 33 / 35
src/backend/optimizer/prep/prepunion.c 95.5 % 661 / 692 100.0 % 25 / 25
95.5 % 661 / 692 100.0 % 25 / 25
src/backend/optimizer/util/pathnode.c 88.7 % 1144 / 1290 98.1 % 52 / 53
88.8 % 1146 / 1290 98.1 % 52 / 53
src/backend/optimizer/util/placeholder.c 96.5 % 139 / 144 100.0 % 10 / 10
96.5 % 139 / 144 100.0 % 10 / 10
src/backend/optimizer/util/plancat.c 89.0 % 540 / 607 94.7 % 18 / 19 89.6 % 544
/ 607 94.7 % 18 / 19
src/backend/optimizer/util/relnode.c 95.3 % 548 / 575 100.0 % 24 / 24
95.3 % 548
/ 575 100.0 % 24 / 24
src/backend/utils/misc/guc.c 67.4 % 1536 / 2278 89.7 % 113 / 126 67.4 % 1536
/ 2278 89.7 % 113 / 126

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation