Useful SQL queries for debugging

gistfile1.txt

// Change SQL client timeout

set statement_timeout = '0s';
set statement_timeout = '10s';

// Change timeout globally

alter database mentimeter set statement_timeout = '10s';

// Time queries

\timing

// Create multicolumn index concurrenty

CREATE INDEX CONCURRENTLY vote_texts_multi_column ON vote_texts (question_id, identifier_id);

// Drop index

DROP INDEX CONCURRENTLY vote_texts_multi_column;

// See all locks

SELECT locktype, relation::regclass, mode, pid FROM pg_locks;

// See all locks with relevant query

SELECT
  S.pid,
  age(clock_timestamp(), query_start),
  query,
  L.mode,
  L.locktype,
  L.granted
FROM pg_stat_activity S
inner join pg_locks L on S.pid = L.pid
order by L.granted, L.pid DESC;


// List all tables of a specific schema

\dt schema_name.*


// Get number of rows per table

WITH tbl AS
(SELECT table_schema,
        TABLE_NAME
 FROM information_schema.tables
 WHERE TABLE_NAME not like 'pg_%'
   AND table_schema in ('public'))
SELECT table_schema,
     TABLE_NAME,
     (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

// Get size of databases

select t1.datname AS db_name,  
pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc;

Useful article about types of locks:
http://shiroyasha.io/understanding-postgresql-locks.html

Interesting read about how an UPDATE resulted in table level exclusive lock instead of a row level lock:
https://blog.heroku.com/curious-case-table-locking-update-query

Decent explaination of EXPLAIN ANALYZE:
https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan

Query plan viewer:
http://tatiyants.com/pev/#/plans/new

How to speed up sorting/order by:
https://www.cybertec-postgresql.com/en/postgresql-improving-sort-performance/

Reindexing:
https://ketanbhatt.com/postgres-recreate-index/

Debugging Postgres locks:
https://www.crunchydata.com/blog/one-pid-to-lock-them-all-finding-the-source-of-the-lock-in-postgres