indexing.sql

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

-- 2. Partial
create table t_jobs(id serial, status text);
insert into t_jobs(status) select 'NEW' from generate_series(1, 10);
insert into t_jobs(status) select 'PROCESSED' from generate_series(1, 1000000);
create index ON t_jobs(status);

-- Finding invalid indexes
select
  nspname||'.'|| c.relname as invalid_index_full_name
from
  pg_index i
  join
  pg_class c on c.oid = i.indexrelid
  join pg_namespace n on n.oid = relnamespace
where
  not indisvalid;
  
  
-- Missing index candidates
create extension pg_stat_statements ; -- needs also changing of server config
select * from pg_stat_statements where calls > 10 order by mean_time desc limit 3;