kmoppel

create table t1(c1 int);

CREATE OR REPLACE FUNCTION public.t1_upd()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
perform pg_sleep(1);
return new;
end;

kmoppel

# Sample init script for Centos 7
# Place the file in /usr/lib/systemd/system/postgresql-9.5-custom.service 
#
# systemctl enable postgresql-9.5-custom.service
# systemctl status postgresql-9.5-custom.service

# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-setup.
[Unit]
Description=Custom PostgreSQL 9.5 database server

kmoppel

ALTER TABLE pgbench_accounts
	ADD created_on timestamptz,
	DROP CONSTRAINT pgbench_accounts_pkey;

CREATE UNIQUE INDEX ON pgbench_accounts(aid, created_on DESC);

kmoppel

PATH=/usr/local/pgsql/bin:$PATH

MASTER_PORT=5432
REPLICA_IP=172.31.28.169

function append_param_to_config {
OPTS=$(cat <<HERE
${1}
HERE
)

kmoppel

SET maintenance_work_mem TO '1GB';

CREATE TABLE t_test (a int NOT NULL, b int, c text);
INSERT INTO t_test SELECT i AS a, random()*100 as b, random() as c FROM generate_series(1, 2*1e6::int) AS i;
CREATE INDEX ON t_test (a);
ANALYZE t_test;

CREATE TABLE t_joiny (a INT NOT NULL, b int);
INSERT INTO t_joiny SELECT i AS a, (random()*100)::int as b FROM generate_series(1, 1e6::int) AS i ;
CREATE UNIQUE INDEX ON t_joiny (a);

kmoppel

-- 1. selectivity basics

CREATE TABLE t_person (id SERIAL PRIMARY KEY, name TEXT NOT NULL);

INSERT INTO t_person(name) SELECT unnest(array['alice', 'bob']) FROM generate_series(1, 1000000);

CREATE INDEX ON t_person (name);

ANALYZE t_person;

kmoppel

-- normal view
create or replace view v_test as select a, b from t_test;
explain select * from v_test where a < 100;

-- materialized view
create materialized view m_test as select a, b from t_test;
refresh materialized view m_test ; -- locking version
refresh materialized view CONCURRENTLY m_test ; -- needs unique key!

-- recursion

kmoppel

-- exporting data with COPY to Excel
\copy (select * from t_test limit 2) to '/tmp/test.dump' with (format csv, header)
-- on Windows
\copy (select * from t_test limit 2) to 'c:\\User\\X\\test.dump' with (format csv, header)

kmoppel

-- subqueries
explain select * from t_counter c, lateral (select * from t_joiny j where j.a = c.id) s;
explain select * from t_counter c join lateral (select * from t_joiny j where j.a = c.id) s on true;
-- functions
explain select * from t_counter, lateral (select * from generate_series(1, id)) s;
explain select * from t_counter,  generate_series(1, id) s;

kmoppel

create table t_log (created_on timestamptz not null, event text);

create table t_log_archive (created_on timestamptz not null, event text);

insert into t_log select d, 'event' from generate_series(current_date-300, now(), '5m'::interval) d;

with q_sub as (delete from t_log where created_on < current_date - 30 returning *) insert into t_log_archive select * from q_sub;