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

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

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

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

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;

kmoppel

select distinct on (id) id, value2 from t_pk order by id, value2 asc nulls first;

-- filter
explain analyze select region, sum(case when year = 2009 then production else 0 end) as p2009, sum(case when year = 2010 then production else 0 end) as p2010 from t_oil group by region;

explain analyze select region, sum(production) filter (where  year = 2009) as p2009, sum(production) filter (where  year = 2010) as p2010 from t_oil group by region;

-- array_agg
select string_agg(a::text, ':')::text from (select a from t_test limit 100) s;
select array_agg(a order by a desc)::text from (select a from t_test limit 10) s;

kmoppel

create or replace function all_queries(out q_pid int, out q_usename text, out q_query text) returns setof record as
$$
select pid, usename::text, query from pg_stat_activity
$$ language sql security definer;