Otimização de Queries PostgreSQL

docker run -d --name pg-lab \
  -e POSTGRES_PASSWORD=lab -p 5432:5432 \
  --shm-size=1g \
  postgres:18 \
  -c shared_preload_libraries='pg_stat_statements,auto_explain' \
  -c shared_buffers=1GB -c work_mem=16MB \
  -c track_io_timing=on -c pg_stat_statements.track=all

# extensões extras (hypopg = índices hipotéticos) — a imagem oficial já tem o repo PGDG
docker exec -it pg-lab bash -c "apt-get update && apt-get install -y postgresql-18-hypopg"

docker exec -it pg-lab psql -U postgres

CREATE DATABASE lab;
\c lab
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_trgm;
CREATE EXTENSION pgstattuple;
CREATE EXTENSION pg_buffercache;
CREATE EXTENSION hypopg;
CREATE EXTENSION bloom;

-- no psql: mostrar tempo de cada comando
\timing on

CREATE TABLE customers (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text        NOT NULL,
  email       text        NOT NULL,
  city        text        NOT NULL,
  state       char(2)     NOT NULL,
  created_at  timestamptz NOT NULL
);

CREATE TABLE products (
  id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku    text    NOT NULL UNIQUE,
  name   text    NOT NULL,
  category text  NOT NULL,
  price  numeric(10,2) NOT NULL,
  tags   text[]  NOT NULL
);

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint      NOT NULL REFERENCES customers(id),
  status       text        NOT NULL,
  total        numeric(12,2) NOT NULL,
  created_at   timestamptz NOT NULL,
  payload      jsonb
);

CREATE TABLE order_items (
  order_id   bigint NOT NULL REFERENCES orders(id),
  product_id int    NOT NULL REFERENCES products(id),
  qty        int    NOT NULL,
  unit_price numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- 1M clientes: city e state são CORRELACIONADAS (city determina state)
INSERT INTO customers (name, email, city, state, created_at)
SELECT 'Cliente ' || g,
       'cliente' || g || '@exemplo.com',
       (ARRAY['Vitória','Vila Velha','Serra','Cariacica','São Paulo','Campinas','Rio de Janeiro','Niterói'])[1 + g % 8],
       (ARRAY['ES','ES','ES','ES','SP','SP','RJ','RJ'])[1 + g % 8],
       now() - (random() * 1500)::int * interval '1 day'
FROM generate_series(1, 1000000) g;

-- 5k produtos
INSERT INTO products (sku, name, category, price, tags)
SELECT 'SKU-' || lpad(g::text, 6, '0'),
       'Produto ' || md5(g::text),
       (ARRAY['eletrônicos','casa','moda','livros','esporte'])[1 + g % 5],
       round((random() * 2000 + 5)::numeric, 2),
       ARRAY[(ARRAY['promo','novo','premium','outlet','kit'])[1 + g % 5],
             (ARRAY['frete-gratis','importado','nacional'])[1 + g % 3]]
FROM generate_series(1, 5000) g;

-- 5M pedidos: created_at CRESCENTE com o id (ótimo para BRIN)
-- status enviesado: 'pending' ~1%, 'canceled' ~4%
INSERT INTO orders (customer_id, status, total, created_at, payload)
SELECT 1 + (random() * 999999)::bigint,
       CASE WHEN r < 0.01 THEN 'pending'
            WHEN r < 0.05 THEN 'canceled'
            WHEN r < 0.20 THEN 'paid'
            WHEN r < 0.40 THEN 'shipped'
            ELSE 'delivered' END,
       round((random() * 3000 + 10)::numeric, 2),
       timestamptz '2024-01-01' + g * interval '15 seconds',
       jsonb_build_object(
         'channel', (ARRAY['web','app','marketplace','loja'])[1 + g % 4],
         'coupon',  CASE WHEN g % 10 = 0 THEN 'PROMO10' END,
         'device',  jsonb_build_object('os', (ARRAY['android','ios','windows','macos'])[1 + g % 4]))
FROM (SELECT g, random() AS r FROM generate_series(1, 5000000) g) s;

-- ~12M itens (1 a 4 por pedido)
INSERT INTO order_items (order_id, product_id, qty, unit_price)
SELECT o.id, p.pid, 1 + (random() * 3)::int, round((random() * 500 + 5)::numeric, 2)
FROM orders o
CROSS JOIN LATERAL (
  SELECT DISTINCT 1 + (random() * 4999)::int AS pid
  FROM generate_series(1, 1 + (o.id % 4)::int)
) p;

VACUUM (ANALYZE) customers, products, orders, order_items;

SELECT relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, sum(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Vitória' AND o.created_at >= '2025-06-01'
GROUP BY c.name;

HashAggregate  (cost=178912.40..181412.40 rows=125000 width=45) (actual time=1480.221..1532.004 rows=108113 loops=1)
  Group Key: c.name
  Batches: 5  Memory Usage: 16433kB  Disk Usage: 12872kB
  Buffers: shared hit=2915 read=58690, temp read=1604 written=2958
  ->  Hash Join  (cost=27466.02..177648.60 rows=252760 width=21) (actual time=231.9..1391.8 rows=252629 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (cost=0.00..133520.00 rows=2019845 width=14) (actual time=0.02..905.1 rows=2022080 loops=1)
              Filter: (created_at >= '2025-06-01 00:00:00-03'::timestamptz)
              Rows Removed by Filter: 2977920
        ->  Hash  (cost=25846.00..25846.00 rows=125000 width=23) (actual time=231.2..231.2 rows=125000 loops=1)
              Buckets: 131072  Batches: 1  Memory Usage: 7856kB
              ->  Seq Scan on customers c  (cost=0.00..25846.00 rows=125000 width=23) (actual time=0.01..201.3 rows=125000 loops=1)
                    Filter: (city = 'Vitória'::text)
                    Rows Removed by Filter: 875000
Planning Time: 0.412 ms
Execution Time: 1539.550 ms

-- PG16+: planejar sem valores (como o prepared statement genérico vê)
EXPLAIN (GENERIC_PLAN)
SELECT * FROM orders WHERE customer_id = $1 AND created_at > $2;

-- DML com ANALYZE sem efeito colateral
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders SET status = 'canceled' WHERE status = 'pending' AND created_at < now() - interval '30 days';
ROLLBACK;

-- JSON para colar em https://explain.dalibo.com
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON) SELECT ...;

-- FK sem índice: order_items.product_id
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.sku, count(*)
FROM products p JOIN order_items oi ON oi.product_id = p.id
WHERE p.category = 'livros' AND p.price > 1900
GROUP BY p.sku;
-- Esperado: Hash Join (build = products filtrados, probe = Seq Scan de 12M itens)

CREATE INDEX idx_oi_product ON order_items (product_id);
ANALYZE order_items;
-- Rode de novo: agora Nested Loop (products filtrados → Index Scan em order_items)

-- Forçando alternativas para comparar (somente estudo!)
SET enable_nestloop = off;  EXPLAIN (ANALYZE) ...;  RESET enable_nestloop;
SET enable_hashjoin = off;  EXPLAIN (ANALYZE) ...;  RESET enable_hashjoin;

->  Hash  (actual time=2210.4..2210.4 rows=5000000 loops=1)
      Buckets: 262144  Batches: 32  Memory Usage: 11265kB
      Buffers: temp written=21456

Nested Loop  (actual time=0.05..310.2 rows=250000 loops=1)
  ->  Seq Scan on order_items oi  (actual rows=250000 loops=1)
  ->  Memoize  (actual time=0.001..0.001 rows=1 loops=250000)
        Cache Key: oi.product_id
        Hits: 245000  Misses: 5000  Evictions: 0  Overflows: 0  Memory Usage: 590kB
        ->  Index Scan using products_pkey on products p  (actual rows=1 loops=5000)

SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats WHERE tablename = 'orders' AND attname = 'status';
-- most_common_vals  = {delivered,shipped,paid,canceled,pending}
-- most_common_freqs = {0.60,0.20,0.15,0.04,0.01}

-- Amostra = 300 × default_statistics_target (100) = 30.000 linhas
-- Aumentar para uma coluna com distribuição complexa:
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders (customer_id);

EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Vitória' AND state = 'ES';
-- Seq Scan on customers (rows=62500) (actual rows=125000)
--   estimativa: 1/8 × 1/2 = 6,25%   real: 12,5%  → subestimou 2×

CREATE STATISTICS st_customers_city_state (dependencies, ndistinct, mcv)
  ON city, state FROM customers;
ANALYZE customers;

EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Vitória' AND state = 'ES';
-- rows=125000 (actual rows=125000) ✔

-- GROUP BY em colunas correlacionadas também melhora (ndistinct):
EXPLAIN SELECT city, state, count(*) FROM customers GROUP BY city, state;
-- sem stats: rows=16 (8 × 2) · com stats: rows=8

SELECT * FROM pg_stats_ext WHERE statistics_name = 'st_customers_city_state';

-- jsonb_ops (padrão): suporta @>, ?, ?|, ?&, @?, @@
CREATE INDEX idx_orders_payload ON orders USING gin (payload);

-- jsonb_path_ops: só @>, @?, @@ — porém menor e mais rápido
CREATE INDEX idx_orders_payload_path ON orders USING gin (payload jsonb_path_ops);

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE payload @> '{"coupon": "PROMO10"}';
-- Bitmap Heap Scan → Bitmap Index Scan on idx_orders_payload_path

-- ATENÇÃO: ->> com = NÃO usa índice GIN; use @> ou um índice de expressão B-tree:
CREATE INDEX idx_orders_channel ON orders ((payload->>'channel'));
SELECT count(*) FROM orders WHERE payload->>'channel' = 'app';

-- LIKE/ILIKE com curinga no início: pg_trgm
CREATE INDEX idx_customers_name_trgm ON customers USING gin (name gin_trgm_ops);
EXPLAIN ANALYZE SELECT * FROM customers WHERE name ILIKE '%nte 4242%';

-- Full-text search com coluna gerada
ALTER TABLE products ADD COLUMN fts tsvector
  GENERATED ALWAYS AS (to_tsvector('portuguese', name || ' ' || category)) STORED;
CREATE INDEX idx_products_fts ON products USING gin (fts);
SELECT sku FROM products WHERE fts @@ websearch_to_tsquery('portuguese', 'livros');

-- Arrays
CREATE INDEX idx_products_tags ON products USING gin (tags);
SELECT count(*) FROM products WHERE tags @> ARRAY['promo','importado'];

CREATE INDEX idx_orders_created_brin ON orders USING brin (created_at) WITH (pages_per_range = 32);

SELECT pg_size_pretty(pg_relation_size('idx_orders_created_at'))   AS btree,   -- ~107 MB
       pg_size_pretty(pg_relation_size('idx_orders_created_brin')) AS brin;    -- ~48 kB

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE created_at BETWEEN '2025-03-01' AND '2025-03-02';
-- Bitmap Heap Scan ... Rows Removed by Index Recheck: ~N   Heap Blocks: lossy=...
-- BRIN é sempre lossy: devolve faixas de blocos que "podem" conter os valores.

-- PG14+: minmax-multi tolera outliers (ex.: registros atrasados)
CREATE INDEX ... USING brin (created_at timestamptz_minmax_multi_ops);

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservas (
  sala    int,
  periodo tstzrange,
  EXCLUDE USING gist (sala WITH =, periodo WITH &&)   -- impede sobreposição
);

SELECT * FROM reservas WHERE periodo && tstzrange(now(), now() + interval '1 hour');

-- KNN: os 5 produtos com preço mais próximo de 100 (ordenação pelo índice)
-- (btree_gist oferece <-> para int/float/date/timestamp; numeric não tem, por isso o cast)
CREATE INDEX idx_products_price_gist ON products USING gist ((price::float8));
SELECT sku, price FROM products ORDER BY price::float8 <-> 100 LIMIT 5;

CREATE INDEX idx_customers_email_hash ON customers USING hash (email);

CREATE TABLE acessos (ip inet, ts timestamptz);
CREATE INDEX ON acessos USING spgist (ip inet_ops);
SELECT * FROM acessos WHERE ip << '10.0.0.0/8';

-- Bloom: filtros em qualquer combinação de colunas int/text
CREATE INDEX idx_oi_bloom ON order_items USING bloom (order_id, product_id, qty)
  WITH (length = 80, col1 = 2, col2 = 2, col3 = 4);

-- ✗ não usa idx_orders_created_at
SELECT count(*) FROM orders WHERE created_at::date = '2025-03-10';
SELECT count(*) FROM orders WHERE extract(year FROM created_at) = 2025;

-- ✓ faixas semiabertas
SELECT count(*) FROM orders
WHERE created_at >= '2025-03-10' AND created_at < '2025-03-11';

-- clientes que nunca compraram
-- ✗ se orders.customer_id pudesse ser NULL o resultado seria vazio; e o plano é pior
SELECT count(*) FROM customers c WHERE c.id NOT IN (SELECT customer_id FROM orders);

-- ✓ Hash Anti Join
SELECT count(*) FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- ✗ OR entre colunas de tabelas diferentes impede o uso de índices
SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'cliente42@exemplo.com' OR o.payload @> '{"coupon":"VIP"}';

-- ✓ cada ramo usa seu índice
SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'cliente42@exemplo.com'
UNION
SELECT o.* FROM orders o WHERE o.payload @> '{"coupon":"VIP"}';

-- ✗ página 5000: lê 100.020 linhas
SELECT id, created_at, total FROM orders ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- ✓ passa o último (created_at, id) visto; custo constante em qualquer página
CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);
SELECT id, created_at, total FROM orders
WHERE (created_at, id) < ('2025-05-01 10:00:00-03', 3200000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Os 3 últimos pedidos de cada cliente de Vitória (usa idx_orders_cust_created)
SELECT c.id, o.*
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, total, created_at FROM orders
  WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 3
) o
WHERE c.city = 'Vitória';

-- Contagem aproximada instantânea (quando exatidão não é necessária)
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

vacuum dispara quando: dead_tuples > autovacuum_vacuum_threshold (50)
                                   + autovacuum_vacuum_scale_factor (0.2) × reltuples
analyze dispara quando: mudanças   > autovacuum_analyze_threshold (50)
                                   + autovacuum_analyze_scale_factor (0.1) × reltuples

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor  = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit    = 2000     -- deixa o worker trabalhar mais rápido
);

-- Monitorando
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS pct_dead,
       last_autovacuum, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

-- Progresso de vacuums em execução
SELECT * FROM pg_stat_progress_vacuum;

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS pct_hot
FROM pg_stat_user_tables ORDER BY n_tup_upd DESC;

ALTER TABLE orders SET (fillfactor = 85);   -- vale para páginas novas; reescreva para aplicar a tudo

SELECT * FROM pgstattuple('orders');                 -- exato, lê a tabela inteira
SELECT * FROM pgstattuple_approx('orders');          -- rápido, usa o visibility map
SELECT * FROM pgstatindex('idx_orders_created_at');  -- avg_leaf_density, leaf_fragmentation

-- Índice inchado: reconstrua sem lock de escrita
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

CREATE TABLE orders_p (
  id bigint, customer_id bigint, status text, total numeric(12,2),
  created_at timestamptz NOT NULL, payload jsonb,
  PRIMARY KEY (id, created_at)                 -- a PK precisa conter a chave de partição
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_p_2024 PARTITION OF orders_p FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_p_2025 PARTITION OF orders_p FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_p_2026 PARTITION OF orders_p FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

INSERT INTO orders_p SELECT * FROM orders;
CREATE INDEX ON orders_p (customer_id, created_at);   -- propagado a todas as partições
ANALYZE orders_p;

-- Pruning em tempo de plano: só a partição 2025 aparece
EXPLAIN SELECT count(*) FROM orders_p WHERE created_at BETWEEN '2025-03-01' AND '2025-04-01';

-- Pruning em tempo de execução (parâmetros, subqueries, now())
EXPLAIN (ANALYZE) SELECT count(*) FROM orders_p WHERE created_at > now() - interval '10 days';
--   Subplans Removed: 2

-- Joins e agregações por partição (desligados por padrão)
SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;