kmoppel

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

kmoppel

-- 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.0484473166

kmoppel

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.2 (Ubuntu 15.2-1.pgdg22.04+1)
-- Dumped by pg_dump version 15.2 (Ubuntu 15.2-1.pgdg22.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;

kmoppel

#!/bin/bash

set -eu -o pipefail

export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=postgres
export PGUSER=postgres
export PGPASSWORD=postgres
export PATH=/usr/lib/postgresql/15/bin:$PATH

kmoppel

export PGHOST=/var/run/postgresql
export PGUSER=postgres
export PGDATABASE=postgres
export PGOPTIONS='-c maintenance_work_mem=4GB' # Helps to speed up CREATE INDEX for most index types
CLIENTS=2
JOBS=1
DURATION=1800


SQL_DDL=$(cat << "EOF"

kmoppel

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.5 (Ubuntu 14.5-1.pgdg22.04+1)
-- Dumped by pg_dump version 14.5 (Ubuntu 14.5-1.pgdg22.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;

kmoppel

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

# Inspired by https://dzone.com/articles/redis-vs-memcached-2021-comparison

ROWS = [1000, 10000, 100000, 1000000] # 1mio rows will be 66 MB data size + 50 MB index size so make sure shared_buffers is 128MB+
LOOPS=10
TEST_NAME = 'run1'

import psycopg2

kmoppel

explain analyse
with q_data as (
    select
    (extract (epoch from now()) * 1e9)::int8 as epoch_ns,
    queryid::text as tag_queryid,
    /*
    NB! if security conscious about exposing query texts replace the below expression with a dash ('-') OR
    use the stat_statements_no_query_text metric instead, created specifically for this use case.
    */
    --max(ltrim(regexp_replace(query, E'[ \\t\\n\\r]+' , ' ', 'g')))::varchar(16000) as tag_query,

kmoppel

SET search_path TO public;
SET synchronous_commit TO off;

CREATE TABLE t_oil (
    region text,
    country text,
    year integer,
    production integer,
    consumption integer
);

kmoppel

DROP TABLE IF EXISTS standard_flow, alternative_flow, alternative_flow_state CASCADE;
--TRUNCATE standard_flow, alternative_flow, alternative_flow_state;

/* STANDARD UPDATES */
CREATE TABLE IF NOT EXISTS standard_flow (
  session_id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  state text NOT NULL DEFAULT 'APPSTATE_1',
  created_on timestamptz NOT NULL DEFAULT now(),
  last_state_change timestamptz,
  data1 int8,