Feature: add PGO support

[zamazan4ik]

Summary

Profile-Guided Optimization (PGO) can help with gaining more performance for Databend.

Possible improvements:

• Test, how PGO influences Databend performance. Maybe it's not worth it :)
• Integrate PGO into the regular CI builds. In this case, users will get PGO-boosted builds from the Release page. However, it will require preparing a sample workload.
• Try to use LLVM BOLT additionally to PGO

More about PGO (more results with numbers for other applications, possible caveats, etc.) you can find here.

[PsiACE]

Let me see how this can be done, it feels like it will be interesting.

[sundy-li]

@PsiACE Any progress? We can test it when @everpcpc finish the perf scripts.

[PsiACE]

@PsiACE Any progress?

I've put together an internal document on PGO, but for users, they should build to suit their workload.

I'll sync it to the site/blog in the near future. It's worth noting that PGO doesn't always lead to performance optimization.

[sundy-li]

Just bench 4 queries of hits dataset.

https://docs.google.com/spreadsheets/d/16odG8rz3e5nP8-myyIK4IVqxRDVame4jpcSuPPqzHxc/edit#gid=0

[zamazan4ik]

@PsiACE @sundy-li I performed a more robust benchmark over release vs release+PGO Databench and want to share my results here.

Hopefully, these numbers would be interesting for someone. Also, I suggest posting these results in the documentation so Databend users can estimate PGO benefits for them. As you see, PGO makes a consistent improvement for Databend.

Test environment:

• Fedora 38
• Linux kernel 6.2.15
• AMD Ryzen 9 5800x
• 48 Gib RAM
• SSD Samsung 980 Pro 2 Tib
• Databend version: latest commit for now

As a training and evaluation set, I used ClickBench.

The results are the following:

Release (in CSV)

1,1,0.050026
1,2,0.005401
1,3,0.004386
1,4,0.004146
1,5,0.004459
1,6,0.004292
1,7,0.004146
1,8,0.004342
1,9,0.004291
1,10,0.004218
2,1,0.134557
2,2,0.023229
2,3,0.022468
2,4,0.021895
2,5,0.022071
2,6,0.022510
2,7,0.021968
2,8,0.021921
2,9,0.021493
2,10,0.021969
3,1,0.173451
3,2,0.042243
3,3,0.042484
3,4,0.042752
3,5,0.042042
3,6,0.041156
3,7,0.043720
3,8,0.041701
3,9,0.041424
3,10,0.041359
4,1,0.283332
4,2,0.032512
4,3,0.031995
4,4,0.032098
4,5,0.031732
4,6,0.031943
4,7,0.031992
4,8,0.032305
4,9,0.031731
4,10,0.031839
5,1,0.457098
5,2,0.344052
5,3,0.343285
5,4,0.344150
5,5,0.348119
5,6,0.337637
5,7,0.340816
5,8,0.338742
5,9,0.338636
5,10,0.342720
6,1,0.862271
6,2,0.645672
6,3,0.646106
6,4,0.648641
6,5,0.644757
6,6,0.640993
6,7,0.649696
6,8,0.642852
6,9,0.648390
6,10,0.649156
7,1,0.089584
7,2,0.030858
7,3,0.028055
7,4,0.027979
7,5,0.028889
7,6,0.028454
7,7,0.028498
7,8,0.028578
7,9,0.028407
7,10,0.027545
8,1,0.086659
8,2,0.025216
8,3,0.025591
8,4,0.027003
8,5,0.027145
8,6,0.025188
8,7,0.027686
8,8,0.028050
8,9,0.025356
8,10,0.024804
9,1,0.663055
9,2,0.511075
9,3,0.496364
9,4,0.492999
9,5,0.500250
9,6,0.490088
9,7,0.503038
9,8,0.498859
9,9,0.501576
9,10,0.506489
10,1,0.744321
10,2,0.536469
10,3,0.538781
10,4,0.536948
10,5,0.533051
10,6,0.544037
10,7,0.533225
10,8,0.537343
10,9,0.537739
10,10,0.541745
11,1,0.434644
11,2,0.196753
11,3,0.188048
11,4,0.189915
11,5,0.183505
11,6,0.191594
11,7,0.192489
11,8,0.191243
11,9,0.190163
11,10,0.185209
12,1,0.385988
12,2,0.165845
12,3,0.161341
12,4,0.159535
12,5,0.162062
12,6,0.157021
12,7,0.155943
12,8,0.158228
12,9,0.159900
12,10,0.160396
13,1,1.082393
13,2,0.850055
13,3,0.850640
13,4,0.847859
13,5,0.851002
13,6,0.845397
13,7,0.845867
13,8,0.849816
13,9,0.851234
13,10,0.850801
14,1,1.609629
14,2,1.303916
14,3,1.309553
14,4,1.309645
14,5,1.307854
14,6,1.296369
14,7,1.309271
14,8,1.313427
14,9,1.317088
14,10,1.299861
15,1,1.135177
15,2,0.936678
15,3,0.925734
15,4,0.923465
15,5,0.918942
15,6,0.919308
15,7,0.915883
15,8,0.923629
15,9,0.914548
15,10,0.917202
16,1,0.873891
16,2,0.773152
16,3,0.778891
16,4,0.769694
16,5,0.770811
16,6,0.772402
16,7,0.771161
16,8,0.770129
16,9,0.776566
16,10,0.772827
17,1,3.311647
17,2,3.013585
17,3,3.053314
17,4,3.027522
17,5,3.050140
17,6,3.061314
17,7,3.064766
17,8,3.061211
17,9,3.038234
17,10,3.076854
18,1,1.840432
18,2,1.590880
18,3,1.569952
18,4,1.559939
18,5,1.553125
18,6,1.560262
18,7,1.535251
18,8,1.520765
18,9,1.561211
18,10,1.530577
19,1,4.817216
19,2,4.483809
19,3,4.507087
19,4,4.510731
19,5,4.514817
19,6,4.517014
19,7,4.511981
19,8,4.530623
19,9,4.515187
19,10,4.509778
20,1,0.121320
20,2,0.006585
20,3,0.006621
20,4,0.006207
20,5,0.005561
20,6,0.005696
20,7,0.005657
20,8,0.006028
20,9,0.005569
20,10,0.005626
21,1,1.236447
21,2,0.304571
21,3,0.308297
21,4,0.304297
21,5,0.304736
21,6,0.300946
21,7,0.303319
21,8,0.306606
21,9,0.307211
21,10,0.300812
22,1,1.439052
22,2,0.375744
22,3,0.377096
22,4,0.375978
22,5,0.376899
22,6,0.377708
22,7,0.379931
22,8,0.378716
22,9,0.373669
22,10,0.378261
23,1,2.081930
23,2,0.695606
23,3,0.687592
23,4,0.683988
23,5,0.696888
23,6,0.688566
23,7,0.686913
23,8,0.687388
23,9,0.689495
23,10,0.692413
24,1,
24,2,
24,3,
24,4,
24,5,
24,6,
24,7,
24,8,
24,9,
24,10,
25,1,6.286131
25,2,0.028502
25,3,0.028054
25,4,0.027637
25,5,0.027284
25,6,0.028093
25,7,0.027985
25,8,0.027617
25,9,0.027763
25,10,0.027409
26,1,0.347750
26,2,0.129036
26,3,0.133238
26,4,0.131119
26,5,0.132262
26,6,0.137220
26,7,0.130487
26,8,0.137526
26,9,0.136009
26,10,0.136282
27,1,0.170214
27,2,0.057899
27,3,0.028900
27,4,0.028934
27,5,0.028449
27,6,0.028648
27,7,0.028227
27,8,0.028095
27,9,0.028324
27,10,0.027837
28,1,1.239418
28,2,0.305298
28,3,0.312231
28,4,0.310203
28,5,0.304119
28,6,0.312433
28,7,0.309523
28,8,0.310116
28,9,0.307490
28,10,0.309860
29,1,8.793339
29,2,8.653600
29,3,8.681540
29,4,8.742904
29,5,8.744620
29,6,8.781341
29,7,8.801848
29,8,8.819547
29,9,8.823820
29,10,8.853989
30,1,0.230037
30,2,0.107964
30,3,0.106887
30,4,0.107225
30,5,0.105960
30,6,0.107189
30,7,0.109013
30,8,0.106457
30,9,0.107579
30,10,0.107917
31,1,0.830755
31,2,0.419906
31,3,0.417990
31,4,0.421011
31,5,0.418664
31,6,0.421211
31,7,0.419943
31,8,0.416985
31,9,0.418651
31,10,0.418258
32,1,1.846663
32,2,1.243098
32,3,1.227840
32,4,1.213282
32,5,1.206771
32,6,1.224928
32,7,1.201760
32,8,1.217043
32,9,1.212691
32,10,1.225716
33,1,8.301313
33,2,7.663124
33,3,7.691085
33,4,7.698990
33,5,7.686395
33,6,7.663507
33,7,7.704894
33,8,7.656293
33,9,7.665789
33,10,7.679501
34,1,4.893191
34,2,4.295481
34,3,4.245025
34,4,4.308801
34,5,4.292448
34,6,4.333450
34,7,4.311795
34,8,4.313715
34,9,4.331196
34,10,4.322769
35,1,4.967232
35,2,4.299791
35,3,4.336239
35,4,4.344043
35,5,4.303286
35,6,4.317500
35,7,4.325992
35,8,4.310504
35,9,4.350881
35,10,4.371835
36,1,0.846336
36,2,0.695227
36,3,0.692435
36,4,0.687485
36,5,0.686108
36,6,0.686078
36,7,0.682114
36,8,0.682460
36,9,0.676762
36,10,0.674969
37,1,0.154173
37,2,0.163062
37,3,0.143059
37,4,0.152884
37,5,0.168623
37,6,0.112146
37,7,0.171182
37,8,0.165078
37,9,0.163111
37,10,0.150808
38,1,0.083893
38,2,0.039210
38,3,0.035657
38,4,0.026847
38,5,0.049169
38,6,0.039447
38,7,0.028548
38,8,0.031388
38,9,0.026745
38,10,0.025150
39,1,0.086246
39,2,0.017168
39,3,0.021550
39,4,0.025425
39,5,0.024406
39,6,0.020337
39,7,0.018736
39,8,0.019656
39,9,0.020272
39,10,0.021543
40,1,0.213684
40,2,0.148415
40,3,0.145614
40,4,0.147505
40,5,0.138993
40,6,0.147749
40,7,0.163328
40,8,0.150743
40,9,0.138363
40,10,0.136700
41,1,0.097148
41,2,0.020580
41,3,0.023213
41,4,0.039411
41,5,0.018678
41,6,0.019873
41,7,0.019078
41,8,0.018894
41,9,0.018677
41,10,0.020778
42,1,0.074079
42,2,0.015104
42,3,0.013426
42,4,0.013551
42,5,0.013460
42,6,0.015097
42,7,0.014503
42,8,0.013239
42,9,0.012883
42,10,0.012397
43,1,0.073441
43,2,0.013318
43,3,0.012371
43,4,0.013009
43,5,0.012612
43,6,0.013861
43,7,0.012465
43,8,0.012504
43,9,0.012195
43,10,0.012430

Release (with SQL queries)

SELECT COUNT(*) FROM hits;
[0.050026, 0.005401, 0.004386, 0.004146, 0.004459, 0.004292, 0.004146, 0.004342, 0.004291, 0.004218],
SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;
[0.134557, 0.023229, 0.022468, 0.021895, 0.022071, 0.022510, 0.021968, 0.021921, 0.021493, 0.021969],
SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;
[0.173451, 0.042243, 0.042484, 0.042752, 0.042042, 0.041156, 0.043720, 0.041701, 0.041424, 0.041359],
SELECT AVG(UserID) FROM hits;
[0.283332, 0.032512, 0.031995, 0.032098, 0.031732, 0.031943, 0.031992, 0.032305, 0.031731, 0.031839],
SELECT COUNT(DISTINCT UserID) FROM hits;
[0.457098, 0.344052, 0.343285, 0.344150, 0.348119, 0.337637, 0.340816, 0.338742, 0.338636, 0.342720],
SELECT COUNT(DISTINCT SearchPhrase) FROM hits;
[0.862271, 0.645672, 0.646106, 0.648641, 0.644757, 0.640993, 0.649696, 0.642852, 0.648390, 0.649156],
SELECT MIN(EventDate), MAX(EventDate) FROM hits;
[0.089584, 0.030858, 0.028055, 0.027979, 0.028889, 0.028454, 0.028498, 0.028578, 0.028407, 0.027545],
SELECT AdvEngineID, COUNT(*) FROM hits WHERE AdvEngineID <> 0 GROUP BY AdvEngineID ORDER BY COUNT(*) DESC;
[0.086659, 0.025216, 0.025591, 0.027003, 0.027145, 0.025188, 0.027686, 0.028050, 0.025356, 0.024804],
SELECT RegionID, COUNT(DISTINCT UserID) AS u FROM hits GROUP BY RegionID ORDER BY u DESC LIMIT 10;
[0.663055, 0.511075, 0.496364, 0.492999, 0.500250, 0.490088, 0.503038, 0.498859, 0.501576, 0.506489],
SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c, AVG(ResolutionWidth), COUNT(DISTINCT UserID) FROM hits GROUP BY RegionID ORDER BY c DESC LIMIT 10;
[0.744321, 0.536469, 0.538781, 0.536948, 0.533051, 0.544037, 0.533225, 0.537343, 0.537739, 0.541745],
SELECT MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhoneModel ORDER BY u DESC LIMIT 10;
[0.434644, 0.196753, 0.188048, 0.189915, 0.183505, 0.191594, 0.192489, 0.191243, 0.190163, 0.185209],
SELECT MobilePhone, MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhone, MobilePhoneModel ORDER BY u DESC LIMIT 10;
[0.385988, 0.165845, 0.161341, 0.159535, 0.162062, 0.157021, 0.155943, 0.158228, 0.159900, 0.160396],
SELECT SearchPhrase, COUNT(*) AS c FROM hits WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
[1.082393, 0.850055, 0.850640, 0.847859, 0.851002, 0.845397, 0.845867, 0.849816, 0.851234, 0.850801],
SELECT SearchPhrase, COUNT(DISTINCT UserID) AS u FROM hits WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY u DESC LIMIT 10;
[1.609629, 1.303916, 1.309553, 1.309645, 1.307854, 1.296369, 1.309271, 1.313427, 1.317088, 1.299861],
SELECT SearchEngineID, SearchPhrase, COUNT(*) AS c FROM hits WHERE SearchPhrase <> '' GROUP BY SearchEngineID, SearchPhrase ORDER BY c DESC LIMIT 10;
[1.135177, 0.936678, 0.925734, 0.923465, 0.918942, 0.919308, 0.915883, 0.923629, 0.914548, 0.917202],
SELECT UserID, COUNT(*) FROM hits GROUP BY UserID ORDER BY COUNT(*) DESC LIMIT 10;
[0.873891, 0.773152, 0.778891, 0.769694, 0.770811, 0.772402, 0.771161, 0.770129, 0.776566, 0.772827],
SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
[3.311647, 3.013585, 3.053314, 3.027522, 3.050140, 3.061314, 3.064766, 3.061211, 3.038234, 3.076854],
SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, SearchPhrase LIMIT 10;
[1.840432, 1.590880, 1.569952, 1.559939, 1.553125, 1.560262, 1.535251, 1.520765, 1.561211, 1.530577],
SELECT UserID, extract(minute FROM EventTime) AS m, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, m, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
[4.817216, 4.483809, 4.507087, 4.510731, 4.514817, 4.517014, 4.511981, 4.530623, 4.515187, 4.509778],
SELECT UserID FROM hits WHERE UserID = 435090932899640449;
[0.121320, 0.006585, 0.006621, 0.006207, 0.005561, 0.005696, 0.005657, 0.006028, 0.005569, 0.005626],
SELECT COUNT(*) FROM hits WHERE URL LIKE '%google%';
[1.236447, 0.304571, 0.308297, 0.304297, 0.304736, 0.300946, 0.303319, 0.306606, 0.307211, 0.300812],
SELECT SearchPhrase, MIN(URL), COUNT(*) AS c FROM hits WHERE URL LIKE '%google%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
[1.439052, 0.375744, 0.377096, 0.375978, 0.376899, 0.377708, 0.379931, 0.378716, 0.373669, 0.378261],
SELECT SearchPhrase, MIN(URL), MIN(Title), COUNT(*) AS c, COUNT(DISTINCT UserID) FROM hits WHERE Title LIKE '%Google%' AND URL NOT LIKE '%.google.%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
[2.081930, 0.695606, 0.687592, 0.683988, 0.696888, 0.688566, 0.686913, 0.687388, 0.689495, 0.692413],
SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;
[, , , , , , , , , ],
SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY EventTime LIMIT 10;
[6.286131, 0.028502, 0.028054, 0.027637, 0.027284, 0.028093, 0.027985, 0.027617, 0.027763, 0.027409],
SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY SearchPhrase LIMIT 10;
[0.347750, 0.129036, 0.133238, 0.131119, 0.132262, 0.137220, 0.130487, 0.137526, 0.136009, 0.136282],
SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY EventTime, SearchPhrase LIMIT 10;
[0.170214, 0.057899, 0.028900, 0.028934, 0.028449, 0.028648, 0.028227, 0.028095, 0.028324, 0.027837],
SELECT CounterID, AVG(length(URL)) AS l, COUNT(*) AS c FROM hits WHERE URL <> '' GROUP BY CounterID HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
[1.239418, 0.305298, 0.312231, 0.310203, 0.304119, 0.312433, 0.309523, 0.310116, 0.307490, 0.309860],
SELECT REGEXP_REPLACE(Referer, '^https?://(?:www.)?([^/]+)/.*$', '$1') AS k, AVG(length(Referer)) AS l, COUNT(*) AS c, MIN(Referer) FROM hits WHERE Referer <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
[8.793339, 8.653600, 8.681540, 8.742904, 8.744620, 8.781341, 8.801848, 8.819547, 8.823820, 8.853989],
SELECT SUM(ResolutionWidth), SUM(ResolutionWidth + 1), SUM(ResolutionWidth + 2), SUM(ResolutionWidth + 3), SUM(ResolutionWidth + 4), SUM(ResolutionWidth + 5), SUM(ResolutionWidth + 6), SUM(ResolutionWidth + 7), SUM(ResolutionWidth + 8), SUM(ResolutionWidth + 9), SUM(ResolutionWidth + 10), SUM(ResolutionWidth + 11), SUM(ResolutionWidth + 12), SUM(ResolutionWidth + 13), SUM(ResolutionWidth + 14), SUM(ResolutionWidth + 15), SUM(ResolutionWidth + 16), SUM(ResolutionWidth + 17), SUM(ResolutionWidth + 18), SUM(ResolutionWidth + 19), SUM(ResolutionWidth + 20), SUM(ResolutionWidth + 21), SUM(ResolutionWidth + 22), SUM(ResolutionWidth + 23), SUM(ResolutionWidth + 24), SUM(ResolutionWidth + 25), SUM(ResolutionWidth + 26), SUM(ResolutionWidth + 27), SUM(ResolutionWidth + 28), SUM(ResolutionWidth + 29), SUM(ResolutionWidth + 30), SUM(ResolutionWidth + 31), SUM(ResolutionWidth + 32), SUM(ResolutionWidth + 33), SUM(ResolutionWidth + 34), SUM(ResolutionWidth + 35), SUM(ResolutionWidth + 36), SUM(ResolutionWidth + 37), SUM(ResolutionWidth + 38), SUM(ResolutionWidth + 39), SUM(ResolutionWidth + 40), SUM(ResolutionWidth + 41), SUM(ResolutionWidth + 42), SUM(ResolutionWidth + 43), SUM(ResolutionWidth + 44), SUM(ResolutionWidth + 45), SUM(ResolutionWidth + 46), SUM(ResolutionWidth + 47), SUM(ResolutionWidth + 48), SUM(ResolutionWidth + 49), SUM(ResolutionWidth + 50), SUM(ResolutionWidth + 51), SUM(ResolutionWidth + 52), SUM(ResolutionWidth + 53), SUM(ResolutionWidth + 54), SUM(ResolutionWidth + 55), SUM(ResolutionWidth + 56), SUM(ResolutionWidth + 57), SUM(ResolutionWidth + 58), SUM(ResolutionWidth + 59), SUM(ResolutionWidth + 60), SUM(ResolutionWidth + 61), SUM(ResolutionWidth + 62), SUM(ResolutionWidth + 63), SUM(ResolutionWidth + 64), SUM(ResolutionWidth + 65), SUM(ResolutionWidth + 66), SUM(ResolutionWidth + 67), SUM(ResolutionWidth + 68), SUM(ResolutionWidth + 69), SUM(ResolutionWidth + 70), SUM(ResolutionWidth + 71), SUM(ResolutionWidth + 72), SUM(ResolutionWidth + 73), SUM(ResolutionWidth + 74), SUM(ResolutionWidth + 75), SUM(ResolutionWidth + 76), SUM(ResolutionWidth + 77), SUM(ResolutionWidth + 78), SUM(ResolutionWidth + 79), SUM(ResolutionWidth + 80), SUM(ResolutionWidth + 81), SUM(ResolutionWidth + 82), SUM(ResolutionWidth + 83), SUM(ResolutionWidth + 84), SUM(ResolutionWidth + 85), SUM(ResolutionWidth + 86), SUM(ResolutionWidth + 87), SUM(ResolutionWidth + 88), SUM(ResolutionWidth + 89) FROM hits;
[0.230037, 0.107964, 0.106887, 0.107225, 0.105960, 0.107189, 0.109013, 0.106457, 0.107579, 0.107917],
SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;
[0.830755, 0.419906, 0.417990, 0.421011, 0.418664, 0.421211, 0.419943, 0.416985, 0.418651, 0.418258],
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
[1.846663, 1.243098, 1.227840, 1.213282, 1.206771, 1.224928, 1.201760, 1.217043, 1.212691, 1.225716],
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
[8.301313, 7.663124, 7.691085, 7.698990, 7.686395, 7.663507, 7.704894, 7.656293, 7.665789, 7.679501],
SELECT URL, COUNT(*) AS c FROM hits GROUP BY URL ORDER BY c DESC LIMIT 10;
[4.893191, 4.295481, 4.245025, 4.308801, 4.292448, 4.333450, 4.311795, 4.313715, 4.331196, 4.322769],
SELECT 1, URL, COUNT(*) AS c FROM hits GROUP BY 1, URL ORDER BY c DESC LIMIT 10;
[4.967232, 4.299791, 4.336239, 4.344043, 4.303286, 4.317500, 4.325992, 4.310504, 4.350881, 4.371835],
SELECT ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3, COUNT(*) AS c FROM hits GROUP BY ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3 ORDER BY c DESC LIMIT 10;
[0.846336, 0.695227, 0.692435, 0.687485, 0.686108, 0.686078, 0.682114, 0.682460, 0.676762, 0.674969],
SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND URL <> '' GROUP BY URL ORDER BY PageViews DESC LIMIT 10;
[0.154173, 0.163062, 0.143059, 0.152884, 0.168623, 0.112146, 0.171182, 0.165078, 0.163111, 0.150808],
SELECT Title, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND Title <> '' GROUP BY Title ORDER BY PageViews DESC LIMIT 10;
[0.083893, 0.039210, 0.035657, 0.026847, 0.049169, 0.039447, 0.028548, 0.031388, 0.026745, 0.025150],
SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND IsLink <> 0 AND IsDownload = 0 GROUP BY URL ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
[0.086246, 0.017168, 0.021550, 0.025425, 0.024406, 0.020337, 0.018736, 0.019656, 0.020272, 0.021543],
SELECT TraficSourceID, SearchEngineID, AdvEngineID, CASE WHEN (SearchEngineID = 0 AND AdvEngineID = 0) THEN Referer ELSE '' END AS Src, URL AS Dst, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 GROUP BY TraficSourceID, SearchEngineID, AdvEngineID, Src, Dst ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
[0.213684, 0.148415, 0.145614, 0.147505, 0.138993, 0.147749, 0.163328, 0.150743, 0.138363, 0.136700],
SELECT URLHash, EventDate, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND TraficSourceID IN (-1, 6) AND RefererHash = 3594120000172545465 GROUP BY URLHash, EventDate ORDER BY PageViews DESC LIMIT 10 OFFSET 100;
[0.097148, 0.020580, 0.023213, 0.039411, 0.018678, 0.019873, 0.019078, 0.018894, 0.018677, 0.020778],
SELECT WindowClientWidth, WindowClientHeight, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND DontCountHits = 0 AND URLHash = 2868770270353813622 GROUP BY WindowClientWidth, WindowClientHeight ORDER BY PageViews DESC LIMIT 10 OFFSET 10000;
[0.074079, 0.015104, 0.013426, 0.013551, 0.013460, 0.015097, 0.014503, 0.013239, 0.012883, 0.012397],
SELECT DATE_TRUNC('minute', EventTime) AS M, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-14' AND EventDate <= '2013-07-15' AND IsRefresh = 0 AND DontCountHits = 0 GROUP BY DATE_TRUNC('minute', EventTime) ORDER BY DATE_TRUNC('minute', EventTime) LIMIT 10 OFFSET 1000;
[0.073441, 0.013318, 0.012371, 0.013009, 0.012612, 0.013861, 0.012465, 0.012504, 0.012195, 0.012430],

Release+PGO (in CSV)

1,1,0.038473
1,2,0.004729
1,3,0.004205
1,4,0.003789
1,5,0.003824
1,6,0.003634
1,7,0.003714
1,8,0.003833
1,9,0.003743
1,10,0.003591
2,1,0.110648
2,2,0.019625
2,3,0.019340
2,4,0.019343
2,5,0.019324
2,6,0.019241
2,7,0.018917
2,8,0.019111
2,9,0.019471
2,10,0.019683
3,1,0.164843
3,2,0.038889
3,3,0.040461
3,4,0.038267
3,5,0.037215
3,6,0.038489
3,7,0.037921
3,8,0.038279
3,9,0.037947
3,10,0.038058
4,1,0.276667
4,2,0.032309
4,3,0.032277
4,4,0.032470
4,5,0.031490
4,6,0.030651
4,7,0.031341
4,8,0.031773
4,9,0.031341
4,10,0.031326
5,1,0.459781
5,2,0.344554
5,3,0.341727
5,4,0.336053
5,5,0.342010
5,6,0.340279
5,7,0.340653
5,8,0.336576
5,9,0.339449
5,10,0.337806
6,1,0.846991
6,2,0.639814
6,3,0.635260
6,4,0.637122
6,5,0.638429
6,6,0.635046
6,7,0.635879
6,8,0.643035
6,9,0.641823
6,10,0.638693
7,1,0.079533
7,2,0.025584
7,3,0.024765
7,4,0.024260
7,5,0.024965
7,6,0.023873
7,7,0.025585
7,8,0.024978
7,9,0.024549
7,10,0.023756
8,1,0.085947
8,2,0.023049
8,3,0.022605
8,4,0.025659
8,5,0.022482
8,6,0.024271
8,7,0.021959
8,8,0.023055
8,9,0.022950
8,10,0.023261
9,1,0.698269
9,2,0.553377
9,3,0.551302
9,4,0.543125
9,5,0.544068
9,6,0.534507
9,7,0.538779
9,8,0.538554
9,9,0.543078
9,10,0.546371
10,1,0.778123
10,2,0.580840
10,3,0.611641
10,4,0.580113
10,5,0.571279
10,6,0.586745
10,7,0.585737
10,8,0.583344
10,9,0.580148
10,10,0.569313
11,1,0.423816
11,2,0.181981
11,3,0.174780
11,4,0.176388
11,5,0.181801
11,6,0.182250
11,7,0.186440
11,8,0.180184
11,9,0.183110
11,10,0.180507
12,1,0.378167
12,2,0.145368
12,3,0.141489
12,4,0.140038
12,5,0.136983
12,6,0.142860
12,7,0.150439
12,8,0.145221
12,9,0.142901
12,10,0.150497
13,1,1.077430
13,2,0.839859
13,3,0.833137
13,4,0.835761
13,5,0.834590
13,6,0.830919
13,7,0.834455
13,8,0.829600
13,9,0.827712
13,10,0.830738
14,1,1.601784
14,2,1.292304
14,3,1.281528
14,4,1.291294
14,5,1.286762
14,6,1.286276
14,7,1.280571
14,8,1.304211
14,9,1.296882
14,10,1.289686
15,1,1.105025
15,2,0.892642
15,3,0.888812
15,4,0.891778
15,5,0.886325
15,6,0.885210
15,7,0.880967
15,8,0.883912
15,9,0.888384
15,10,0.885082
16,1,0.867570
16,2,0.771963
16,3,0.769253
16,4,0.768777
16,5,0.767923
16,6,0.770266
16,7,0.768517
16,8,0.769361
16,9,0.762851
16,10,0.769385
17,1,3.280156
17,2,2.941162
17,3,2.957423
17,4,2.983199
17,5,2.968931
17,6,3.004662
17,7,2.973054
17,8,3.008871
17,9,2.983001
17,10,3.002069
18,1,1.772093
18,2,1.513527
18,3,1.522954
18,4,1.463082
18,5,1.481337
18,6,1.503999
18,7,1.504374
18,8,1.474470
18,9,1.499723
18,10,1.517451
19,1,4.640493
19,2,4.311797
19,3,4.299701
19,4,4.315831
19,5,4.331983
19,6,4.329365
19,7,4.330275
19,8,4.333581
19,9,4.320429
19,10,4.345714
20,1,0.106986
20,2,0.006041
20,3,0.005421
20,4,0.005228
20,5,0.005111
20,6,0.005337
20,7,0.004882
20,8,0.004961
20,9,0.004806
20,10,0.004518
21,1,1.232610
21,2,0.276353
21,3,0.271419
21,4,0.275520
21,5,0.275726
21,6,0.273157
21,7,0.274997
21,8,0.276559
21,9,0.272923
21,10,0.272604
22,1,1.433694
22,2,0.337009
22,3,0.342013
22,4,0.341850
22,5,0.339347
22,6,0.339644
22,7,0.339665
22,8,0.337609
22,9,0.343509
22,10,0.339634
23,1,2.074988
23,2,0.638918
23,3,0.622498
23,4,0.620184
23,5,0.619740
23,6,0.623884
23,7,0.619748
23,8,0.620217
23,9,0.621458
23,10,0.623522
24,1,
24,2,
24,3,
24,4,
24,5,
24,6,
24,7,
24,8,
24,9,
24,10,
25,1,
25,2,
25,3,
25,4,
25,5,
25,6,
25,7,
25,8,
25,9,
25,10,
26,1,2.542791
26,2,0.115896
26,3,0.125329
26,4,0.118264
26,5,0.128411
26,6,0.124221
26,7,0.132771
26,8,0.122471
26,9,0.121708
26,10,0.127308
27,1,0.163920
27,2,0.028251
27,3,0.028017
27,4,0.028813
27,5,0.028660
27,6,0.027169
27,7,0.027481
27,8,0.027973
27,9,0.028740
27,10,0.027506
28,1,1.234687
28,2,0.285637
28,3,0.284847
28,4,0.286920
28,5,0.292022
28,6,0.288842
28,7,0.287844
28,8,0.284692
28,9,0.288386
28,10,0.289375
29,1,6.791796
29,2,6.618030
29,3,6.630912
29,4,6.640657
29,5,6.671725
29,6,6.703830
29,7,6.708012
29,8,6.713170
29,9,6.730410
29,10,6.761823
30,1,0.211284
30,2,0.094368
30,3,0.092567
30,4,0.092359
30,5,0.106746
30,6,0.092231
30,7,0.093048
30,8,0.091069
30,9,0.092259
30,10,0.092288
31,1,0.789277
31,2,0.396376
31,3,0.395151
31,4,0.393379
31,5,0.396761
31,6,0.394368
31,7,0.396788
31,8,0.398694
31,9,0.394435
31,10,0.395505
32,1,1.811681
32,2,1.224303
32,3,1.208930
32,4,1.212025
32,5,1.203722
32,6,1.187711
32,7,1.192606
32,8,1.234047
32,9,1.186057
32,10,1.217266
33,1,8.258915
33,2,7.584486
33,3,7.592673
33,4,7.577467
33,5,7.560354
33,6,7.552612
33,7,7.559998
33,8,7.560091
33,9,7.547348
33,10,7.539388
34,1,4.889545
34,2,4.300101
34,3,4.299457
34,4,4.314735
34,5,4.298566
34,6,4.316172
34,7,4.299711
34,8,4.315894
34,9,4.316462
34,10,4.318467
35,1,4.982010
35,2,4.276110
35,3,4.320005
35,4,4.317239
35,5,4.316311
35,6,4.328155
35,7,4.320330
35,8,4.322064
35,9,4.334668
35,10,4.332617
36,1,0.844651
36,2,0.699600
36,3,0.696139
36,4,0.693908
36,5,0.687945
36,6,0.681698
36,7,0.684062
36,8,0.680198
36,9,0.673985
36,10,0.682590
37,1,0.158659
37,2,0.146861
37,3,0.136923
37,4,0.161581
37,5,0.156649
37,6,0.166650
37,7,0.161699
37,8,0.158327
37,9,0.130813
37,10,0.166426
38,1,0.077555
38,2,0.022606
38,3,0.029737
38,4,0.025831
38,5,0.024918
38,6,0.033080
38,7,0.023835
38,8,0.026329
38,9,0.046340
38,10,0.043926
39,1,0.080027
39,2,0.017616
39,3,0.020976
39,4,0.017453
39,5,0.022568
39,6,0.017542
39,7,0.021956
39,8,0.020425
39,9,0.025127
39,10,0.020867
40,1,0.204700
40,2,0.136538
40,3,0.130707
40,4,0.132142
40,5,0.129380
40,6,0.139656
40,7,0.127983
40,8,0.146122
40,9,0.146283
40,10,0.130522
41,1,0.081241
41,2,0.017045
41,3,0.017335
41,4,0.017093
41,5,0.017831
41,6,0.017041
41,7,0.016058
41,8,0.016164
41,9,0.017644
41,10,0.016570
42,1,0.071030
42,2,0.012418
42,3,0.012161
42,4,0.012056
42,5,0.012063
42,6,0.012089
42,7,0.011760
42,8,0.012952
42,9,0.012292
42,10,0.011842
43,1,0.066559
43,2,0.012814
43,3,0.012662
43,4,0.011659
43,5,0.011597
43,6,0.011386
43,7,0.011690
43,8,0.011489
43,9,0.010940
43,10,0.012121

Release+PGO (with SQL queries)

SELECT COUNT(*) FROM hits;
[0.038473, 0.004729, 0.004205, 0.003789, 0.003824, 0.003634, 0.003714, 0.003833, 0.003743, 0.003591],
SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;
[0.110648, 0.019625, 0.019340, 0.019343, 0.019324, 0.019241, 0.018917, 0.019111, 0.019471, 0.019683],
SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;
[0.164843, 0.038889, 0.040461, 0.038267, 0.037215, 0.038489, 0.037921, 0.038279, 0.037947, 0.038058],
SELECT AVG(UserID) FROM hits;
[0.276667, 0.032309, 0.032277, 0.032470, 0.031490, 0.030651, 0.031341, 0.031773, 0.031341, 0.031326],
SELECT COUNT(DISTINCT UserID) FROM hits;
[0.459781, 0.344554, 0.341727, 0.336053, 0.342010, 0.340279, 0.340653, 0.336576, 0.339449, 0.337806],
SELECT COUNT(DISTINCT SearchPhrase) FROM hits;
[0.846991, 0.639814, 0.635260, 0.637122, 0.638429, 0.635046, 0.635879, 0.643035, 0.641823, 0.638693],
SELECT MIN(EventDate), MAX(EventDate) FROM hits;
[0.079533, 0.025584, 0.024765, 0.024260, 0.024965, 0.023873, 0.025585, 0.024978, 0.024549, 0.023756],
SELECT AdvEngineID, COUNT(*) FROM hits WHERE AdvEngineID <> 0 GROUP BY AdvEngineID ORDER BY COUNT(*) DESC;
[0.085947, 0.023049, 0.022605, 0.025659, 0.022482, 0.024271, 0.021959, 0.023055, 0.022950, 0.023261],
SELECT RegionID, COUNT(DISTINCT UserID) AS u FROM hits GROUP BY RegionID ORDER BY u DESC LIMIT 10;
[0.698269, 0.553377, 0.551302, 0.543125, 0.544068, 0.534507, 0.538779, 0.538554, 0.543078, 0.546371],
SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c, AVG(ResolutionWidth), COUNT(DISTINCT UserID) FROM hits GROUP BY RegionID ORDER BY c DESC LIMIT 10;
[0.778123, 0.580840, 0.611641, 0.580113, 0.571279, 0.586745, 0.585737, 0.583344, 0.580148, 0.569313],
SELECT MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhoneModel ORDER BY u DESC LIMIT 10;
[0.423816, 0.181981, 0.174780, 0.176388, 0.181801, 0.182250, 0.186440, 0.180184, 0.183110, 0.180507],
SELECT MobilePhone, MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhone, MobilePhoneModel ORDER BY u DESC LIMIT 10;
[0.378167, 0.145368, 0.141489, 0.140038, 0.136983, 0.142860, 0.150439, 0.145221, 0.142901, 0.150497],
SELECT SearchPhrase, COUNT(*) AS c FROM hits WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
[1.077430, 0.839859, 0.833137, 0.835761, 0.834590, 0.830919, 0.834455, 0.829600, 0.827712, 0.830738],
SELECT SearchPhrase, COUNT(DISTINCT UserID) AS u FROM hits WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY u DESC LIMIT 10;
[1.601784, 1.292304, 1.281528, 1.291294, 1.286762, 1.286276, 1.280571, 1.304211, 1.296882, 1.289686],
SELECT SearchEngineID, SearchPhrase, COUNT(*) AS c FROM hits WHERE SearchPhrase <> '' GROUP BY SearchEngineID, SearchPhrase ORDER BY c DESC LIMIT 10;
[1.105025, 0.892642, 0.888812, 0.891778, 0.886325, 0.885210, 0.880967, 0.883912, 0.888384, 0.885082],
SELECT UserID, COUNT(*) FROM hits GROUP BY UserID ORDER BY COUNT(*) DESC LIMIT 10;
[0.867570, 0.771963, 0.769253, 0.768777, 0.767923, 0.770266, 0.768517, 0.769361, 0.762851, 0.769385],
SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
[3.280156, 2.941162, 2.957423, 2.983199, 2.968931, 3.004662, 2.973054, 3.008871, 2.983001, 3.002069],
SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, SearchPhrase LIMIT 10;
[1.772093, 1.513527, 1.522954, 1.463082, 1.481337, 1.503999, 1.504374, 1.474470, 1.499723, 1.517451],
SELECT UserID, extract(minute FROM EventTime) AS m, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, m, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
[4.640493, 4.311797, 4.299701, 4.315831, 4.331983, 4.329365, 4.330275, 4.333581, 4.320429, 4.345714],
SELECT UserID FROM hits WHERE UserID = 435090932899640449;
[0.106986, 0.006041, 0.005421, 0.005228, 0.005111, 0.005337, 0.004882, 0.004961, 0.004806, 0.004518],
SELECT COUNT(*) FROM hits WHERE URL LIKE '%google%';
[1.232610, 0.276353, 0.271419, 0.275520, 0.275726, 0.273157, 0.274997, 0.276559, 0.272923, 0.272604],
SELECT SearchPhrase, MIN(URL), COUNT(*) AS c FROM hits WHERE URL LIKE '%google%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
[1.433694, 0.337009, 0.342013, 0.341850, 0.339347, 0.339644, 0.339665, 0.337609, 0.343509, 0.339634],
SELECT SearchPhrase, MIN(URL), MIN(Title), COUNT(*) AS c, COUNT(DISTINCT UserID) FROM hits WHERE Title LIKE '%Google%' AND URL NOT LIKE '%.google.%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
[2.074988, 0.638918, 0.622498, 0.620184, 0.619740, 0.623884, 0.619748, 0.620217, 0.621458, 0.623522],
SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;
[, , , , , , , , , ],
SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY EventTime LIMIT 10;
[, , , , , , , , , ],
SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY SearchPhrase LIMIT 10;
[2.542791, 0.115896, 0.125329, 0.118264, 0.128411, 0.124221, 0.132771, 0.122471, 0.121708, 0.127308],
SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY EventTime, SearchPhrase LIMIT 10;
[0.163920, 0.028251, 0.028017, 0.028813, 0.028660, 0.027169, 0.027481, 0.027973, 0.028740, 0.027506],
SELECT CounterID, AVG(length(URL)) AS l, COUNT(*) AS c FROM hits WHERE URL <> '' GROUP BY CounterID HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
[1.234687, 0.285637, 0.284847, 0.286920, 0.292022, 0.288842, 0.287844, 0.284692, 0.288386, 0.289375],
SELECT REGEXP_REPLACE(Referer, '^https?://(?:www.)?([^/]+)/.*$', '$1') AS k, AVG(length(Referer)) AS l, COUNT(*) AS c, MIN(Referer) FROM hits WHERE Referer <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
[6.791796, 6.618030, 6.630912, 6.640657, 6.671725, 6.703830, 6.708012, 6.713170, 6.730410, 6.761823],
SELECT SUM(ResolutionWidth), SUM(ResolutionWidth + 1), SUM(ResolutionWidth + 2), SUM(ResolutionWidth + 3), SUM(ResolutionWidth + 4), SUM(ResolutionWidth + 5), SUM(ResolutionWidth + 6), SUM(ResolutionWidth + 7), SUM(ResolutionWidth + 8), SUM(ResolutionWidth + 9), SUM(ResolutionWidth + 10), SUM(ResolutionWidth + 11), SUM(ResolutionWidth + 12), SUM(ResolutionWidth + 13), SUM(ResolutionWidth + 14), SUM(ResolutionWidth + 15), SUM(ResolutionWidth + 16), SUM(ResolutionWidth + 17), SUM(ResolutionWidth + 18), SUM(ResolutionWidth + 19), SUM(ResolutionWidth + 20), SUM(ResolutionWidth + 21), SUM(ResolutionWidth + 22), SUM(ResolutionWidth + 23), SUM(ResolutionWidth + 24), SUM(ResolutionWidth + 25), SUM(ResolutionWidth + 26), SUM(ResolutionWidth + 27), SUM(ResolutionWidth + 28), SUM(ResolutionWidth + 29), SUM(ResolutionWidth + 30), SUM(ResolutionWidth + 31), SUM(ResolutionWidth + 32), SUM(ResolutionWidth + 33), SUM(ResolutionWidth + 34), SUM(ResolutionWidth + 35), SUM(ResolutionWidth + 36), SUM(ResolutionWidth + 37), SUM(ResolutionWidth + 38), SUM(ResolutionWidth + 39), SUM(ResolutionWidth + 40), SUM(ResolutionWidth + 41), SUM(ResolutionWidth + 42), SUM(ResolutionWidth + 43), SUM(ResolutionWidth + 44), SUM(ResolutionWidth + 45), SUM(ResolutionWidth + 46), SUM(ResolutionWidth + 47), SUM(ResolutionWidth + 48), SUM(ResolutionWidth + 49), SUM(ResolutionWidth + 50), SUM(ResolutionWidth + 51), SUM(ResolutionWidth + 52), SUM(ResolutionWidth + 53), SUM(ResolutionWidth + 54), SUM(ResolutionWidth + 55), SUM(ResolutionWidth + 56), SUM(ResolutionWidth + 57), SUM(ResolutionWidth + 58), SUM(ResolutionWidth + 59), SUM(ResolutionWidth + 60), SUM(ResolutionWidth + 61), SUM(ResolutionWidth + 62), SUM(ResolutionWidth + 63), SUM(ResolutionWidth + 64), SUM(ResolutionWidth + 65), SUM(ResolutionWidth + 66), SUM(ResolutionWidth + 67), SUM(ResolutionWidth + 68), SUM(ResolutionWidth + 69), SUM(ResolutionWidth + 70), SUM(ResolutionWidth + 71), SUM(ResolutionWidth + 72), SUM(ResolutionWidth + 73), SUM(ResolutionWidth + 74), SUM(ResolutionWidth + 75), SUM(ResolutionWidth + 76), SUM(ResolutionWidth + 77), SUM(ResolutionWidth + 78), SUM(ResolutionWidth + 79), SUM(ResolutionWidth + 80), SUM(ResolutionWidth + 81), SUM(ResolutionWidth + 82), SUM(ResolutionWidth + 83), SUM(ResolutionWidth + 84), SUM(ResolutionWidth + 85), SUM(ResolutionWidth + 86), SUM(ResolutionWidth + 87), SUM(ResolutionWidth + 88), SUM(ResolutionWidth + 89) FROM hits;
[0.211284, 0.094368, 0.092567, 0.092359, 0.106746, 0.092231, 0.093048, 0.091069, 0.092259, 0.092288],
SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;
[0.789277, 0.396376, 0.395151, 0.393379, 0.396761, 0.394368, 0.396788, 0.398694, 0.394435, 0.395505],
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
[1.811681, 1.224303, 1.208930, 1.212025, 1.203722, 1.187711, 1.192606, 1.234047, 1.186057, 1.217266],
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
[8.258915, 7.584486, 7.592673, 7.577467, 7.560354, 7.552612, 7.559998, 7.560091, 7.547348, 7.539388],
SELECT URL, COUNT(*) AS c FROM hits GROUP BY URL ORDER BY c DESC LIMIT 10;
[4.889545, 4.300101, 4.299457, 4.314735, 4.298566, 4.316172, 4.299711, 4.315894, 4.316462, 4.318467],
SELECT 1, URL, COUNT(*) AS c FROM hits GROUP BY 1, URL ORDER BY c DESC LIMIT 10;
[4.982010, 4.276110, 4.320005, 4.317239, 4.316311, 4.328155, 4.320330, 4.322064, 4.334668, 4.332617],
SELECT ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3, COUNT(*) AS c FROM hits GROUP BY ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3 ORDER BY c DESC LIMIT 10;
[0.844651, 0.699600, 0.696139, 0.693908, 0.687945, 0.681698, 0.684062, 0.680198, 0.673985, 0.682590],
SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND URL <> '' GROUP BY URL ORDER BY PageViews DESC LIMIT 10;
[0.158659, 0.146861, 0.136923, 0.161581, 0.156649, 0.166650, 0.161699, 0.158327, 0.130813, 0.166426],
SELECT Title, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND Title <> '' GROUP BY Title ORDER BY PageViews DESC LIMIT 10;
[0.077555, 0.022606, 0.029737, 0.025831, 0.024918, 0.033080, 0.023835, 0.026329, 0.046340, 0.043926],
SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND IsLink <> 0 AND IsDownload = 0 GROUP BY URL ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
[0.080027, 0.017616, 0.020976, 0.017453, 0.022568, 0.017542, 0.021956, 0.020425, 0.025127, 0.020867],
SELECT TraficSourceID, SearchEngineID, AdvEngineID, CASE WHEN (SearchEngineID = 0 AND AdvEngineID = 0) THEN Referer ELSE '' END AS Src, URL AS Dst, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 GROUP BY TraficSourceID, SearchEngineID, AdvEngineID, Src, Dst ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
[0.204700, 0.136538, 0.130707, 0.132142, 0.129380, 0.139656, 0.127983, 0.146122, 0.146283, 0.130522],
SELECT URLHash, EventDate, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND TraficSourceID IN (-1, 6) AND RefererHash = 3594120000172545465 GROUP BY URLHash, EventDate ORDER BY PageViews DESC LIMIT 10 OFFSET 100;
[0.081241, 0.017045, 0.017335, 0.017093, 0.017831, 0.017041, 0.016058, 0.016164, 0.017644, 0.016570],
SELECT WindowClientWidth, WindowClientHeight, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND DontCountHits = 0 AND URLHash = 2868770270353813622 GROUP BY WindowClientWidth, WindowClientHeight ORDER BY PageViews DESC LIMIT 10 OFFSET 10000;
[0.071030, 0.012418, 0.012161, 0.012056, 0.012063, 0.012089, 0.011760, 0.012952, 0.012292, 0.011842],
SELECT DATE_TRUNC('minute', EventTime) AS M, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-14' AND EventDate <= '2013-07-15' AND IsRefresh = 0 AND DontCountHits = 0 GROUP BY DATE_TRUNC('minute', EventTime) ORDER BY DATE_TRUNC('minute', EventTime) LIMIT 10 OFFSET 1000;
[0.066559, 0.012814, 0.012662, 0.011659, 0.011597, 0.011386, 0.011690, 0.011489, 0.010940, 0.012121],

Also, as an estimation, here are the results how slow Databend is in the Instrumentation mode for PGO:

Instrumentation (CSV)

1,1,0.081487
1,2,0.009557
1,3,0.007676
2,1,8.916766
2,2,8.040678
2,3,8.020130
3,1,1.236844
3,2,0.293331
3,3,0.290775
4,1,1.141073
4,2,0.202581
4,3,0.201807
5,1,14.880574
5,2,14.773950
5,3,14.768243
6,1,13.345794
6,2,12.477314
6,3,12.505075
7,1,1.131818
7,2,0.209595
7,3,0.215940
8,1,8.994215
8,2,8.075758
8,3,8.077138
9,1,20.980332
9,2,20.036677
9,3,19.775207
10,1,20.953144
10,2,19.756196
10,3,19.869630
11,1,11.023067
11,2,10.079988
11,3,10.047428
12,1,10.977730
12,2,10.057442
12,3,10.049909
13,1,13.096304
13,2,12.197684
13,3,12.197062
14,1,15.073473
14,2,14.106207
14,3,14.153492
15,1,13.856008
15,2,12.858341
15,3,12.845196
16,1,19.119850
16,2,19.096095
16,3,19.131377
17,1,33.458416
17,2,32.224921
17,3,32.108163
18,1,24.299676
18,2,24.195204
18,3,24.281687
19,1,51.407382
19,2,48.713793
19,3,47.651087
20,1,1.117563
20,2,0.042788
20,3,0.032563
21,1,14.166415
21,2,13.174442
21,3,13.195004
22,1,17.344272
22,2,16.484632
22,3,16.514159
23,1,27.490499
23,2,26.471080
23,3,26.482866
24,1,
24,2,
24,3,
25,1,2.926731
25,2,1.974064
25,3,2.020107
26,1,10.590981
26,2,9.685888
26,3,9.700754
27,1,2.913867
27,2,2.021020
27,3,2.017933
28,1,15.601711
28,2,14.691596
28,3,14.692898
29,1,911.444364
29,2,910.291038
29,3,910.402289
30,1,1.712135
30,2,0.774621
30,3,0.778358
31,1,13.564298
31,2,12.519589
31,3,12.530487
32,1,16.589501
32,2,15.445060
32,3,15.357461
33,1,70.798827
33,2,66.911168
33,3,68.433476
34,1,27.522646
34,2,26.645706
34,3,26.589688
35,1,26.401031
35,2,26.446602
35,3,26.499105
36,1,18.746242
36,2,17.749322
36,3,17.796184
37,1,1.317141
37,2,0.406870
37,3,0.401249
38,1,1.185829
38,2,0.254438
38,3,0.245584
39,1,1.153874
39,2,0.267570
39,3,0.263603
40,1,1.676257
40,2,0.757157
40,3,0.741417
41,1,1.221626
41,2,0.282767
41,3,0.290906
42,1,1.147368
42,2,0.234655
42,3,0.236766
43,1,1.135197
43,2,0.227937
43,3,0.222634

Instrumentation (with SQL queries)

[0.081487, 0.009557, 0.007676],
SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;
[8.916766, 8.040678, 8.020130],
SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;
[1.236844, 0.293331, 0.290775],
SELECT AVG(UserID) FROM hits;
[1.141073, 0.202581, 0.201807],
SELECT COUNT(DISTINCT UserID) FROM hits;
[14.880574, 14.773950, 14.768243],
SELECT COUNT(DISTINCT SearchPhrase) FROM hits;
[13.345794, 12.477314, 12.505075],
SELECT MIN(EventDate), MAX(EventDate) FROM hits;
[1.131818, 0.209595, 0.215940],
SELECT AdvEngineID, COUNT(*) FROM hits WHERE AdvEngineID <> 0 GROUP BY AdvEngineID ORDER BY COUNT(*) DESC;
[8.994215, 8.075758, 8.077138],
SELECT RegionID, COUNT(DISTINCT UserID) AS u FROM hits GROUP BY RegionID ORDER BY u DESC LIMIT 10;
[20.980332, 20.036677, 19.775207],
SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c, AVG(ResolutionWidth), COUNT(DISTINCT UserID) FROM hits GROUP BY RegionID ORDER BY c DESC LIMIT 10;
[20.953144, 19.756196, 19.869630],
SELECT MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhoneModel ORDER BY u DESC LIMIT 10;
[11.023067, 10.079988, 10.047428],
SELECT MobilePhone, MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhone, MobilePhoneModel ORDER BY u DESC LIMIT 10;
[10.977730, 10.057442, 10.049909],
SELECT SearchPhrase, COUNT(*) AS c FROM hits WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
[13.096304, 12.197684, 12.197062],
SELECT SearchPhrase, COUNT(DISTINCT UserID) AS u FROM hits WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY u DESC LIMIT 10;
[15.073473, 14.106207, 14.153492],
SELECT SearchEngineID, SearchPhrase, COUNT(*) AS c FROM hits WHERE SearchPhrase <> '' GROUP BY SearchEngineID, SearchPhrase ORDER BY c DESC LIMIT 10;
[13.856008, 12.858341, 12.845196],
SELECT UserID, COUNT(*) FROM hits GROUP BY UserID ORDER BY COUNT(*) DESC LIMIT 10;
[19.119850, 19.096095, 19.131377],
SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
[33.458416, 32.224921, 32.108163],
SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, SearchPhrase LIMIT 10;
[24.299676, 24.195204, 24.281687],
SELECT UserID, extract(minute FROM EventTime) AS m, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID, m, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
[51.407382, 48.713793, 47.651087],
SELECT UserID FROM hits WHERE UserID = 435090932899640449;
[1.117563, 0.042788, 0.032563],
SELECT COUNT(*) FROM hits WHERE URL LIKE '%google%';
[14.166415, 13.174442, 13.195004],
SELECT SearchPhrase, MIN(URL), COUNT(*) AS c FROM hits WHERE URL LIKE '%google%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
[17.344272, 16.484632, 16.514159],
SELECT SearchPhrase, MIN(URL), MIN(Title), COUNT(*) AS c, COUNT(DISTINCT UserID) FROM hits WHERE Title LIKE '%Google%' AND URL NOT LIKE '%.google.%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
[27.490499, 26.471080, 26.482866],
SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;
[, , ],
SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY EventTime LIMIT 10;
[2.926731, 1.974064, 2.020107],
SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY SearchPhrase LIMIT 10;
[10.590981, 9.685888, 9.700754],
SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY EventTime, SearchPhrase LIMIT 10;
[2.913867, 2.021020, 2.017933],
SELECT CounterID, AVG(length(URL)) AS l, COUNT(*) AS c FROM hits WHERE URL <> '' GROUP BY CounterID HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
[15.601711, 14.691596, 14.692898],
SELECT REGEXP_REPLACE(Referer, '^https?://(?:www.)?([^/]+)/.*$', '$1') AS k, AVG(length(Referer)) AS l, COUNT(*) AS c, MIN(Referer) FROM hits WHERE Referer <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
[911.444364, 910.291038, 910.402289],
SELECT SUM(ResolutionWidth), SUM(ResolutionWidth + 1), SUM(ResolutionWidth + 2), SUM(ResolutionWidth + 3), SUM(ResolutionWidth + 4), SUM(ResolutionWidth + 5), SUM(ResolutionWidth + 6), SUM(ResolutionWidth + 7), SUM(ResolutionWidth + 8), SUM(ResolutionWidth + 9), SUM(ResolutionWidth + 10), SUM(ResolutionWidth + 11), SUM(ResolutionWidth + 12), SUM(ResolutionWidth + 13), SUM(ResolutionWidth + 14), SUM(ResolutionWidth + 15), SUM(ResolutionWidth + 16), SUM(ResolutionWidth + 17), SUM(ResolutionWidth + 18), SUM(ResolutionWidth + 19), SUM(ResolutionWidth + 20), SUM(ResolutionWidth + 21), SUM(ResolutionWidth + 22), SUM(ResolutionWidth + 23), SUM(ResolutionWidth + 24), SUM(ResolutionWidth + 25), SUM(ResolutionWidth + 26), SUM(ResolutionWidth + 27), SUM(ResolutionWidth + 28), SUM(ResolutionWidth + 29), SUM(ResolutionWidth + 30), SUM(ResolutionWidth + 31), SUM(ResolutionWidth + 32), SUM(ResolutionWidth + 33), SUM(ResolutionWidth + 34), SUM(ResolutionWidth + 35), SUM(ResolutionWidth + 36), SUM(ResolutionWidth + 37), SUM(ResolutionWidth + 38), SUM(ResolutionWidth + 39), SUM(ResolutionWidth + 40), SUM(ResolutionWidth + 41), SUM(ResolutionWidth + 42), SUM(ResolutionWidth + 43), SUM(ResolutionWidth + 44), SUM(ResolutionWidth + 45), SUM(ResolutionWidth + 46), SUM(ResolutionWidth + 47), SUM(ResolutionWidth + 48), SUM(ResolutionWidth + 49), SUM(ResolutionWidth + 50), SUM(ResolutionWidth + 51), SUM(ResolutionWidth + 52), SUM(ResolutionWidth + 53), SUM(ResolutionWidth + 54), SUM(ResolutionWidth + 55), SUM(ResolutionWidth + 56), SUM(ResolutionWidth + 57), SUM(ResolutionWidth + 58), SUM(ResolutionWidth + 59), SUM(ResolutionWidth + 60), SUM(ResolutionWidth + 61), SUM(ResolutionWidth + 62), SUM(ResolutionWidth + 63), SUM(ResolutionWidth + 64), SUM(ResolutionWidth + 65), SUM(ResolutionWidth + 66), SUM(ResolutionWidth + 67), SUM(ResolutionWidth + 68), SUM(ResolutionWidth + 69), SUM(ResolutionWidth + 70), SUM(ResolutionWidth + 71), SUM(ResolutionWidth + 72), SUM(ResolutionWidth + 73), SUM(ResolutionWidth + 74), SUM(ResolutionWidth + 75), SUM(ResolutionWidth + 76), SUM(ResolutionWidth + 77), SUM(ResolutionWidth + 78), SUM(ResolutionWidth + 79), SUM(ResolutionWidth + 80), SUM(ResolutionWidth + 81), SUM(ResolutionWidth + 82), SUM(ResolutionWidth + 83), SUM(ResolutionWidth + 84), SUM(ResolutionWidth + 85), SUM(ResolutionWidth + 86), SUM(ResolutionWidth + 87), SUM(ResolutionWidth + 88), SUM(ResolutionWidth + 89) FROM hits;
[1.712135, 0.774621, 0.778358],
SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;
[13.564298, 12.519589, 12.530487],
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
[16.589501, 15.445060, 15.357461],
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM hits GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
[70.798827, 66.911168, 68.433476],
SELECT URL, COUNT(*) AS c FROM hits GROUP BY URL ORDER BY c DESC LIMIT 10;
[27.522646, 26.645706, 26.589688],
SELECT 1, URL, COUNT(*) AS c FROM hits GROUP BY 1, URL ORDER BY c DESC LIMIT 10;
[26.401031, 26.446602, 26.499105],
SELECT ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3, COUNT(*) AS c FROM hits GROUP BY ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3 ORDER BY c DESC LIMIT 10;
[18.746242, 17.749322, 17.796184],
SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND URL <> '' GROUP BY URL ORDER BY PageViews DESC LIMIT 10;
[1.317141, 0.406870, 0.401249],
SELECT Title, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND Title <> '' GROUP BY Title ORDER BY PageViews DESC LIMIT 10;
[1.185829, 0.254438, 0.245584],
SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND IsLink <> 0 AND IsDownload = 0 GROUP BY URL ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
[1.153874, 0.267570, 0.263603],
SELECT TraficSourceID, SearchEngineID, AdvEngineID, CASE WHEN (SearchEngineID = 0 AND AdvEngineID = 0) THEN Referer ELSE '' END AS Src, URL AS Dst, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 GROUP BY TraficSourceID, SearchEngineID, AdvEngineID, Src, Dst ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
[1.676257, 0.757157, 0.741417],
SELECT URLHash, EventDate, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND TraficSourceID IN (-1, 6) AND RefererHash = 3594120000172545465 GROUP BY URLHash, EventDate ORDER BY PageViews DESC LIMIT 10 OFFSET 100;
[1.221626, 0.282767, 0.290906],
SELECT WindowClientWidth, WindowClientHeight, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 AND DontCountHits = 0 AND URLHash = 2868770270353813622 GROUP BY WindowClientWidth, WindowClientHeight ORDER BY PageViews DESC LIMIT 10 OFFSET 10000;
[1.147368, 0.234655, 0.236766],
SELECT DATE_TRUNC('minute', EventTime) AS M, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-14' AND EventDate <= '2013-07-15' AND IsRefresh = 0 AND DontCountHits = 0 GROUP BY DATE_TRUNC('minute', EventTime) ORDER BY DATE_TRUNC('minute', EventTime) LIMIT 10 OFFSET 1000;
[1.135197, 0.227937, 0.222634],

[PsiACE]

@zamazan4ik Thank you very much for your testing. It's great to see that PGO is an effective way.

cc @soyeric128 Please help evaluate how we should incorporate it into the document.