Ottimizzazione delle Query PostgreSQL: Da Lente a Sub-Millisecondi | Akousa

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
   Index Cond: (customer_id = 42)

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.43..8.45 rows=1 width=128)
                                                    (actual time=0.021..0.023 rows=1 loops=1)
   Index Cond: (customer_id = 42)
 Planning Time: 0.085 ms
 Execution Time: 0.041 ms

                                    QUERY PLAN
---------------------------------------------------------------------------
 Hash Join  (cost=425.00..1893.25 rows=2150 width=48)
            (actual time=2.341..15.892 rows=2347 loops=1)
   Hash Cond: (o.customer_id = c.id)
   Buffers: shared hit=312 read=45
   ->  Bitmap Heap Scan on orders o  (cost=12.50..1450.75 rows=2150 width=32)
                                     (actual time=0.512..12.105 rows=2347 loops=1)
         Recheck Cond: (created_at > '2026-01-01'::date)
         Filter: (status = 'completed')
         Rows Removed by Filter: 893
         Heap Blocks: exact=189
         Buffers: shared hit=195 read=45
         ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..11.96 rows=3043 width=0)
                                                         (actual time=0.287..0.287 rows=3240 loops=1)
               Index Cond: (created_at > '2026-01-01'::date)
               Buffers: shared hit=12
   ->  Hash  (cost=287.50..287.50 rows=10000 width=24)
             (actual time=1.753..1.753 rows=10000 loops=1)
         Buffers: shared hit=117
         ->  Seq Scan on customers c  (cost=0.00..287.50 rows=10000 width=24)
                                      (actual time=0.008..0.892 rows=10000 loops=1)
               Buffers: shared hit=117
 Planning Time: 0.215 ms
 Execution Time: 16.147 ms

Bitmap Heap Scan on orders  (cost=45.00..1250.00 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-01-01')
  ->  Bitmap Index Scan on idx_orders_created_at  (cost=0.00..43.75 rows=5000 width=0)
        Index Cond: (created_at > '2026-01-01')

Bitmap Heap Scan on orders
  Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
  ->  BitmapAnd
        ->  Bitmap Index Scan on idx_orders_status
              Index Cond: (status = 'completed')
        ->  Bitmap Index Scan on idx_orders_created_at
              Index Cond: (created_at > '2026-01-01')

sql

-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- Buono: uguaglianza prima, poi range, poi ordinamento
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- Cattivo: range prima dell'uguaglianza
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

sql

-- Un altro pattern comune: soft delete
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- Vincolo unico solo sui record attivi
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

sql

-- Query di contenimento JSONB
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- Ora questo è veloce:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- Ricerca full-text
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

sql

-- Ricerche di range IP
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- Range temporali (conflitti di prenotazione)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- Trovare prenotazioni sovrapposte:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

sql

-- Buono per ricerche solo-uguaglianza su colonne di testo grandi
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- Questo è veloce:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- Questo NON PUÒ usare l'indice hash:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

sql

-- Trova indici inutilizzati
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- escludi chiave primaria e vincoli unici
  )
ORDER BY pg_relation_size(indexrelid) DESC;

sql

-- Trova indici duplicati (stesse colonne, stesso ordine)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

python

# Python / SQLAlchemy — la classica trappola
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # Questo innesca una query separata per OGNI ordine
    print(f"Customer: {order.customer.name}")

sql

-- Query 1: Ottieni tutti gli ordini in attesa
SELECT * FROM orders WHERE status = 'pending';
 
-- Query 2: Ottieni il cliente per l'ordine 1
SELECT * FROM customers WHERE id = 101;
 
-- Query 3: Ottieni il cliente per l'ordine 2
SELECT * FROM customers WHERE id = 102;
 
-- ...Query 502: Ottieni il cliente per l'ordine 500
SELECT * FROM customers WHERE id = 600;

sql

-- Logga tutte le query che impiegano più di 0ms (cioè tutte le query)
SET log_min_duration_statement = 0;
 
-- Meglio per la produzione: logga solo le query lente
ALTER SYSTEM SET log_min_duration_statement = 50;  -- soglia 50ms
SELECT pg_reload_conf();

python

# SQLAlchemy — joinedload recupera i clienti nella stessa query
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

sql

-- Approccio 1: Singola JOIN (potrebbe produrre dati duplicati se uno-a-molti)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- Approccio 2: Due query (meno trasferimento dati se gli ordini hanno molte colonne)
SELECT * FROM orders WHERE status = 'pending';
-- l'applicazione raccoglie order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

sql

-- Subquery nel WHERE — spesso va bene, a volte terribile
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — di solito la scelta migliore
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — materializzata in PG 11 e precedenti, ottimizzata in PG 12+
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

sql

-- EXISTS — si ferma alla prima corrispondenza, ottimo per verificare l'esistenza
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN con subquery — costruisce prima l'intero set di risultati
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — può produrre duplicati se returns ha più righe per ordine
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

sql

-- Male: recupera tutte le 25 colonne inclusa una colonna TEXT con kilobyte di dati
SELECT * FROM products WHERE category = 'electronics';
 
-- Bene: recupera solo ciò di cui hai bisogno
SELECT id, name, price FROM products WHERE category = 'electronics';

sql

-- Lento: la subquery correlata viene eseguita una volta per riga
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- Veloce: le funzioni window calcolano in un singolo passaggio
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

sql

-- Lento: subquery correlata
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- Veloce: DISTINCT ON (specifico di PostgreSQL, estremamente utile)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- Anche veloce: funzione window ROW_NUMBER
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

sql

-- Pagina 1: veloce
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- Pagina 500: lenta — PostgreSQL deve scansionare e scartare 9.980 righe
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- Pagina 5000: molto lenta — scansiona 99.980 righe per restituirne 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

sql

-- Analizza una singola tabella
ANALYZE orders;
 
-- Analizza l'intero database
ANALYZE;
 
-- Visualizza le statistiche correnti per una colonna
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

sql

-- Controlla lo stato dell'autovacuum
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

sql

-- Autovacuum più aggressivo per tabelle con molte scritture
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- vacuum all'1% di tuple morte (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- analyze allo 0,5% di modifiche (default 10%)
  autovacuum_vacuum_cost_delay = 2           -- vacuum più veloce (default 2ms nei PG più recenti)
);

sql

-- Stima il bloat della tabella (versione semplificata)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; Modalità pool
pool_mode = transaction
 
; Dimensionamento del pool
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; Timeout
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

javascript

const pool = new Pool({
  host: "127.0.0.1",
  port: 6432, // Porta PgBouncer, non la porta PostgreSQL
  database: "mydb",
  max: 20, // corrisponde al default_pool_size di PgBouncer
  statement_timeout: 30000,
  // Disabilita i prepared statement per la modalità transaction di PgBouncer
  prepare: false,
});

sql

-- Top 10 query per tempo di esecuzione totale
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

sql

-- Quali indici esistono su questa tabella?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

sql

-- Crea l'indice in modo concorrente (non blocca la tabella)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- Verifica che l'indice sia usato
EXPLAIN (ANALYZE, BUFFERS) <la query lenta>;

sql

-- work_mem: memoria per ordinamenti e hash join (per operazione, non per connessione)
-- Il default è 4MB, che è troppo basso per query complesse
SET work_mem = '64MB';  -- prova questo e riesegui EXPLAIN ANALYZE
 
-- effective_cache_size: dice al planner quanta cache del disco aspettarsi
-- Imposta a ~75% della RAM totale
SHOW effective_cache_size;
 
-- random_page_cost: rapporto tra I/O casuale e I/O sequenziale
-- Il default è 4.0, imposta a 1.1-1.5 per SSD
SHOW random_page_cost;

sql

-- Resetta pg_stat_statements per vedere dati freschi dopo le tue modifiche
SELECT pg_stat_statements_reset();
 
-- Controlla dopo un'ora/un giorno
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

sql

-- 1. Installa pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. Abilita il logging delle query lente (50ms è la mia soglia)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. Impostazioni appropriate per SSD
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. Aumenta work_mem dal default anemico
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. Imposta effective_cache_size al 75% della RAM
-- Per un server da 16GB:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. Imposta shared_buffers al 25% della RAM
-- Per un server da 16GB:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. Migliora la reattività dell'autovacuum
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- Applica le modifiche
SELECT pg_reload_conf();
-- Nota: shared_buffers richiede un riavvio

sql

-- Tabelle che richiedono attenzione
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

La Mentalità del Debug: Non Tirare a Indovinare, Misura

EXPLAIN — Il Progetto

EXPLAIN ANALYZE — La Verità

EXPLAIN (ANALYZE, BUFFERS) — Il Quadro Completo

Opzioni EXPLAIN che Uso Realmente

Leggere i Piani di Query Come un Detective

Seq Scan — La Scansione Completa della Tabella

Index Scan — La Ricerca Mirata

Index Only Scan — Il Santo Graal

Bitmap Scan — La Via di Mezzo

Individuare Stime Errate

Strategia degli Indici: L'Indice Giusto per il Compito Giusto

B-tree — Il Cavallo da Lavoro Predefinito

Indici Covering con INCLUDE

Indici Parziali — Indicizza Solo Ciò Che Conta

Indici GIN — Ricerca Full-Text e JSONB

Indici GiST — Tipi Geometrici e Range

Indici Hash — Di Nicchia ma Utili

Quando gli Indici Fanno Male

Il Problema delle Query N+1

Come si Presenta N+1

Rilevare N+1 nei Log

La Soluzione: Caricamento Eager o JOIN

JOIN vs Query Multiple

Riscritture Comuni delle Query

Subquery vs JOIN vs CTE

EXISTS vs IN vs JOIN

Evitare SELECT *

Funzioni Window vs Subquery

Paginazione Fatta Bene

Statistiche delle Tabelle e Vacuuming

ANALYZE: Aggiorna le Statistiche

Regolazione dell'Autovacuum

Bloat delle Tabelle

Connection Pooling

Perché Hai Bisogno di un Connection Pooler

Configurazione di PgBouncer

Modalità Transaction vs Session

Formula per il Dimensionamento del Pool

Checklist Pratica: I Passi Esatti per Ogni Query Lenta

Passo 1: Ottieni la Query Effettiva

Passo 2: Esegui EXPLAIN (ANALYZE, BUFFERS)

Passo 3: Controlla le Statistiche della Tabella

Passo 4: Controlla gli Indici Esistenti

Passo 5: Crea o Modifica gli Indici

Passo 6: Considera la Riscrittura delle Query

Passo 7: Controlla le Impostazioni del Server

Passo 8: Monitora Dopo la Correzione

Bonus: Quick Win che Applico a Ogni Nuovo Database

Considerazioni Finali

Articoli correlati

How to Compress Images Without Losing Quality: The Complete 2026 Guide

Autenticazione moderna nel 2026: JWT, sessioni, OAuth e passkey