TPC-H 10G Skew Factor Z=2 results
---------------------------------

LI-P Regular HJ: (time in milliseconds)
505672
424922
303250
361610
358125
Average: 390715.8

LI-P Skew-enabled HJ: (time in milliseconds)
219078
210078
212938
210094
212500
Average: 212937.6

% difference: 45.5%

LI-P
Values: 9 Skew: 0.94  Est. tuples: 59986052.00 Batches: 512  Est. Save: 56174982.55
Total Inner Tuples: 2000000
IM Inner Tuples: 9
Batch Zero Inner Tuples: 3941
Batch Zero Potential Inner Tuples: 3941
Total Outer Tuples: 59986052
IM Outer Tuples: 56163835
Batch Zero Outer Tuples: 825
Batch Zero Potential Outer Tuples: 825
Total Output Tuples: 59986052
IM Output Tuples: 56163835
Batch Zero Output Tuples: 825
Batch Zero Potential Output Tuples: 825
Percentage less tuple IOs than HHJ: 90.61


This is the test query suggested by Josh:

SELECT * FROM lineitem l LEFT JOIN part p ON (p.p_partkey = l.l_partkey) LEFT JOIN orders o ON (o.o_orderkey = l.l_orderkey) LEFT JOIN customer c ON (c.c_custkey = o.o_custkey);

LI-P-O-C Regular HJ: (time in milliseconds)
8193906
7274906
Average: 7734406

LI-P-O-C Skew-enabled HJ: (time in milliseconds)
7965641
7656969
Average: 7811305

% difference: -0.99%	(even the heavily skewed LI-P join cannot improve the time for this query that takes about 2 hours).

There is no skew benefit for the C-O or O-LI parts of this query that dominate its cost.

LI-P-S Regular HJ: (time in milliseconds)
1112515
981313
801281
761359
765734
1064282
938937
839906
Average: 861916.5

LI-P-S Skew-enabled HJ: (time in milliseconds)
303407
307375
410329
295047
296594
334375
294609
Average: 326190.8

% difference: 62%


LI-S
Values: 27 Skew: 0.92  Est. tuples: 59986052.00 Batches: 32  Est. Save: 53396708.76
Total Inner Tuples: 100000
IM Inner Tuples: 27
Batch Zero Inner Tuples: 3125
Batch Zero Potential Inner Tuples: 3125
Total Outer Tuples: 59986052
IM Outer Tuples: 54957282
Batch Zero Outer Tuples: 174737
Batch Zero Potential Outer Tuples: 174737
Total Output Tuples: 59986052
IM Output Tuples: 54957282
Batch Zero Output Tuples: 174737
Batch Zero Potential Output Tuples: 174737
Percentage less tuple IOs than HHJ: 91.74

(LI-S)-P
Values: 9 Skew: 0.94  Est. tuples: 59986052.00 Batches: 512  Est. Save: 56174982.55
Total Inner Tuples: 2000000
IM Inner Tuples: 9
Batch Zero Inner Tuples: 3941
Batch Zero Potential Inner Tuples: 3941
Total Outer Tuples: 59986052
IM Outer Tuples: 56163835
Batch Zero Outer Tuples: 825
Batch Zero Potential Outer Tuples: 825
Total Output Tuples: 59986052
IM Output Tuples: 56163835
Batch Zero Output Tuples: 825
Batch Zero Potential Output Tuples: 825
Percentage less tuple IOs than HHJ: 90.61