Created
April 18, 2023 21:48
Revisions
-
kmoppel created this gist
Apr 18, 2023 .There are no files selected for viewing
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,157 @@ -- more parts penalty /* test_name │ part_method │ avg_plan_time │ avg_plan_time_diff │ avg_plan_time_stddev_diff │ avg_exec_time │ avg_exec_time_diff │ avg_exec_time_stddev_diff ────────────────┼─────────────┼──────────────────────┼────────────────────┼───────────────────────────┼─────────────────────┼───────────────────────┼─────────────────────────── random_access │ hash │ 0.036001358353539424 │ 19.22491740688769 │ 32.181566052208396 │ 0.24820840462700047 │ -0.8716309424183029 │ 1.236449844148022 random_access │ range │ 0.03714072543338436 │ 20.41650568434875 │ 36.069893809198994 │ 0.25118521676332045 │ -1.2993096120041625 │ -0.8905487092585993 zipfian_access │ hash │ 0.03310657188586292 │ 18.83769228595254 │ 37.139484335097094 │ 0.04842279570874101 │ -0.1236185797740346 │ -0.0648544720406091 zipfian_access │ range │ 0.030341247036364927 │ 13.961726095155843 │ 38.035038463010075 │ 0.0484473166466542 │ -0.011157256904170687 │ -0.02093898759713735 (4 rows) avg_avg_plan_time_diff │ avg_avg_exec_time_diff ────────────────────────┼──────────────────────── 18.110210368086204 │ -0.5764290977751676 (1 row) */ -- select avg(avg_plan_time_diff) avg_avg_plan_time_diff, avg(avg_exec_time_diff) avg_avg_exec_time_diff from ( select test_name, part_method, avg(mean_plan_time) as avg_plan_time, avg(mean_plan_time_diff) as avg_plan_time_diff, avg(stddev_plan_time_diff) as avg_plan_time_stddev_diff, avg(mean_exec_time) as avg_exec_time, avg(mean_exec_time_pct_diff) as avg_exec_time_diff, avg(stddev_exec_time_pct_diff) as avg_exec_time_stddev_diff from ( select *, 100.0 * ((mean_plan_time - mean_plan_time_lag)/mean_plan_time_lag) as mean_plan_time_diff, 100.0 * ((stddev_plan_time - stddev_plan_time_lag)/stddev_plan_time_lag) as stddev_plan_time_diff, 100.0 * ((mean_exec_time - mean_exec_time_lag)/mean_exec_time_lag) as mean_exec_time_pct_diff, 100.0 * ((stddev_exec_time - stddev_exec_time_lag)/stddev_exec_time_lag) as stddev_exec_time_pct_diff from ( select *, lag(mean_plan_time) over (partition by hostname, test_name, part_method order by partitions) as mean_plan_time_lag, lag(stddev_plan_time) over (partition by hostname, test_name, part_method order by partitions) as stddev_plan_time_lag, lag(mean_exec_time) over (partition by hostname, test_name, part_method order by partitions) as mean_exec_time_lag, lag(stddev_exec_time) over (partition by hostname, test_name, part_method order by partitions) as stddev_exec_time_lag from ( select hostname, test_name, part_method, partitions, avg(mean_plan_time) mean_plan_time, avg(stddev_plan_time) stddev_plan_time, avg(mean_exec_time) mean_exec_time, avg(stddev_exec_time) stddev_exec_time from pgss_results_planning_test group by hostname, test_name, part_method, partitions order by hostname, test_name, partitions, part_method ) a order by hostname, test_name, part_method, partitions ) b order by hostname, test_name, part_method, partitions ) c group by 1, 2 order by 1, 2; --) d; -- 16 vs 4k /* partitions │ mean_plan_time_diff │ mean_exec_time_pct_diff ────────────┼─────────────────────┼───────────────────────── 0 │ 0 │ 0 16 │ 53.43018638819235 │ 3.6639453400388833 64 │ 57.88042404166534 │ 3.9819252831374468 256 │ 64.54215503828327 │ 1.2881763620609563 1024 │ 97.91689723613561 │ -0.7771150437654413 4096 │ 117.70347472600221 │ -3.273824081790126 (6 rows) test_name │ partitions │ mean_plan_time_diff │ mean_exec_time_pct_diff ──────────────┼────────────┼─────────────────────┼───────────────────────── random_access │ 0 │ 0 │ 0 random_access │ 16 │ 55.44703851197451 │ 3.934397651819213 random_access │ 64 │ 61.50810508596568 │ 4.397420346730956 random_access │ 256 │ 69.9729659072041 │ 2.456016088482998 random_access │ 1024 │ 110.34786130147236 │ -2.7503650929584222 random_access │ 4096 │ 132.9199381363199 │ -5.88530016493424 zipfian_access │ 0 │ 0 │ 0 zipfian_access │ 16 │ 51.41333426441019 │ 3.393493028258553 zipfian_access │ 64 │ 54.252742997365004 │ 3.566430219543938 zipfian_access │ 256 │ 59.11134416936245 │ 0.12033663563891436 zipfian_access │ 1024 │ 85.48593317079887 │ 1.19613500542754 zipfian_access │ 4096 │ 102.48701131568448 │ -0.6623479986460122 (12 rows) */ select test_name, partitions, avg(mean_plan_time_diff) mean_plan_time_diff, avg(mean_pct_diff) mean_exec_time_pct_diff /* avg(mean_plan_time_diff) as mean_plan_time_diff, avg(stddev_plan_time_diff) as stddev_plan_time_diff, avg(mean_pct_diff) as avg_mean_pct_diff, avg(stddev_pct_diff) as avg_stddev_pct_diff -- max(mean_plan_time) - min(mean_plan_time) mean_plan_time_delta_ms, avg(mean_plan_time) as mean_plan_time, avg(stddev_plan_time_diff) as stddev_plan_time_diff, */ from ( select *, 100.0 * ((mean_plan_time - mean_plan_time_lag)/mean_plan_time_lag) as mean_plan_time_diff, 100.0 * ((stddev_plan_time - stddev_plan_time_lag)/stddev_plan_time_lag) as stddev_plan_time_diff, 100.0 * ((mean_exec_time - mean_exec_time_lag)/mean_exec_time_lag) as mean_pct_diff, 100.0 * ((stddev_exec_time - stddev_exec_time_lag)/stddev_exec_time_lag) as stddev_pct_diff from ( select *, first_value(mean_plan_time) over (partition by hostname, test_name, part_method order by partitions) as mean_plan_time_lag, first_value(stddev_plan_time) over (partition by hostname, test_name, part_method order by partitions) as stddev_plan_time_lag, first_value(mean_exec_time) over (partition by hostname, test_name, part_method order by partitions) as mean_exec_time_lag, first_value(stddev_exec_time) over (partition by hostname, test_name, part_method order by partitions) as stddev_exec_time_lag from ( select hostname, test_name, part_method, partitions, avg(mean_plan_time) mean_plan_time, avg(stddev_plan_time) stddev_plan_time, avg(mean_exec_time) mean_exec_time, avg(stddev_exec_time) stddev_exec_time from pgss_results_planning_test where 1 = 1 -- partitions in (16, 1024) group by hostname, test_name, part_method, partitions order by hostname, test_name, partitions, part_method ) a order by hostname, test_name, part_method, partitions ) b order by hostname, test_name, part_method, partitions ) c group by 1, 2 order by 1, 2 ;