Skip to content

Instantly share code, notes, and snippets.

@kmoppel
Last active January 5, 2024 00:40
Show Gist options
  • Save kmoppel/3fe12db152fd38a0a98bd7de35bf7feb to your computer and use it in GitHub Desktop.
Save kmoppel/3fe12db152fd38a0a98bd7de35bf7feb to your computer and use it in GitHub Desktop.
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.5 (Ubuntu 15.5-1.pgdg22.04+1)
-- Dumped by pg_dump version 15.5 (Ubuntu 15.5-1.pgdg22.04+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: dataset_test_results; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.dataset_test_results (
created_on timestamp with time zone DEFAULT now() NOT NULL,
test_start_time timestamp with time zone NOT NULL,
dataset_name text NOT NULL,
test_script_name text NOT NULL,
test_id text NOT NULL,
test_id_num numeric,
test_value numeric NOT NULL,
test_value_info text,
test_value_2 numeric,
test_value_info_2 text
);
--
-- Data for Name: dataset_test_results; Type: TABLE DATA; Schema: public; Owner: -
--
COPY public.dataset_test_results (created_on, test_start_time, dataset_name, test_script_name, test_id, test_id_num, test_value, test_value_info, test_value_2, test_value_info_2) FROM stdin;
2024-01-02 15:25:48.614576+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression gzip 1 97535 duration millis 4016419354 compressed size bytes
2024-01-02 15:27:50.042046+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression gzip 3 121415 duration millis 3937296267 compressed size bytes
2024-01-02 15:31:04.840301+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression gzip 5 194788 duration millis 3627588529 compressed size bytes
2024-01-02 15:35:49.232105+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression gzip 7 284382 duration millis 3587497295 compressed size bytes
2024-01-02 15:44:22.335381+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression gzip 9 513093 duration millis 3559301983 compressed size bytes
2024-01-02 15:44:54.774936+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression lz4 1 32430 duration millis 5698165300 compressed size bytes
2024-01-02 15:46:34.484266+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression lz4 3 99699 duration millis 4747606889 compressed size bytes
2024-01-02 15:48:41.301071+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression lz4 5 126807 duration millis 4679417054 compressed size bytes
2024-01-02 15:51:24.054272+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression lz4 7 162743 duration millis 4658644831 compressed size bytes
2024-01-02 15:54:31.09317+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression lz4 9 187029 duration millis 4651087307 compressed size bytes
2024-01-02 16:05:48.840174+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression lz4 11 677737 duration millis 4639080529 compressed size bytes
2024-01-02 16:06:23.829843+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression zstd 1 34980 duration millis 2880284363 compressed size bytes
2024-01-02 16:07:49.471754+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression zstd 5 85633 duration millis 3301030733 compressed size bytes
2024-01-02 16:11:00.083064+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression zstd 9 190602 duration millis 3115607645 compressed size bytes
2024-01-02 16:21:54.697539+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression zstd 13 654604 duration millis 3095484409 compressed size bytes
2024-01-02 16:52:23.712959+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression zstd 17 1829006 duration millis 2469996885 compressed size bytes
2024-01-02 18:41:17.970138+02 2024-01-02 15:24:11.069067+02 osm_australia pg_dump_compression zstd 21 6534247 duration millis 2482830900 compressed size bytes
2024-01-02 18:42:39.87538+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression gzip 1 26479 duration millis 290006496 compressed size bytes
2024-01-02 18:43:05.191742+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression gzip 3 25306 duration millis 290000912 compressed size bytes
2024-01-02 18:43:51.922531+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression gzip 5 46720 duration millis 284811800 compressed size bytes
2024-01-02 18:44:39.376287+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression gzip 7 47443 duration millis 284592924 compressed size bytes
2024-01-02 18:45:39.140987+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression gzip 9 59755 duration millis 275712317 compressed size bytes
2024-01-02 18:46:04.816142+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression lz4 1 25665 duration millis 518782294 compressed size bytes
2024-01-02 18:46:36.211723+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression lz4 3 31386 duration millis 502045449 compressed size bytes
2024-01-02 18:47:16.525746+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression lz4 5 40304 duration millis 502027476 compressed size bytes
2024-01-02 18:48:02.675647+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression lz4 7 46141 duration millis 502027458 compressed size bytes
2024-01-02 18:48:50.770741+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression lz4 9 48085 duration millis 502026975 compressed size bytes
2024-01-02 19:01:42.623562+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression lz4 11 771843 duration millis 501922584 compressed size bytes
2024-01-02 19:02:08.151759+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression zstd 1 25519 duration millis 89815225 compressed size bytes
2024-01-02 19:02:33.63138+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression zstd 5 25469 duration millis 89381063 compressed size bytes
2024-01-02 19:03:03.059052+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression zstd 9 29417 duration millis 88446290 compressed size bytes
2024-01-02 19:03:31.868984+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression zstd 13 28800 duration millis 88091063 compressed size bytes
2024-01-02 19:05:59.781813+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression zstd 17 147903 duration millis 67791748 compressed size bytes
2024-01-02 20:40:36.734431+02 2024-01-02 18:42:13.385374+02 pgbench pg_dump_compression zstd 21 5676941 duration millis 79971670 compressed size bytes
2024-01-02 20:42:22.721786+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression gzip 1 7520 duration millis 290908402 compressed size bytes
2024-01-02 20:42:34.219421+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression gzip 3 11487 duration millis 271806090 compressed size bytes
2024-01-02 20:42:50.900107+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression gzip 5 16671 duration millis 248036421 compressed size bytes
2024-01-02 20:43:19.756395+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression gzip 7 28846 duration millis 242017140 compressed size bytes
2024-01-02 20:44:16.864421+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression gzip 9 57098 duration millis 240178497 compressed size bytes
2024-01-02 20:44:22.042975+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression lz4 1 5168 duration millis 409580817 compressed size bytes
2024-01-02 20:44:30.091509+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression lz4 3 8038 duration millis 328005685 compressed size bytes
2024-01-02 20:44:43.208191+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression lz4 5 13106 duration millis 315113996 compressed size bytes
2024-01-02 20:45:03.418251+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression lz4 7 20200 duration millis 311179491 compressed size bytes
2024-01-02 20:45:31.96155+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression lz4 9 28533 duration millis 310201357 compressed size bytes
2024-01-02 20:46:37.957034+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression lz4 11 65986 duration millis 304119239 compressed size bytes
2024-01-02 20:46:43.042266+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression zstd 1 5075 duration millis 250799372 compressed size bytes
2024-01-02 20:46:50.093903+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression zstd 5 7042 duration millis 246314604 compressed size bytes
2024-01-02 20:47:14.324278+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression zstd 9 24220 duration millis 223851928 compressed size bytes
2024-01-02 20:48:06.184444+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression zstd 13 51850 duration millis 215786761 compressed size bytes
2024-01-02 20:51:13.51083+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression zstd 17 187317 duration millis 183584535 compressed size bytes
2024-01-02 20:58:02.647826+02 2024-01-02 20:42:15.192421+02 postgrespro_demodb_big pg_dump_compression zstd 21 409126 duration millis 177674737 compressed size bytes
2024-01-02 21:01:42.723211+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression gzip 1 38001 duration millis 1468746967 compressed size bytes
2024-01-02 21:02:38.219834+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression gzip 3 55486 duration millis 1403554211 compressed size bytes
2024-01-02 21:04:00.664614+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression gzip 5 82435 duration millis 1285160997 compressed size bytes
2024-01-02 21:06:32.428407+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression gzip 7 151754 duration millis 1267176593 compressed size bytes
2024-01-02 21:11:36.588653+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression gzip 9 304150 duration millis 1262204601 compressed size bytes
2024-01-02 21:11:55.797134+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression lz4 1 19199 duration millis 2101222042 compressed size bytes
2024-01-02 21:12:32.868914+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression lz4 3 37062 duration millis 1650295512 compressed size bytes
2024-01-02 21:13:28.420753+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression lz4 5 55542 duration millis 1613506818 compressed size bytes
2024-01-02 21:14:52.884675+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression lz4 7 84454 duration millis 1605393228 compressed size bytes
2024-01-02 21:16:51.09946+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression lz4 9 118204 duration millis 1603130623 compressed size bytes
2024-01-02 21:22:28.645356+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression lz4 11 337536 duration millis 1588668779 compressed size bytes
2024-01-02 21:22:48.550534+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression zstd 1 19895 duration millis 1315117730 compressed size bytes
2024-01-02 21:23:21.954243+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression zstd 5 33394 duration millis 1209319659 compressed size bytes
2024-01-02 21:25:11.027781+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression zstd 9 109063 duration millis 1108481811 compressed size bytes
2024-01-02 21:28:56.22468+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression zstd 13 225187 duration millis 1085052466 compressed size bytes
2024-01-02 21:41:01.45357+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression zstd 17 725219 duration millis 961054474 compressed size bytes
2024-01-02 22:13:04.54631+02 2024-01-02 21:01:04.710168+02 imdb pg_dump_compression zstd 21 1923083 duration millis 912411820 compressed size bytes
2024-01-02 22:37:21.28959+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression gzip 1 225306 duration millis 4837694713 compressed size bytes
2024-01-02 22:41:21.613026+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression gzip 3 240301 duration millis 4586817856 compressed size bytes
2024-01-02 22:46:58.393831+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression gzip 5 336752 duration millis 4047635187 compressed size bytes
2024-01-02 22:56:37.691553+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression gzip 7 579277 duration millis 3936090155 compressed size bytes
2024-01-02 23:18:29.951031+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression gzip 9 1312241 duration millis 3823479674 compressed size bytes
2024-01-02 23:21:59.591602+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression lz4 1 209622 duration millis 6966666891 compressed size bytes
2024-01-02 23:25:38.902549+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression lz4 3 219290 duration millis 5806215416 compressed size bytes
2024-01-02 23:29:49.277642+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression lz4 5 250358 duration millis 5707448571 compressed size bytes
2024-01-02 23:36:31.015943+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression lz4 7 401721 duration millis 5676971361 compressed size bytes
2024-01-02 23:46:03.918455+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression lz4 9 572881 duration millis 5668307984 compressed size bytes
2024-01-03 00:30:10.383817+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression lz4 11 2646449 duration millis 5612941637 compressed size bytes
2024-01-03 00:33:42.298442+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression zstd 1 211895 duration millis 3647425535 compressed size bytes
2024-01-03 00:37:18.764988+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression zstd 5 216450 duration millis 3656125329 compressed size bytes
2024-01-03 00:43:31.479866+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression zstd 9 372695 duration millis 3009436485 compressed size bytes
2024-01-03 00:54:56.41054+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression zstd 13 684911 duration millis 2975878291 compressed size bytes
2024-01-03 01:56:49.718613+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression zstd 17 3713283 duration millis 2740906417 compressed size bytes
2024-01-03 07:05:49.999984+02 2024-01-02 22:33:35.951513+02 mouse_genome pg_dump_compression zstd 21 18540267 duration millis 2370356392 compressed size bytes
2024-01-03 13:06:22.354261+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression gzip 1 45742 duration millis 1633474132 compressed size bytes
2024-01-03 13:07:19.213582+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression gzip 3 56849 duration millis 1524390846 compressed size bytes
2024-01-03 13:08:46.190056+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression gzip 5 86967 duration millis 1391369315 compressed size bytes
2024-01-03 13:10:51.548008+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression gzip 7 125348 duration millis 1363417660 compressed size bytes
2024-01-03 13:13:50.391652+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression gzip 9 178833 duration millis 1354999073 compressed size bytes
2024-01-03 13:14:00.893554+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression lz4 1 10491 duration millis 2084288232 compressed size bytes
2024-01-03 13:14:38.258962+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression lz4 3 37356 duration millis 1601978337 compressed size bytes
2024-01-03 13:15:30.965415+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression lz4 5 52696 duration millis 1563219421 compressed size bytes
2024-01-03 13:16:44.030951+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression lz4 7 73056 duration millis 1553639021 compressed size bytes
2024-01-03 13:18:21.02153+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression lz4 9 96981 duration millis 1551033482 compressed size bytes
2024-01-03 13:22:33.860286+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression lz4 11 252829 duration millis 1541198017 compressed size bytes
2024-01-03 13:22:47.07405+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression zstd 1 13204 duration millis 1399029954 compressed size bytes
2024-01-03 13:23:18.088092+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression zstd 5 31004 duration millis 1167855682 compressed size bytes
2024-01-03 13:24:53.81479+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression zstd 9 95717 duration millis 1057089417 compressed size bytes
2024-01-03 13:30:17.698842+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression zstd 13 323874 duration millis 994535439 compressed size bytes
2024-01-03 13:43:14.464839+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression zstd 17 776756 duration millis 895866111 compressed size bytes
2024-01-03 14:19:08.513423+02 2024-01-03 13:05:36.599931+02 stackexchange_askubuntu pg_dump_compression zstd 21 2154038 duration millis 796045328 compressed size bytes
\.
--
-- PostgreSQL database dump complete
--
-- score
with q_data as (
select
dataset_name,
test_id method,
test_id_num as level,
test_value as time_spent_ms,
test_value_2 as backup_size_b
from
dataset_test_results
), q_dataset_mins as (
select
dataset_name,
min(time_spent_ms) min_time_spent_ms,
min(backup_size_b) min_backup_size_b
from
q_data
group by
1
)
select
method,
level,
avg(rank)::numeric(3,1) as avg_per_dataset_rank
from (
select
*,
rank() over(partition by dataset_name order by score)
from (
select
*,
(time_score + size_score)::numeric(6,2) as score
from (
select
q_data.dataset_name,
method,
level,
time_spent_ms,
(time_spent_ms / min_time_spent_ms)::numeric(6,2) as time_score,
backup_size_b,
(backup_size_b / min_backup_size_b)::numeric(6,2) as size_score
from
q_data
join
q_dataset_mins using (dataset_name)
) x
order by
dataset_name, time_score + size_score
) y
) z
group by 1, 2
order by 3
;
-- score
with q_data as (
select
dataset_name,
test_id method,
test_id_num as level,
test_value as time_spent_ms,
test_value_2 as backup_size_b
from
dataset_test_results
), q_dataset_mins as (
select
dataset_name,
min(time_spent_ms) min_time_spent_ms,
min(backup_size_b) min_backup_size_b
from
q_data
group by
1
)
select
*,
(time_score + size_score)::numeric(6,2) as score
from (
select
q_data.dataset_name,
method,
level,
time_spent_ms,
(time_spent_ms / min_time_spent_ms)::numeric(6,2) as time_score,
backup_size_b,
(backup_size_b / min_backup_size_b)::numeric(6,2) as size_score
from
q_data
join
q_dataset_mins using (dataset_name)
) x
order by
dataset_name, time_score + size_score
;
-- speed
select distinct on (dataset_name)
dataset_name,
method,
level,
time_spent_s,
(select (avg(test_value)/1000)::numeric(7,1) from dataset_test_results where dataset_name = x.dataset_name) as avg_time_spent_s,
dump_size,
(select pg_size_pretty(avg(test_value_2)) from dataset_test_results where dataset_name = x.dataset_name) as avg_dump_size
from (
select
dataset_name,
test_id method,
test_id_num as level,
(test_value/1000)::numeric(7,1) as time_spent_s,
pg_size_pretty(test_value_2) as backup_size
from
dataset_test_results
) x
order by dataset_name, time_spent_s;
-- min size
select distinct on (dataset_name)
dataset_name,
method,
level,
time_spent_s,
(select (avg(test_value)/1000)::numeric(7,1) from dataset_test_results where dataset_name = x.dataset_name) as avg_time_spent_s,
dump_size,
(select pg_size_pretty(avg(test_value_2)) from dataset_test_results where dataset_name = x.dataset_name) as avg_dump_size
from (
select
dataset_name,
test_id method,
test_id_num as level,
(test_value/1000)::numeric(7,1) as time_spent_s,
pg_size_pretty(test_value_2) as dump_size
from
dataset_test_results
) x
order by dataset_name, dump_size ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment