Last active
January 5, 2024 00:40
-
-
Save kmoppel/3fe12db152fd38a0a98bd7de35bf7feb to your computer and use it in GitHub Desktop.
To analyze https://github.com/kmoppel/pg-open-datasets test results
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 characters
| -- | |
| -- 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 | |
| -- | |
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 characters
| -- 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 | |
| ; |
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 characters
| -- 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 | |
| ; |
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 characters
| -- 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