SQL Avanzato — Cheat Sheet

Tag: sql, postgresql, database, query-optimization, indexing, window-functions

Livello: avanzato

TL;DR: Strategie avanzate di interrogazione, window functions, CTE ricorsive, piani di esecuzione e ottimizzazione degli indici per query ad alte prestazioni.

Concetti chiave

Window functions calcolano aggregati su partizioni senza collassare le singole righe.

Common Table Expressions (CTE) migliorano la leggibilità e abilitano l'iterazione su grafi con RECURSIVE.

EXPLAIN ANALYZE mostra i costi stimati a confronto con tempi di esecuzione e buffer reali.

Indici B-Tree gestiscono uguaglianze e range; indici parziali riducono storage e overhead di scrittura.

Indici compositi seguono la regola del prefisso più a sinistra per filtrare efficacemente.

JOIN su grandi volumi possono impiegare Nested Loop, Hash Join o Merge Join.

Le transazioni richiedono la scelta consapevole del livello di isolamento per bilanciare concorrenza e anomalie.

Le subquery correlate possono causare problemi prestazionali N+1 a livello di motore relazionale.

Domande tipiche e risposte pronte

Q: Qual è la differenza principale tra le funzioni ROW_NUMBER(), RANK() e DENSE_RANK()?R: ROW_NUMBER assegna numeri progressivi unici; RANK assegna lo stesso valore ai duplicati lasciando buchi successivi, mentre DENSE_RANK assegna lo stesso valore senza saltare numeri.

Q: Quando conviene usare una CTE rispetto a una subquery o tabella temporanea?R: Uso le CTE per strutturare query complesse leggibili o per query ricorsive; passo a tabelle temporanee se il set intermedio è pesante e necessita di indicizzazione dedicata.

Q: Come affronti una query lenta in produzione?R: Eseguo EXPLAIN ANALYZE per individuare Sequential Scan non voluti, discordanze nelle stime delle righe o operazioni di sort su disco, valutando poi indici mirati o riscrittura.

Insidie e errori comuni

Applicare funzioni o cast sulle colonne nella clausola WHERE disabilita l'uso degli indici standard.

Dimenticare la condizione di terminazione in una CTE RECURSIVE genera cicli infiniti.

Usare SELECT * nelle query con join e viste appesantisce il traffico di rete e impedisce index-only scans.

Confondere COUNT(colonna) con COUNT(*), ignorando che il primo esclude i valori NULL.

Trascurare l'impatto di troppi indici sulle operazioni di INSERT, UPDATE e DELETE.

Snippet e comandiSQL

```-- Window function: running total e ranking per dipartimento

SELECT

id,

department_id,

salary,

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_dept,

SUM(salary) OVER (PARTITION BY department_id ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total

FROM employees;

-- CTE Ricorsiva: attraversamento gerarchia organizzativa

WITH RECURSIVE org_tree AS (

SELECT id, manager_id, full_name, 1 AS level

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.manager_id, e.full_name, ot.level + 1

FROM employees e

INNER JOIN org_tree ot ON e.manager_id = ot.id

)

SELECT * FROM org_tree;

-- Analisi del piano di esecuzione con buffer I/O

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

SELECT c.name, COUNT(o.id) AS total_orders

FROM customers c

LEFT JOIN orders o ON o.customer_id = c.id

WHERE c.created_at >= '2026-01-01'

GROUP BY c.name;

```

Da ripassare

[ ] Regole di precedenza e operatività dei predicati sargable

[ ] Meccanismi di lock e differenze tra READ COMMITTED e REPEATABLE READ

[x] Sintassi e partizionamento delle window functions

[x] Utilizzo di indici parziali e indici su espressioni