partitioning_test_results_analyse.sql

-- 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
;