Camille Baldock

Troubleshooting performance issues in PostgreSQL

Install Postgres

MacOS, Windows, Linux

Course slides

Download course slides pdf

Queries for the course


🔷 1

CREATE TABLE demo_buffers(value int); SELECT pg_relation_filepath('demo_buffers');

🔷 2


SELECT relname, oid, relfilenode
FROM pg_class WHERE relname='demo_buffers';

🔷 3


\d pg_catalog.pg_class

🔷 4


CREATE EXTENSION pg_buffercache;

INSERT INTO demo_buffers VALUES (1);

SELECT reldatabase, relfilenode, isdirty, usagecount from pg_buffercache WHERE relfilenode=16504;

🔷 5


CHECKPOINT;

SELECT reldatabase, relfilenode, isdirty, usagecount FROM pg_buffercache WHERE relfilenode=16504;

🔷 6


CREATE TABLE conferences (
  name text,
  description text,
  conference_id int,
  total_attendees int DEFAULT 0,
  expected_attendees int DEFAULT 0,
  start_date date);

INSERT INTO conferences
(name, description, conference_id, total_attendees, expected_attendees)
SELECT 'name ' || s,
'description ' || s,
s,
(random() * 500 + 1)::int,
(random() * 500 + 1)::int
FROM generate_series(1,20000) s;

🔷 7


SELECT * FROM conferences WHERE conference_id = 42;

🔷 8


EXPLAIN SELECT * FROM conferences WHERE conference_id = 42;

🔷 9


select relpages, reltuples from pg_catalog.pg_class where relname = 'conferences';
select histogram_bounds from pg_stats where tablename = 'conferences';

🔷 10


EXPLAIN ANALYZE SELECT * FROM conferences WHERE conference_id = 42;

🔷 11


EXPLAIN ANALYZE SELECT * FROM conferences WHERE conference_id = 42;

🔷 12


CREATE INDEX ON conferences(conference_id);

🔷 13


CREATE INDEX CONCURRENTLY ON conferences(conference_id);

🔷 14


\timing

EXPLAIN ANALYZE SELECT count(*) FROM conferences WHERE total_attendees > 100;

SELECT count(*) FROM conferences WHERE total_attendees > 100;

🔷 15


CREATE INDEX ON conferences(total_attendees);
SELECT * FROM conferences WHERE total_attendees > 100;

🔷 16


EXPLAIN ANALYZE SELECT * FROM conferences WHERE total_attendees > 100;

🔷 17


EXPLAIN ANALYZE SELECT * FROM conferences WHERE total_attendees = 500;

🔷 18


CREATE INDEX ON conferences(expected_attendees);
SELECT * FROM conferences WHERE total_attendees > 400 AND expected_attendees < 150;

🔷 19


EXPLAIN ANALYZE SELECT * FROM conferences WHERE total_attendees > 400 and expected_attendees < 150;

🔷 20


EXPLAIN ANALYZE SELECT * FROM conferences WHERE total_attendees > 400 and expected_attendees < 100;

🔷 21


SELECT count(*) FROM conferences WHERE total_attendees > 400;
SELECT count(*) FROM conferences WHERE expected_attendees < 100;
SELECT count(*) FROM conferences WHERE expected_attendees < 150;

🔷 22


set enable_bitmapscan=off;
set enable_indexscan=off;
set enable_indexonlyscan=off;

🔷 23


CREATE TABLE sponsors (name text,
conference_id int, employees int);

DO
$do$
BEGIN
FOR i IN 1..20000 LOOP
  INSERT INTO sponsors
  (name, conference_id, employees)
  SELECT 'sponsor ' || s,
  i,
  (random() * 500 + 1)::int
  FROM generate_series(1,10) s;
END LOOP;
END
$do$;

🔷 24


DROP INDEX conferences_conference_id_idx;

🔷 25


SELECT * FROM sponsors, conferences
WHERE sponsors.conference_id = 1
AND sponsors.conference_id = conferences.conference_id;

🔷 26


EXPLAIN ANALYZE SELECT * FROM sponsors, conferences
WHERE sponsors.conference_id = 1
AND sponsors.conference_id = conferences.conference_id;

🔷 27


CREATE INDEX ON sponsors(conference_id);
CREATE INDEX ON conferences(conference_id);

🔷 28


CREATE INDEX CONCURRENTLY ON sponsors(conference_id);
CREATE INDEX CONCURRENTLY ON conferences(conference_id);

🔷 29


EXPLAIN ANALYZE SELECT * from sponsors, conferences
WHERE sponsors.conference_id = 1
AND sponsors.conference_id = conferences.conference_id;

🔷 30


SELECT conferences.conference_id, avg(employees)
FROM conferences, sponsors
WHERE conferences.conference_id = sponsors.conference_id
GROUP BY conferences.conference_id;

🔷 31


EXPLAIN ANALYZE SELECT conferences.conference_id, avg(employees) FROM conferences, sponsors WHERE conferences.conference_id = sponsors.conference_id GROUP BY conferences.conference_id;

🔷 32


SELECT * FROM sponsors, conferences WHERE sponsors.conference_id = conferences.conference_id;

🔷 33


EXPLAIN ANALYZE SELECT * FROM sponsors, conferences WHERE sponsors.conference_id = conferences.conference_id;

🔷 34 (from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans
),
indexes as (
    SELECT idx_stat.relid, idx_stat.indexrelid,
        idx_stat.schemaname, idx_stat.relname as tablename,
        idx_stat.indexrelname as indexname,
        idx_stat.idx_scan,
        pg_relation_size(idx_stat.indexrelid) as index_bytes,
        indexdef ~* 'USING btree' AS idx_is_btree
    FROM pg_stat_user_indexes as idx_stat
        JOIN pg_index
            USING (indexrelid)
        JOIN pg_indexes as indexes
            ON idx_stat.schemaname = indexes.schemaname
                AND idx_stat.relname = indexes.tablename
                AND idx_stat.indexrelname = indexes.indexname
    WHERE pg_index.indisunique = FALSE
),
index_ratios AS (
SELECT schemaname, tablename, indexname,
    idx_scan, all_scans,
    round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC
        ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct,
    writes,
    round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2)
        as scans_per_write,
    pg_size_pretty(index_bytes) as index_size,
    pg_size_pretty(table_size) as table_size,
    idx_is_btree, index_bytes
    FROM indexes
    JOIN table_scans
    USING (relid)
),
index_groups AS (
SELECT 'Never Used Indexes' as reason, *, 1 as grp
FROM index_ratios
WHERE
    idx_scan = 0
    and idx_is_btree
UNION ALL
SELECT 'Low Scans, High Writes' as reason, *, 2 as grp
FROM index_ratios
WHERE
    scans_per_write <= 1
    and index_scan_pct < 10
    and idx_scan > 0
    and writes > 100
    and idx_is_btree
UNION ALL
SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp
FROM index_ratios
WHERE
    index_scan_pct < 5
    and scans_per_write > 1
    and idx_scan > 0
    and idx_is_btree
    and index_bytes > 100000000
UNION ALL
SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp
FROM index_ratios, all_writes
WHERE
    ( writes::NUMERIC / ( total_writes + 1 ) ) > 0.02
    AND NOT idx_is_btree
    AND index_bytes > 100000000
ORDER BY grp, index_bytes DESC )
SELECT reason, schemaname, tablename, indexname,
    index_scan_pct, scans_per_write, index_size, table_size
FROM index_groups;

🔷 35(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


SELECT pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup
FROM pg_class c WHERE relname='conferences';

UPDATE conferences SET name = 'something';

VACUUM conferences;

🔷 36(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


UPDATE conferences
SET start_date = '1/1/2017'::date + ('1 day'::interval*floor(random()*180));

CREATE INDEX ON conferences(start_date);

CREATE TABLE conferences_2017_q1 (
CHECK (start_date >= DATE '2017-01-01'
AND start_date < DATE '2017-04-01'))
INHERITS (conferences);

CREATE TABLE conferences_2017_q2 (
CHECK (start_date >= DATE '2017-04-01'
AND start_date < DATE '2017-07-01'))
INHERITS (conferences);

🔷 37(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


CREATE OR REPLACE FUNCTION conferences_update_trigger()
RETURNS TRIGGER AS
$$
BEGIN
  IF (NEW.start_date >= DATE ('2017-01-01') AND NEW.start_date < DATE('2017-04-01')) THEN
  INSERT INTO conferences_2017_q1 VALUES (NEW.*);
  ELSIF (NEW.start_date >= DATE ('2017-04-01') AND NEW.start_date < DATE('2017-07-01')) THEN
  INSERT INTO conferences_2017_q2 VALUES (NEW.*);
  ELSE
RAISE EXCEPTION 'start_date date out of range';
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_conferences_trigger BEFORE UPDATE ON conferences FOR EACH ROW EXECUTE PROCEDURE conferences_update_trigger();

🔷 38(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


SELECT count(*) FROM conferences;

SELECT count(*) FROM conferences_2017_q1;

UPDATE conferences set start_date=start_date;

🔷 39(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


EXPLAIN ANALYZE SELECT * FROM conferences WHERE start_date < '02-11-2017' AND start_date > '01-11-2017';

🔷 40(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


CREATE INDEX CONCURRENTLY ON conferences_2017_q1(start_date);
CREATE INDEX CONCURRENTLY ON conferences_2017_q2(start_date);

🔷 41(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


\d conferences

CLUSTER conferences using conferences_start_date_idx;

ANALYZE conferences;

🔷 42(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


EXPLAIN ANALYZE SELECT * FROM conferences WHERE total_attendees < 200;

🔷 43(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


CREATE DATABASE tutorial_bench;

🔷 44(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


pgbench -i -s 50 tutorial_bench

🔷 45(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


\dt

SELECT pg_size_pretty(pg_database_size(pg_database.datname)) AS size  
FROM pg_database where datname = 'tutorial_bench';

🔷 46(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


pgbench -c 10 -j 2 -t 10000 tutorial_bench

🔷 47(from: https://github.com/pgexperts/pgx_scripts/blob/master/indexes/unused_indexes.sql)


SELECT relname, age(relfrozenxid) FROM pg_class WHERE relname = 'conferences';

SELECT setting::numeric AS max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age';