Skip to content

Instantly share code, notes, and snippets.

@kmoppel
Created April 18, 2023 21:48
Show Gist options
  • Save kmoppel/b6a93e0bdbaeb5a1cfe8a78327393de3 to your computer and use it in GitHub Desktop.
Save kmoppel/b6a93e0bdbaeb5a1cfe8a78327393de3 to your computer and use it in GitHub Desktop.
-- 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
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment