tips_and_gotchas.sql

-- multiple LIKE conditions
select * from t_test where c LIKE any(array['%566%', '%7788%'])  limit 5;

-- selecting json
select * from t_json where data @> '{"a":1}';

-- array access
select ('{1:2,3}'::text[])[2];

-- fixing sequences
select setval('t_serial_id_seq', (select max(id) from t_serial));

-- extract time info
select extract(day from now());

-- text searching for data
pg_dump -t t_test --data-only db1 | grep 887788

-- force your join ordering
set join_collapse_limit to 1;

-- set settings per database
alter role u1 in database db1 set join_collapse_limit to 1;

-- system tables
select table_name from information_schema.tables where table_schema like 'pg\_%';

-- locks for a table
select * from pg_locks where relation = regclass('t_test')::oid;

-- users of a table
select a.usename, a.query from pg_locks l join pg_stat_activity a on a.pid = l.pid  where relation = regclass('t_test')::oid;

-- top scans for tables > 10MB
select * from pg_stat_user_tables where pg_relation_size(relid) > 10e7 order by seq_scan desc nulls last;

-- setup of pg_stat_statements
-- 1. server config needs to be changed: shared_preload_libraries = 'pg_stat_statements' + restart
-- 2. in every db create the extension
create extension pg_stat_statements;

-- top select time spent
select * from pg_stat_statements where calls > 3 order by blk_read_time desc nulls last;