Plans d'execució (EXPLAIN)

Què és un pla d'execució

Quan el SGBD rep una consulta SQL, no l'executa immediatament. Primer la passa pel planificador de consultes (query planner o query optimizer), que és un component intern que analitza les possibles estratègies d'execució i en tria la que estima com a més eficient.

El pla d'execució (execution plan) és el resultat d'aquesta anàlisi: un arbre d'operacions que descriu exactament com el motor executarà la consulta. Pot incloure:

• Quins índexs s'usaran (o si es farà un escaneig seqüencial).
• En quin ordre s'uniràn les taules (join order).
• Quin algoritme de join s'aplicarà (Nested Loop, Hash Join, Merge Join).
• Si es realitzaran ordenacions i on.
• El cost estimat de cada operació.

Llegir i interpretar plans d'execució és l'habilitat fonamental de l'optimització de consultes: sense saber com el motor executa una consulta, és impossible millorar-la de manera informada.

---

Sintaxi EXPLAIN per motor

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Mostra el pla estimat (sense executar)
EXPLAIN SELECT * FROM empleats WHERE departament_id = 10;

-- Executa la consulta i mostra estadístiques reals
EXPLAIN ANALYZE SELECT * FROM empleats WHERE departament_id = 10;

-- Format JSON amb tota la informació disponible
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
    SELECT e.nom, d.nom AS departament
    FROM empleats e
    JOIN departaments d ON e.departament_id = d.id
    WHERE d.ubicació = 'Barcelona';

-- Opcions addicionals útils
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
    SELECT * FROM comandes WHERE estat = 'pendent';

-- Format tabular clàssic
EXPLAIN SELECT * FROM empleats WHERE departament_id = 10;

-- Format JSON (MySQL 5.6+): inclou cost estimat
EXPLAIN FORMAT=JSON SELECT * FROM empleats WHERE departament_id = 10;

-- Amb estadístiques d'E/S reals (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM empleats WHERE departament_id = 10;

-- Consultes DML
EXPLAIN DELETE FROM comandes WHERE estat = 'cancel·lat' AND data_creacio < '2020-01-01';
EXPLAIN UPDATE empleats SET sou = sou * 1.05 WHERE departament_id = 3;

-- Estadístiques d'I/O i temps (text)
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT e.nom, d.nom AS departament
FROM empleats e
JOIN departaments d ON e.departament_id = d.id
WHERE d.ubicació = N'Barcelona';
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- Pla estimat en XML
SET SHOWPLAN_XML ON;
GO
SELECT * FROM empleats WHERE departament_id = 10;
GO
SET SHOWPLAN_XML OFF;
GO

-- En SSMS: Ctrl+M activa el pla real d'execució gràfic
-- En T-SQL: pla real en XML
SET STATISTICS XML ON;
GO
SELECT * FROM empleats WHERE departament_id = 10;
GO
SET STATISTICS XML OFF;
GO

-- Generar el pla (s'emmagatzema a PLAN_TABLE)
EXPLAIN PLAN FOR
    SELECT e.nom, d.nom AS departament
    FROM empleats e
    JOIN departaments d ON e.departament_id = d.id
    WHERE d.ubicació = 'Barcelona';

-- Llegir el pla generat
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Format complet amb estadístiques
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'ALL'));

-- Pla d'una consulta en caché (per SQL_ID)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id_aqui', 0, 'ALLSTATS LAST'));

-- AutoTrace (sessió SQL*Plus)
SET AUTOTRACE ON EXPLAIN STATISTICS;
SELECT * FROM empleats WHERE departament_id = 10;
SET AUTOTRACE OFF;

---

Operacions clau als plans d'execució

Sequential Scan vs Index Scan

Operació	Descripció	Quan és adequada
Seq Scan / Table Scan	Llegeix totes les files de la taula d'inici a fi	Taules petites, o quan es retorna >10-20% de les files
Index Scan	Navega per l'índex B-tree i accedeix al heap per cada fila	Consultes selectives (< 10% de les files)
Index Only Scan	Es resol completament des de l'índex (covering index)	Quan totes les columnes estan a l'índex
Bitmap Index Scan	Crea un mapa de bits de les files que compleixen la condició, llavors accedeix al heap per blocs	Seleccions mitjanament selectives; útil per combinar múltiples índexs

flowchart TD
    Q["Consulta SQL"] --> PL["Planificador"]
    PL --> EST{"Selectivitat?"}
    EST --> |"Alta: < 5% files"| IS["Index Scan\n✓ Ràpid, accés puntual"]
    EST --> |"Mitjana: 5-20%"| BIS["Bitmap Index Scan\n✓ Eficient per blocs"]
    EST --> |"Baixa: > 20% files"| SS["Sequential Scan\n✓ Lectura en bloc és més eficient"]
    IS --> R["Resultat"]
    BIS --> R
    SS --> R

Algoritmes de Join

Algoritme	Descripció	Millor per a
Nested Loop	Per a cada fila de la taula externa, cerca a la taula interna	Taules petites o quan hi ha un índex a la taula interna
Hash Join	Crea una taula de dispersió de la taula menor, llavors proba la major	Taules grans sense índex a la columna de join
Merge Join	Ordena les dues taules per la clau de join i les fusiona linealment	Quan les dues taules ja estan ordenades per la clau de join

Ordenació (Sort)

Les operacions de Sort apareixen quan el planificador ha d'ordenar dades per un ORDER BY, GROUP BY, DISTINCT o per preparar un Merge Join. Si el conjunt de dades supera el work_mem (PostgreSQL) o sort_buffer_size (MySQL), l'ordenació es fa a disc (external sort), la qual és molt lenta.

Operacions Bitmap (PostgreSQL)

Bitmap Heap Scan on empleats
  Recheck Cond: (departament_id = 10)
  ->  Bitmap Index Scan on idx_empleats_dept
        Index Cond: (departament_id = 10)

El Bitmap Index Scan construeix un mapa de bits de les pàgines de disc que contenen files rellevants. El Bitmap Heap Scan llavors accedeix a cada pàgina una sola vegada, fins i tot si diverses files d'aquella pàgina compleixen la condició.

---

Llegir la sortida de PostgreSQL EXPLAIN ANALYZE

Exemple complet amb anotacions:

EXPLAIN (ANALYZE, BUFFERS)
    SELECT e.nom, e.cognom, d.nom AS dept
    FROM empleats e
    JOIN departaments d ON e.departament_id = d.id
    WHERE d.ubicació = 'Barcelona'
      AND e.sou > 30000
    ORDER BY e.cognom;

Sortida típica:

Sort  (cost=1240.52..1243.12 rows=1040 width=72)
      (actual time=45.231..45.412 rows=847 loops=1)       ← ①
  Sort Key: e.cognom
  Sort Method: quicksort  Memory: 128kB                   ← ②
  Buffers: shared hit=512 read=38                         ← ③
  ->  Hash Join  (cost=35.00..1192.10 rows=1040 width=72)
                 (actual time=2.341..44.012 rows=847 loops=1)
        Hash Cond: (e.departament_id = d.id)
        Buffers: shared hit=510 read=38
        ->  Seq Scan on empleats e
              (cost=0.00..1100.00 rows=15000 width=54)
              (actual time=0.021..38.412 rows=12453 loops=1)  ← ④
              Filter: (sou > 30000)
              Rows Removed by Filter: 2547                    ← ⑤
              Buffers: shared hit=500 read=38
        ->  Hash  (cost=28.50..28.50 rows=520 width=22)
              (actual time=1.892..1.893 rows=5 loops=1)       ← ⑥
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Seq Scan on departaments d
                    (cost=0.00..28.50 rows=520 width=22)
                    (actual time=0.012..1.881 rows=5 loops=1)
                    Filter: ((ubicació)::text = 'Barcelona')
                    Rows Removed by Filter: 15

Planning Time: 0.812 ms
Execution Time: 45.687 ms                                  ← ⑦

Anotacions:

Marca	Explicació
①	actual time: temps real en ms. rows: files reals retornades. loops: vegades que s'ha executat aquest node.
②	L'ordenació s'ha fet a memòria (128 kB < work_mem). Si fos a disc, mostraria Sort Method: external merge.
③	shared hit: lectures servides des de la memòria cau. read: lectures físiques de disc. Volem maximitzar hit i minimitzar read.
④	Seq Scan on empleats: escaneig seqüencial. Alerta: si la taula és gran (15.000+ files), considera crear un índex.
⑤	2.547 files han estat llegides però descartades pel filtre sou > 30000. Potencial candidat a índex parcial.
⑥	La taula departaments retorna 5 files (no 520 com estimava el planificador). Estadístiques obsoletes! Caldria ANALYZE departaments.
⑦	Temps total: 45 ms. El temps de planificació (0.8 ms) és molt menor que l'execució, cosa normal.

---

Problemes comuns detectats via EXPLAIN

1. Absència d'índex (Seq Scan en taula gran)

-- Diagnòstic: Seq Scan en una taula de milions de files
-- Seq Scan on comandes (cost=0.00..95000.00 rows=2000000 width=80)
--   Filter: (estat = 'pendent')
-- Rows Removed by Filter: 1998500

-- Solució: crear índex (o índex parcial si 'pendent' és minoritari)
CREATE INDEX idx_comandes_estat ON comandes (estat);
-- O bé (millor si 'pendent' és minoria):
CREATE INDEX idx_comandes_pendents ON comandes (data_creacio) WHERE estat = 'pendent';

2. Tipus de join inadequat

-- Diagnòstic: Nested Loop amb taula gran a la part interna
-- Nested Loop (cost=0.00..250000.00 rows=50000 ...)
--   -> Seq Scan on comandes (100.000 files)
--   -> Index Scan on productes (per cada fila de comandes!)

-- Solució: forçar Hash Join si les estadístiques son incorrectes
-- PostgreSQL:
SET enable_nestloop = off;
-- O afegir índex a la columna de join de la taula interna

3. Estadístiques obsoletes

El planificador basa les seves decisions en estadístiques de distribució de dades. Si les dades han canviat molt des de l'última anàlisi, les estimacions seran errònies i el planificador pot prendre decisions equivocades.

Símptoma: La diferència entre rows=520 (estimació) i rows=5 (real) és molt gran, com en l'exemple anterior.

---

Actualització d'estadístiques

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Analitzar una taula específica
ANALYZE empleats;

-- Analitzar totes les taules de la base de dades
ANALYZE;

-- Analitzar amb informació detallada
ANALYZE VERBOSE empleats;

-- Veure quan s'ha analitzat per última vegada cada taula
SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY last_analyze NULLS FIRST;

-- Configurar freqüència de l'autovacuum/autoanalyze per a una taula
ALTER TABLE comandes SET (
    autovacuum_analyze_scale_factor = 0.01,  -- anàlisi al 1% de canvis (default: 20%)
    autovacuum_analyze_threshold = 100
);

-- Analitzar una taula
ANALYZE TABLE empleats;

-- Analitzar múltiples taules
ANALYZE TABLE empleats, departaments, comandes;

-- Veure estadístiques actuals
SELECT table_name, table_rows, avg_row_length, data_length, update_time
FROM information_schema.TABLES
WHERE table_schema = DATABASE();

-- Histogrames (MySQL 8.0+): milloren l'estimació de selectivitat
ANALYZE TABLE empleats UPDATE HISTOGRAM ON sou, departament_id WITH 100 BUCKETS;

-- Veure histogrames creats
SELECT * FROM information_schema.COLUMN_STATISTICS
WHERE table_name = 'empleats';

-- Actualitzar estadístiques d'una taula
UPDATE STATISTICS empleats;

-- Actualitzar un índex específic
UPDATE STATISTICS empleats idx_empleats_departament;

-- Actualitzar amb mostra completa (més lent, més precís)
UPDATE STATISTICS empleats WITH FULLSCAN;

-- Actualitzar totes les estadístiques de la BD
EXEC sp_updatestats;

-- Veure estadístiques
SELECT name, last_updated, rows, rows_sampled
FROM sys.stats
CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id)
WHERE object_id = OBJECT_ID('empleats');

-- Estadístiques d'una taula
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NOM', 'EMPLEATS');

-- Amb mostra del 100% de les dades (molt precís)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname   => 'SCHEMA_NOM',
    tabname   => 'EMPLEATS',
    estimate_percent => 100,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

-- Estadístiques de tota l'esquema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NOM');

-- Estadístiques de tota la base de dades
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

-- Veure quan s'han actualitzat
SELECT table_name, last_analyzed, num_rows, blocks
FROM user_tables
ORDER BY last_analyzed NULLS FIRST;

---

Consell pràctic

Sempre executa EXPLAIN ANALYZE (no just EXPLAIN) en entorns de desenvolupament per veure els temps reals. En producció, usa EXPLAIN (sense ANALYZE) si la consulta és molt costosa i no vols executar-la sencera.

EXPLAIN ANALYZE modifica dades

EXPLAIN ANALYZE executa la consulta realment. Si l'executeu sobre un DELETE o UPDATE, les dades seran modificades (o eliminades). Per evitar-ho, embolcalla'l en una transacció i fes ROLLBACK:

BEGIN;
EXPLAIN ANALYZE DELETE FROM comandes WHERE estat = 'cancel·lat';
ROLLBACK;

---

Miniactivitat — AC0503

Objectiu: Practicar la lectura de plans d'execució i la identificació de problemes de rendiment.

Preparació:

-- Crea la taula i insereix dades (PostgreSQL)
CREATE TABLE productes (
    id          SERIAL PRIMARY KEY,
    categoria   VARCHAR(50),
    nom         VARCHAR(100),
    preu        DECIMAL(10,2),
    estoc       INT,
    actiu       BOOLEAN DEFAULT TRUE
);

INSERT INTO productes (categoria, nom, preu, estoc, actiu)
SELECT
    ('Cat-' || (random()*20)::INT),
    ('Producte-' || i),
    (random()*500 + 5)::DECIMAL(10,2),
    (random()*1000)::INT,
    random() > 0.1
FROM generate_series(1, 200000) i;

ANALYZE productes;

Tasques:

1. Executa EXPLAIN ANALYZE SELECT * FROM productes WHERE preu > 400; i identifica el tipus d'escaneig. Quan creus que és correcte usar un Seq Scan aquí?

2. Crea un índex idx_productes_preu i repeteix la consulta. El planificador l'utilitza? Per què pot ser que NO l'usi?

3. Prova EXPLAIN ANALYZE SELECT * FROM productes WHERE preu > 490;. Canvia el pla d'execució? Raona la diferència.

4. Executa EXPLAIN ANALYZE SELECT categoria, COUNT(*), AVG(preu) FROM productes GROUP BY categoria;. Identifica l'algoritme d'agregació.

5. Modifica la consulta anterior per ordenar els resultats per AVG(preu) DESC. Apareix un node Sort? Quin és el seu cost?

6. Busca la diferència entre cost estimat i temps real per a cada node. On hi ha la major discrepància? Executa ANALYZE productes i repeteix. Millora?

Lliura: Captures dels plans d'execució i un breu informe (mínim 200 paraules) explicant les teves observacions.