loading base tables (100000 posts, 10000 authors, ten posts per author)... loading 1 comments per post... loading 1 reactions per comment (100000 rows)... === fanout 1, serial, work_mem 4MB, median of 25 === query off ms on ms speedup EXISTS ceiling pushed ----------------------------------------------------------------------------------------- already an EXISTS (control) 32.4 35.1 0.92x - - - DISTINCT over 1:many join 68.0 69.2 0.98x 39.6 1.72x - count(DISTINCT) over 1:many join 21.4 21.6 0.99x 18.5 1.16x - GROUP BY with max() 67.6 65.0 1.04x 64.6 1.05x - DISTINCT, driver filtered 21.8 21.9 0.99x 16.3 1.33x - many to many 65.3 64.1 1.02x 37.7 1.73x - many to many, count(DISTINCT) 41.3 40.5 1.02x 22.0 1.88x - two-hop chain, DISTINCT 37.1 27.1 1.37x 23.8 1.56x fold two-hop chain, count(DISTINCT) 27.5 27.6 1.00x 23.0 1.19x fold three-hop chain, DISTINCT 60.3 46.8 1.29x 47.0 1.28x fold three-hop chain, count(DISTINCT) 46.2 46.6 0.99x 43.0 1.08x fold loading 2 comments per post... loading 2 reactions per comment (400000 rows)... === fanout 2, serial, work_mem 4MB, median of 25 === query off ms on ms speedup EXISTS ceiling pushed ----------------------------------------------------------------------------------------- already an EXISTS (control) 45.5 44.6 1.02x - - - DISTINCT over 1:many join 94.1 94.9 0.99x 46.6 2.02x - count(DISTINCT) over 1:many join 54.5 52.5 1.04x 37.1 1.47x - GROUP BY with max() 82.4 75.8 1.09x 84.0 0.98x semi DISTINCT, driver filtered 32.8 35.0 0.94x 24.5 1.34x - many to many 97.3 101.0 0.96x 53.4 1.82x - many to many, count(DISTINCT) 75.2 75.1 1.00x 34.8 2.16x semi two-hop chain, DISTINCT 66.3 43.5 1.53x 41.5 1.60x fold two-hop chain, count(DISTINCT) 50.9 41.3 1.23x 39.2 1.30x fold three-hop chain, DISTINCT 252.0 23.6 10.70x 21.8 11.53x semi three-hop chain, count(DISTINCT) 202.9 21.7 9.34x 19.2 10.56x semi loading 8 comments per post... loading 8 reactions per comment (6400000 rows)... === fanout 8, serial, work_mem 4MB, median of 25 === query off ms on ms speedup EXISTS ceiling pushed ----------------------------------------------------------------------------------------- already an EXISTS (control) 98.1 96.7 1.01x - - - DISTINCT over 1:many join 320.8 184.3 1.74x 108.8 2.95x fold count(DISTINCT) over 1:many join 153.2 141.1 1.09x 76.7 2.00x fold GROUP BY with max() 224.3 149.5 1.50x 126.3 1.78x semi DISTINCT, driver filtered 90.1 106.1 0.85x 60.1 1.50x fold many to many 265.5 184.9 1.44x 132.7 2.00x fold many to many, count(DISTINCT) 258.1 172.7 1.49x 114.2 2.26x fold two-hop chain, DISTINCT 248.4 17.4 14.29x 14.5 17.13x semi two-hop chain, count(DISTINCT) 233.3 14.5 16.13x 11.9 19.63x semi three-hop chain, DISTINCT 4007.5 24.7 162.42x 22.4 178.53x semi three-hop chain, count(DISTINCT) 2961.5 22.3 132.77x 20.2 146.87x semi loading 32 comments per post... skipping reaction and the three-hop cases above fanout 8 === fanout 32, serial, work_mem 4MB, median of 25 === query off ms on ms speedup EXISTS ceiling pushed ----------------------------------------------------------------------------------------- already an EXISTS (control) 84.3 85.3 0.99x - - - DISTINCT over 1:many join 1224.8 127.3 9.62x 97.8 12.52x semi count(DISTINCT) over 1:many join 601.0 98.9 6.07x 70.4 8.54x semi GROUP BY with max() 769.9 111.9 6.88x 116.8 6.59x semi DISTINCT, driver filtered 340.6 41.7 8.17x 35.1 9.71x semi many to many 1249.0 541.3 2.31x 537.5 2.32x fold many to many, count(DISTINCT) 1078.9 533.3 2.02x 544.3 1.98x fold two-hop chain, DISTINCT 1055.9 18.6 56.84x 16.8 62.83x semi two-hop chain, count(DISTINCT) 974.2 16.9 57.58x 14.6 66.80x semi === Delivered speedup vs master === query fanout =1 =2 =8 =32 --------------------------------------------------------------------- already an EXISTS (control) 0.92x* 1.02x* 1.01x* 0.99x* DISTINCT over 1:many join 0.98x* 0.99x* 1.74x 9.62x count(DISTINCT) over 1:many join 0.99x* 1.04x* 1.09x 6.07x GROUP BY with max() 1.04x* 1.09x 1.50x 6.88x DISTINCT, driver filtered 0.99x* 0.94x* 0.85x 8.17x many to many 1.02x* 0.96x* 1.44x 2.31x many to many, count(DISTINCT) 1.02x* 1.00x 1.49x 2.02x two-hop chain, DISTINCT 1.37x 1.53x 14.29x 56.84x two-hop chain, count(DISTINCT) 1.00x 1.23x 16.13x 57.58x three-hop chain, DISTINCT 1.29x 10.70x 162.42x DNR three-hop chain, count(DISTINCT) 0.99x 9.34x 132.77x DNR * the plan did not change | DNR -> not run === Which pushdown the planner chose === query fanout =1 =2 =8 =32 --------------------------------------------------------------------- already an EXISTS (control) - - - - DISTINCT over 1:many join - - fold semi count(DISTINCT) over 1:many join - - fold semi GROUP BY with max() - semi semi semi DISTINCT, driver filtered - - fold semi many to many - - fold fold many to many, count(DISTINCT) - semi fold fold two-hop chain, DISTINCT fold fold semi semi two-hop chain, count(DISTINCT) fold fold semi semi three-hop chain, DISTINCT fold semi semi DNR three-hop chain, count(DISTINCT) fold semi semi DNR === Hand-written EXISTS vs master: the ceiling === query fanout =1 =2 =8 =32 --------------------------------------------------------------------- DISTINCT over 1:many join 1.72x 2.02x 2.95x 12.52x count(DISTINCT) over 1:many join 1.16x 1.47x 2.00x 8.54x GROUP BY with max() 1.05x 0.98x 1.78x 6.59x DISTINCT, driver filtered 1.33x 1.34x 1.50x 9.71x many to many 1.73x 1.82x 2.00x 2.32x many to many, count(DISTINCT) 1.88x 2.16x 2.26x 1.98x two-hop chain, DISTINCT 1.56x 1.60x 17.13x 62.83x two-hop chain, count(DISTINCT) 1.19x 1.30x 19.63x 66.80x three-hop chain, DISTINCT 1.28x 11.53x 178.53x DNR three-hop chain, count(DISTINCT) 1.08x 10.56x 146.87x DNR === Share of that ceiling the pushdown captures === query fanout =1 =2 =8 =32 --------------------------------------------------------------------- DISTINCT over 1:many join 57% 49% 59% 77% count(DISTINCT) over 1:many join 85% 71% 54% 71% GROUP BY with max() 99% 111% 85% 104% DISTINCT, driver filtered 75% 70% 57% 84% many to many 59% 53% 72% 99% many to many, count(DISTINCT) 54% 46% 66% 102% two-hop chain, DISTINCT 88% 95% 83% 90% two-hop chain, count(DISTINCT) 83% 95% 82% 86% three-hop chain, DISTINCT 100% 93% 91% DNR three-hop chain, count(DISTINCT) 92% 88% 90% DNR