Optimització de queries

La lectura i interpretació del pla d'execució EXPLAIN ANALYZE és la habilitat fonamental per diagnosticar i optimitzar consultes SQL lentes sobre grans volums de dades. Sense entendre el pla, qualsevol optimització és una aposta a cegues.

Com funciona l'optimitzador d'un SGBD

Quan un SGBD rep una consulta SQL, no l'executa directament. Primer la passa per l'optimitzador (query planner), que genera un pla d'execució òptim seguint aquests passos:

Anàlisi sintàctica i semàntica: verifica que la consulta és correcta i resol els noms de taules i columnes.
Generació de plans candidats: enumera diverses maneres d'executar la consulta (quin índex usar, quin algorisme de JOIN, en quin ordre llegir les taules).
Estimació de costos: per a cada pla candidat, estima el cost total basant-se en les estadístiques de la base de dades (ANALYZE).
Selecció del pla mínim cost: tria el pla amb el cost estimat més baix.

El model de costos

Cada motor estima el cost d'un pla a partir d'un model propi, normalment basat en el cost d'accedir a disc (seqüencial vs aleatori), el cost de CPU per fila processada i la memòria disponible per a cache. La filosofia és semblant als 4 motors, però el grau de control manual que cada un ofereix sobre aquest model és molt diferent, com es veu a continuació:

PostgreSQLMySQL / MariaDBSQL ServerOracle

El cost s'expressa en unitats abstractes basades en dos paràmetres clau de postgresql.conf:

seq_page_cost (per defecte 1.0): cost de llegir una pàgina del disc en accés seqüencial.
random_page_cost (per defecte 4.0): cost de llegir una pàgina en accés aleatori (salt de disc).

En sistemes amb SSD, random_page_cost s'hauria de reduir (típicament a 1.1–1.5), perquè els discos SSD no pateixen la penalització del seek mecànic. Si el valor és massa alt, PostgreSQL preferirà Seq Scans innecessaris.

-- Ajust recomanat per a PostgreSQL sobre SSD
ALTER SYSTEM SET random_page_cost = 1.2;
ALTER SYSTEM SET effective_cache_size = '8GB';  -- RAM disponible per a cache del SO
SELECT pg_reload_conf();

MySQL/MariaDB no exposa paràmetres de cost de pàgina equivalents a seq_page_cost/random_page_cost de forma directa: l'optimitzador intern (cost-based optimizer des de MySQL 5.7) no permet aquest tipus d'ajust manual fi. El control real que es té és sobre la mida de la cache de dades i la freqüència d'actualització d'estadístiques:

-- innodb_buffer_pool_size: mida de la cache de dades a memòria
-- (equivalent conceptual a effective_cache_size de PostgreSQL)
SET GLOBAL innodb_buffer_pool_size = 8589934592; -- 8GB

-- Actualitzar estadístiques d'una taula concreta
ANALYZE TABLE vendes;

SQL Server no té un equivalent directe a random_page_cost. El Cardinality Estimator (CE) i el cost-based optimizer fan servir estadístiques i un model de costos intern que no és parametritzable manualment de la mateixa manera que a PostgreSQL o Oracle. La influència que es té és indirecta: hints de consulta, OPTION (RECOMPILE) o ajustant la memòria disponible per a la cache de pàgines:

-- Ajustar la memòria màxima del servidor (afecta la cache de pàgines)
EXEC sp_configure 'max server memory (MB)', 8192;
RECONFIGURE;

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

Oracle sí té control explícit sobre el model de costos, similar a PostgreSQL, mitjançant les System Statistics, que capturen el rendiment real de l'I/O i la CPU del sistema:

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');
-- ... esperar un període representatiu de càrrega ...
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

ALTER SYSTEM SET optimizer_index_cost_adj = 20; -- més baix = afavoreix índexs (SSD)
EXEC DBMS_STATS.GATHER_TABLE_STATS('ESQUEMA', 'VENDES');

Nivells de control molt diferents segons el motor

PostgreSQL i Oracle permeten ajustar explícitament el model de costos de l'optimitzador. MySQL/MariaDB i SQL Server, en canvi, automatitzen molt més aquesta part i ofereixen poc marge d'ajust manual directe: cal confiar en la qualitat de les estadístiques i en la memòria disponible per a la cache.

Totes les estadístiques s'actualitzen automàticament en segon pla (per autovacuum a PostgreSQL, per processos equivalents als altres motors), però en càrregues d'escriptura intensa pot ser necessari forçar-les i consultar-les manualment:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Actualitzar estadístiques d'una taula concreta
ANALYZE vendes;

-- Veure les estadístiques actuals d'una columna
SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'vendes' AND attname = 'pais';
-- correlation propera a 1.0 = dades físicament ordenades (bon candidat per a BRIN)

-- Actualitzar estadístiques d'una taula concreta
ANALYZE TABLE vendes;

-- Veure estadístiques de cardinalitat dels índexs
SELECT index_name, column_name, cardinality
FROM information_schema.statistics
WHERE table_name = 'vendes' AND table_schema = DATABASE();

MySQL no exposa una mètrica de correlació física equivalent a correlation de PostgreSQL.

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

-- Veure l'histograma d'una estadística concreta
DBCC SHOW_STATISTICS ('vendes', idx_vendes_pais) WITH HISTOGRAM;

-- Actualitzar estadístiques d'una taula concreta
EXEC DBMS_STATS.GATHER_TABLE_STATS('ESQUEMA', 'VENDES');

-- Veure les estadístiques actuals d'una columna
SELECT column_name, num_distinct, histogram
FROM user_tab_col_statistics
WHERE table_name = 'VENDES' AND column_name = 'PAIS';

EXPLAIN: llegir el pla sense executar

EXPLAIN mostra el pla d'execució estimat sense executar la consulta. Útil per a consultes molt lentes on no volem esperar.

PostgreSQLMySQL / MariaDBSQL ServerOracle

EXPLAIN SELECT * FROM vendes WHERE client_id = 4231;

Sortida típica:

Index Scan using idx_vendes_client on vendes
  (cost=0.56..458.21 rows=142 width=56)
  Index Cond: (client_id = 4231)

EXPLAIN SELECT * FROM vendes WHERE client_id = 4231;

Retorna una taula amb columnes id, select_type, table, type, possible_keys, key, rows, Extra. El valor de type indica el mètode d'accés:

type = const o ref: ús d'índex (selectivitat alta).
type = ALL: escaneig complet de la taula, equivalent a Seq Scan de PostgreSQL.

SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM vendes WHERE client_id = 4231;
GO

A la pràctica, és més habitual consultar el pla estimat gràfic a SSMS (Ctrl+L) en comptes del format text. Operadors típics que hi apareixen: "Clustered Index Seek", "Index Scan", "Key Lookup".

EXPLAIN PLAN FOR
SELECT * FROM vendes WHERE client_id = 4231;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

El resultat mostra columnes Operation, Cost, Cardinality (files estimades) i Bytes.

Els noms de camp següents (cost, rows, width) corresponen al format de sortida de PostgreSQL. Cada node del pla mostra:

cost=inici..total: cost estimat. El primer número és el startup cost (cost fins a retornar la primera fila); el segon és el total cost (cost fins a retornar totes les files).
rows: nombre de files estimades que retornarà el node.
width: amplada mitjana de cada fila en bytes.

Als altres motors la mateixa informació es presenta amb noms diferents, tal com s'ha vist a les pestanyes anteriors: la columna rows i el camp Extra a l'EXPLAIN de MySQL/MariaDB, els operadors del pla gràfic d'SSMS a SQL Server, i les columnes Cost, Cardinality i Bytes de DBMS_XPLAN.DISPLAY a Oracle.

Llegir un pla niuat

Els plans s'llegeixen de dins cap a fora (de la indentació més profunda cap a la superfície):

PostgreSQLMySQL / MariaDBSQL ServerOracle

EXPLAIN SELECT v.*, c.nom
FROM vendes v
JOIN clients c ON c.id = v.client_id
WHERE v.data_venda >= '2026-01-01';

Hash Join  (cost=1234.00..89234.00 rows=50000 width=120)
  Hash Cond: (v.client_id = c.id)
  ->  Seq Scan on vendes v  (cost=0.00..75000.00 rows=50000 width=56)
        Filter: (data_venda >= '2026-01-01')
  ->  Hash  (cost=800.00..800.00 rows=34400 width=64)
        ->  Seq Scan on clients c  (cost=0.00..800.00 rows=34400 width=64)

S'executa primer: 1) Seq Scan sobre vendes, 2) Seq Scan sobre clients per construir la taula hash, 3) Hash Join combinant els resultats.

EXPLAIN FORMAT=TREE
SELECT v.*, c.nom
FROM vendes v
JOIN clients c ON c.id = v.client_id
WHERE v.data_venda >= '2026-01-01';

El format TREE (disponible des de MySQL 8.0.16) mostra una estructura jeràrquica similar a la de PostgreSQL, amb indentació per nivells i el cost estimat de cada node.

El pla gràfic mostra els operadors en forma d'arbre i es llegeix de dreta a esquerra i de dalt a baix. Els operadors equivalents més habituals són "Hash Match" (per a hash joins), "Nested Loops" i "Merge Join". S'obté amb el pla estimat (Ctrl+L) o l'execucional (Ctrl+M) a SSMS.

EXPLAIN PLAN FOR
SELECT v.*, c.nom
FROM vendes v
JOIN clients c ON c.id = v.client_id
WHERE v.data_venda >= DATE '2026-01-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

DBMS_XPLAN.DISPLAY mostra una estructura amb columnes Id, Operation i sagnat segons la profunditat. Es llegeix de dins (més sagnat) cap a fora, igual que a PostgreSQL.

EXPLAIN ANALYZE: temps reals d'execució

EXPLAIN ANALYZE executa la consulta i afegeix al pla els temps reals i el nombre real de files processades.

EXPLAIN ANALYZE executa la consulta

Per a DELETE, UPDATE o INSERT, emboliqueu-ho en una transacció i feu rollback: BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;

PostgreSQLMySQL / MariaDBSQL ServerOracle

EXPLAIN ANALYZE SELECT * FROM vendes WHERE client_id = 4231;

Index Scan using idx_vendes_client on vendes
  (cost=0.56..458.21 rows=142 width=56)
  (actual time=0.045..1.234 rows=142 loops=1)
  Index Cond: (client_id = 4231)
Planning Time: 0.312 ms
Execution Time: 1.289 ms

Des de MySQL 8.0.18, EXPLAIN ANALYZE executa la consulta i retorna un format d'arbre textual, conceptualment molt similar al de PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM vendes WHERE client_id = 4231;

La sortida inclou actual time=X..Y rows=N loops=M per a cada node de l'arbre, igual que el actual time de PostgreSQL.

SQL Server no té una comanda "EXPLAIN ANALYZE" directa. L'equivalent s'aconsegueix activant SET STATISTICS TIME, IO ON abans de la consulta (dona temps de CPU/transcorregut i lectures lògiques/físiques per taula), combinat amb el "Actual Execution Plan" (Ctrl+M a SSMS), que afegeix files reals processades a cada operador del pla gràfic:

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SELECT * FROM vendes WHERE client_id = 4231;

Cal l'hint GATHER_PLAN_STATISTICS i després consultar el cursor amb DISPLAY_CURSOR:

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM vendes WHERE client_id = 4231;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
-- Mostra E-Rows (estimades) vs A-Rows (reals) costat a costat

Els camps addicionals (format de sortida de PostgreSQL i, de forma molt similar, del EXPLAIN ANALYZE de MySQL/MariaDB 8.0.18+):

actual time=inici..fi: temps real en mil·lisegons fins a la primera fila i fins a l'última.
rows: nombre real de files processades. Si difereix molt de rows estimat, les estadístiques estan desactualitzades.
loops: quantes vegades s'ha executat aquest node (rellevant en Nested Loops).

A SQL Server, la informació equivalent (temps i lectures reals) prové de SET STATISTICS TIME, IO ON combinat amb el pla d'execució real (Ctrl+M); a Oracle, DISPLAY_CURSOR(..., 'ALLSTATS LAST') mostra E-Rows (estimades) i A-Rows (reals) costat a costat per a cada operador.

La trampa de loops

En un Nested Loop, el node interior s'executa tantes vegades com files retorna el node exterior. El actual time mostrat és el temps per iteració, no el total:

Nested Loop  (actual time=0.1..150.0 rows=10000 loops=1)
  ->  Seq Scan on comandes  (actual time=0.05..50.0 rows=1000 loops=1)
  ->  Index Scan on linies  (actual time=0.01..0.08 rows=10 loops=1000)

El temps total del node interior és 0.08 ms × 1000 = 80 ms, no 0.08 ms.

Aquesta convenció és pròpia de PostgreSQL i MySQL

La multiplicació manual actual time × loops és necessària a PostgreSQL i al EXPLAIN ANALYZE de MySQL/MariaDB (que reprodueix el mateix format). A SQL Server, el pla d'execució real mostra directament el nombre total de files processades per operador ("Actual Number of Rows", ja agregat). A Oracle, DISPLAY_CURSOR amb ALLSTATS LAST també mostra A-Rows ja acumulat per a totes les execucions del node, sense necessitat de multiplicar manualment.

EXPLAIN (ANALYZE, BUFFERS): lectures de cache vs disc

Afegint BUFFERS, el pla mostra quantes pàgines de dades s'han llegit de la cache de PostgreSQL (shared buffers) i quantes del disc:

PostgreSQLMySQL / MariaDBSQL ServerOracle

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM vendes WHERE pais = 'ES' AND data_venda >= '2026-01-01';

Bitmap Heap Scan on vendes  (actual time=45.2..890.3 rows=2500000 width=56)
  Recheck Cond: (pais = 'ES')
  Buffers: shared hit=12000 read=85000
  ->  Bitmap Index Scan on idx_vendes_pais
        Index Cond: (pais = 'ES')
        Buffers: shared hit=250 read=1200

shared hit: pàgines servides des de la cache (ràpid).
read: pàgines llegides del disc (lent). Un valor alt de read indica que la taula o l'índex no cap a la cache; es pot considerar augmentar shared_buffers.

No hi ha una opció integrada dins EXPLAIN equivalent a BUFFERS. Cal consultar-ho per separat:

-- Estadístiques del buffer pool d'InnoDB (inclou hit rate)
SHOW ENGINE INNODB STATUS;

-- Lectures lògiques/físiques per taula via Performance Schema
SELECT * FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_NAME = 'vendes';

SET STATISTICS IO ON ja mostra logical reads (cache) i physical reads (disc) per taula directament al missatge de sortida, de forma molt similar al BUFFERS de PostgreSQL:

SET STATISTICS IO ON;
SELECT * FROM vendes WHERE pais = 'ES' AND data_venda >= '2026-01-01';

L'hint GATHER_PLAN_STATISTICS combinat amb DISPLAY_CURSOR(..., 'ALLSTATS LAST') ja inclou la columna Buffers (blocs lògics llegits) directament al pla, igual que EXPLAIN (ANALYZE, BUFFERS) de PostgreSQL:

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM vendes WHERE pais = 'ES' AND data_venda >= DATE '2026-01-01';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Nodes principals del pla d'execució

Node (PostgreSQL)	Descripció	MySQL/MariaDB	SQL Server	Oracle
`Seq Scan`	Llegeix tota la taula seqüencialment. Normal per a taules petites o quan es retorna >10-20% de les files.	`type = ALL`	"Table Scan" / "Clustered Index Scan"	`TABLE ACCESS FULL`
`Index Scan`	Segueix l'índex fila per fila. Òptim per a selectivitat alta (poques files).	`type = ref` / `range`	"Index Seek"	`INDEX RANGE SCAN`
`Bitmap Index Scan`	Recull punteres de l'índex en un bitmap i accedeix al disc en ordre físic. Millor que Index Scan per a selectivitat mitjana.	sense equivalent directe (optimitzador intern diferent)	"Index Seek" + "Key Lookup" combinats	`BITMAP INDEX SCAN`
`Hash Join`	Construeix una taula hash del conjunt petit i en fa cerca per cada fila del gran. Òptim quan un dels conjunts cap a memòria.	hash join (suport des de MySQL 8.0.18)	"Hash Match"	`HASH JOIN`
`Merge Join`	Requereix que ambdós conjunts estiguin ordenats. Eficient per a conjunts grans ja ordenats per l'índex.	rar, l'optimitzador hi recorre poc	"Merge Join"	`MERGE JOIN`
`Nested Loop`	Per a cada fila exterior, cerca les files interiors. Excel·lent quan la selectivitat interior és molt alta.	algorisme per defecte històric de MySQL per a JOINs	"Nested Loops"	`NESTED LOOPS`
`Sort`	Ordena un conjunt. Si no cap a `work_mem`, fa un mergesort extern al disc (molt lent).	"Using filesort" a `Extra`	"Sort"	`SORT ORDER BY`
`Aggregate`	Calcula funcions d'agrupació (`SUM`, `COUNT`, `AVG`).	"Using temporary" / agregació pròpia	"Stream Aggregate"	`SORT AGGREGATE`
`Hash Aggregate`	Versió hash de l'agregació, usar quan els grups caben a memòria.	suport limitat, sovint cau a "Using temporary"	"Hash Match (Aggregate)"	`HASH GROUP BY`

MySQL i Nested Loop

Històricament, MySQL/MariaDB resol pràcticament tots els JOINs amb una variant de Nested Loop Join (Block Nested Loop quan no hi ha índex disponible). El suport de Hash Join és relativament recent (MySQL 8.0.18+) i només s'activa en certes condicions, a diferència de PostgreSQL, SQL Server i Oracle, que trien lliurement entre els tres algorismes segons el cost estimat.

Eines de monitoratge

pg_stat_statements i equivalents

Extensió que acumula estadístiques de totes les consultes executades: temps total, temps mig, nombre d'execucions, files retornades.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Activar l'extensió (requereix reinici si no és al shared_preload_libraries)
-- A postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Les 10 consultes que consumeixen més temps total
SELECT
    round(total_exec_time::numeric, 2)  AS temps_total_ms,
    calls                               AS execucions,
    round(mean_exec_time::numeric, 2)   AS temps_mig_ms,
    round(rows / calls::numeric, 1)     AS files_per_exec,
    left(query, 120)                    AS consulta
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reiniciar estadístiques acumulades
SELECT pg_stat_statements_reset();

El Performance Schema, en concret la vista sys.statement_analysis o la taula performance_schema.events_statements_summary_by_digest, acumula estadístiques de totes les consultes (temps total, mitjana, execucions) — equivalent funcional a pg_stat_statements:

SELECT digest_text, count_star AS execucions,
       round(avg_timer_wait/1000000000, 2) AS temps_mig_ms,
       round(sum_timer_wait/1000000000, 2) AS temps_total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

Query Store (activat per defecte des de SQL Server 2016+) és l'equivalent directe i fins i tot més complet, ja que guarda l'historial de plans i permet "forçar" un pla antic:

ALTER DATABASE bigdata SET QUERY_STORE = ON;

SELECT qt.query_sql_text, rs.avg_duration, rs.count_executions
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
ORDER BY rs.avg_duration DESC;

AWR (Automatic Workload Repository) i ASH (Active Session History), accessibles via vistes com DBA_HIST_SQLSTAT o v$sqlstats, en compleixen la funció. Cal tenir en compte que l'accés complet a AWR/ASH via Enterprise Manager requereix llicència del Diagnostics Pack, no inclosa a totes les edicions:

SELECT sql_text, executions, elapsed_time/1000000 AS temps_total_s,
       (elapsed_time/executions)/1000000 AS temps_mig_s
FROM v$sqlstats
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

auto_explain i equivalents

Extensió/mecanisme que registra automàticament al log el pla d'execució de les consultes que superen un llindar de temps:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- A postgresql.conf o per sessió:
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '1s';   -- registra consultes > 1 segon
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;

El slow query log (slow_query_log + long_query_time) compleix una funció similar, encara que sense el pla d'execució complet, només la consulta i el seu temps:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- registra consultes > 1 segon

Extended Events és l'eina moderna equivalent, capaç de capturar consultes que superen un llindar de durada:

CREATE EVENT SESSION consultes_lentes ON SERVER
ADD EVENT sqlserver.sql_statement_completed
    (WHERE duration > 1000000) -- microsegons, equival a 1 segon
ADD TARGET package0.event_file(SET filename = 'consultes_lentes.xel');

ALTER EVENT SESSION consultes_lentes ON SERVER STATE = START;

Es pot configurar trace SQL a nivell de sessió o, més modernament, fer servir els llindars d'ASH/AWR combinats amb alertes de DBMS_SERVER_ALERT per detectar consultes lentes de forma proactiva.

Tècniques de reescriptura de consultes

Substituir subconsultes correlacionades per JOINs

Una subconsulta correlacionada s'executa una vegada per cada fila del resultat exterior, resultant en milers o milions d'execucions. Aquest problema i la seva solució són idèntics conceptualment als quatre motors: és una qüestió d'SQL relacional, no específica d'un motor concret.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Lent: subconsulta correlacionada (N execucions de la subconsulta)
SELECT nom, (
    SELECT SUM(total) FROM comandes c WHERE c.client_id = cl.id
) AS total_compres
FROM clients cl;

-- Ràpid: JOIN amb agregació
SELECT cl.nom, COALESCE(agg.total_compres, 0) AS total_compres
FROM clients cl
LEFT JOIN (
    SELECT client_id, SUM(total) AS total_compres
    FROM comandes
    GROUP BY client_id
) agg ON agg.client_id = cl.id;

-- Lent: subconsulta correlacionada (N execucions de la subconsulta)
SELECT nom, (
    SELECT SUM(total) FROM comandes c WHERE c.client_id = cl.id
) AS total_compres
FROM clients cl;

-- Ràpid: JOIN amb agregació
SELECT cl.nom, COALESCE(agg.total_compres, 0) AS total_compres
FROM clients cl
LEFT JOIN (
    SELECT client_id, SUM(total) AS total_compres
    FROM comandes
    GROUP BY client_id
) agg ON agg.client_id = cl.id;

-- Lent: subconsulta correlacionada (N execucions de la subconsulta)
SELECT nom, (
    SELECT SUM(total) FROM comandes c WHERE c.client_id = cl.id
) AS total_compres
FROM clients cl;

-- Ràpid: JOIN amb agregació
SELECT cl.nom, COALESCE(agg.total_compres, 0) AS total_compres
FROM clients cl
LEFT JOIN (
    SELECT client_id, SUM(total) AS total_compres
    FROM comandes
    GROUP BY client_id
) agg ON agg.client_id = cl.id;

-- Lent: subconsulta correlacionada (N execucions de la subconsulta)
SELECT nom, (
    SELECT SUM(total) FROM comandes c WHERE c.client_id = cl.id
) AS total_compres
FROM clients cl;

-- Ràpid: JOIN amb agregació
SELECT cl.nom, COALESCE(agg.total_compres, 0) AS total_compres
FROM clients cl
LEFT JOIN (
    SELECT client_id, SUM(total) AS total_compres
    FROM comandes
    GROUP BY client_id
) agg ON agg.client_id = cl.id;

CTEs: materialitzades vs inline

A partir de PostgreSQL 12, les CTEs sense recursió es pleguen (inline) per defecte, permetent a l'optimitzador integrar-les al pla global. Si necessiteu que la CTE s'executi exactament una vegada com a barrera d'optimització, useu MATERIALIZED:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- CTE inline (per defecte a PG12+): l'optimitzador pot empènyer filtres dins
WITH vendes_recents AS (
    SELECT * FROM vendes WHERE data_venda >= '2026-01-01'
)
SELECT pais, COUNT(*) FROM vendes_recents GROUP BY pais;

-- CTE materialitzada: s'executa exactament una vegada i el resultat es guarda
WITH MATERIALIZED vendes_recents AS (
    SELECT * FROM vendes WHERE data_venda >= '2026-01-01'
)
SELECT pais, COUNT(*) FROM vendes_recents GROUP BY pais;

Useu MATERIALIZED quan la CTE conté una subconsulta cara que es referencia múltiples vegades, o quan voleu garantir que s'executa exactament una vegada per efectes secundaris.

MySQL/MariaDB (8.0.14+) suporta WITH ... AS (...) però no té la clàusula MATERIALIZED/NOT MATERIALIZED de PostgreSQL. Una CTE no recursiva sempre es comporta com a "inline": l'optimitzador la pot plegar al pla global sense que l'usuari pugui forçar el comportament contrari.

WITH vendes_recents AS (
    SELECT * FROM vendes WHERE data_venda >= '2026-01-01'
)
SELECT pais, COUNT(*) FROM vendes_recents GROUP BY pais;

SQL Server suporta WITH ... AS (...) (Common Table Expressions), però tampoc té control de materialització explícit. Les CTEs es tracten sempre com a vistes en línia que l'optimitzador pot expandir o no segons el seu propi criteri, sense cap paraula clau que ho permeti forçar:

WITH vendes_recents AS (
    SELECT * FROM vendes WHERE data_venda >= '2026-01-01'
)
SELECT pais, COUNT(*) FROM vendes_recents GROUP BY pais;

Oracle té un control equivalent (i fins i tot anterior a PostgreSQL): els hints /*+ MATERIALIZE */ i /*+ INLINE */ dins la clàusula WITH (subquery factoring) permeten forçar explícitament el comportament:

WITH vendes_recents AS (
    SELECT /*+ MATERIALIZE */ * FROM vendes WHERE data_venda >= DATE '2026-01-01'
)
SELECT pais, COUNT(*) FROM vendes_recents GROUP BY pais;

Materialització de CTEs: només PostgreSQL i Oracle ho controlen explícitament

MySQL/MariaDB i SQL Server no donen a l'usuari cap mecanisme per forçar si una CTE es materialitza o es plega; només PostgreSQL (MATERIALIZED) i Oracle (hints MATERIALIZE/INLINE) ofereixen aquest control explícit.

Evitar funcions en WHERE que impedeixen l'ús d'índexs

Aplicar una funció sobre una columna indexada al WHERE impedeix l'ús de l'índex B-Tree (tret que existeixi un índex funcional), perquè el motor ha d'avaluar la funció per a cada fila abans de poder comparar-la. Aquest problema i la seva solució (reescriure com a rang) són idèntics conceptualment als quatre motors; només canvia la sintaxi de la funció d'extracció de data (EXTRACT a PostgreSQL/Oracle, YEAR() a MySQL/SQL Server).

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Lent: la funció sobre la columna impedeix l'ús de l'índex B-Tree
SELECT * FROM vendes WHERE EXTRACT(year FROM data_venda) = 2026;

-- Ràpid: rang equivalent que sí permet usar l'índex
SELECT * FROM vendes
WHERE data_venda >= '2026-01-01' AND data_venda < '2027-01-01';

-- Lent: càlcul sobre columna indexada
SELECT * FROM productes WHERE preu * 1.21 > 100;

-- Ràpid: reescriure per aïllar la columna
SELECT * FROM productes WHERE preu > 100 / 1.21;

-- Lent: la funció sobre la columna impedeix l'ús de l'índex B-Tree
SELECT * FROM vendes WHERE YEAR(data_venda) = 2026;

-- Ràpid: rang equivalent que sí permet usar l'índex
SELECT * FROM vendes
WHERE data_venda >= '2026-01-01' AND data_venda < '2027-01-01';

-- Lent: càlcul sobre columna indexada
SELECT * FROM productes WHERE preu * 1.21 > 100;

-- Ràpid: reescriure per aïllar la columna
SELECT * FROM productes WHERE preu > 100 / 1.21;

-- Lent: la funció sobre la columna impedeix l'ús de l'índex B-Tree
SELECT * FROM vendes WHERE YEAR(data_venda) = 2026;

-- Ràpid: rang equivalent que sí permet usar l'índex
SELECT * FROM vendes
WHERE data_venda >= '2026-01-01' AND data_venda < '2027-01-01';

-- Lent: càlcul sobre columna indexada
SELECT * FROM productes WHERE preu * 1.21 > 100;

-- Ràpid: reescriure per aïllar la columna
SELECT * FROM productes WHERE preu > 100 / 1.21;

-- Lent: la funció sobre la columna impedeix l'ús de l'índex B-Tree
SELECT * FROM vendes WHERE EXTRACT(YEAR FROM data_venda) = 2026;

-- Ràpid: rang equivalent que sí permet usar l'índex
SELECT * FROM vendes
WHERE data_venda >= DATE '2026-01-01' AND data_venda < DATE '2027-01-01';

-- Lent: càlcul sobre columna indexada
SELECT * FROM productes WHERE preu * 1.21 > 100;

-- Ràpid: reescriure per aïllar la columna
SELECT * FROM productes WHERE preu > 100 / 1.21;

work_mem i el seu impacte

work_mem controla quanta memòria pot usar cada operació de Sort o Hash. Si l'operació no hi cap, PostgreSQL fa un mergesort extern al disc (molt més lent). El valor per defecte (4 MB) és sovint insuficient per a taules grans:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Veure si hi ha sorts/hashes que van al disc
EXPLAIN (ANALYZE, BUFFERS)
SELECT client_id, SUM(total) FROM vendes GROUP BY client_id ORDER BY SUM(total) DESC;
-- Si apareix "Sort Method: external merge  Disk: 1234kB" -> cal més work_mem

-- Augmentar work_mem per a la sessió actual (no canvia la config global)
SET work_mem = '256MB';

-- Tornar a executar i verificar que ara usa "Sort Method: quicksort  Memory: 45kB"

sort_buffer_size controla la memòria per a ORDER BY/GROUP BY quan no es pot resoldre amb un índex, i join_buffer_size la memòria per a joins sense índex disponible. Tots dos són paràmetres per connexió, igual que work_mem:

SET SESSION sort_buffer_size = 268435456; -- 256MB
SET SESSION join_buffer_size = 268435456; -- 256MB

No hi ha un paràmetre equivalent configurable manualment per sessió. La memòria per a operacions de Sort o Hash es gestiona automàticament mitjançant memory grants, calculats per l'optimitzador segons l'estimació de files, i controlats globalment per "Max Server Memory" i el Resource Governor (per a pools de recursos). És una diferència filosòfica respecte a PostgreSQL i Oracle: SQL Server automatitza el que aquests permeten ajustar manualment.

EXEC sp_configure 'max server memory (MB)', 8192;
RECONFIGURE;

PGA_AGGREGATE_TARGET (mode automàtic, recomanat) gestiona la memòria de totes les sessions de forma global. Alternativament, en mode manual (WORKAREA_SIZE_POLICY = MANUAL), SORT_AREA_SIZE és l'equivalent directe a work_mem:

ALTER SYSTEM SET pga_aggregate_target = 4G;

-- Mode manual (no recomanat en producció moderna):
ALTER SESSION SET sort_area_size = 268435456; -- 256MB

work_mem és per operació i per connexió

Si work_mem = 256MB i hi ha 100 connexions actives, cadascuna amb 5 operacions de sort simultànies, el consum màxim teòric és de 256 MB × 100 × 5 = 128 GB. Augmenteu-lo amb prudència i preferiblement per sessió o per rol, no globalment. A MySQL/MariaDB el mateix raonament aplica a sort_buffer_size/join_buffer_size; a SQL Server i Oracle (en mode automàtic), el motor gestiona aquest risc internament.

AC5074/02/03 — Miniactivitat

Analitzeu el següent pla EXPLAIN ANALYZE i responeu les preguntes:

Hash Join  (cost=45234.00..312450.00 rows=500000 width=88)
           (actual time=8234.5..45123.2 rows=487234 loops=1)
  Hash Cond: (v.producte_id = p.id)
  Buffers: shared hit=1200 read=98000
  ->  Seq Scan on vendes v  (cost=0.00..234000.00 rows=10000000 width=56)
                            (actual time=0.1..12340.0 rows=10000000 loops=1)
        Filter: (data_venda >= '2026-01-01')
        Rows Removed by Filter: 3500000
        Buffers: shared hit=800 read=95000
  ->  Hash  (cost=2100.00..2100.00 rows=105000 width=32)
            (actual time=234.5..234.5 rows=105000 loops=1)
        ->  Seq Scan on productes p  (cost=0.00..2100.00 rows=105000 width=32)
                                    (actual time=0.1..123.4 rows=105000 loops=1)
Planning Time: 12.3 ms
Execution Time: 45890.1 ms

Identificació: (1) Quin és el coll d'ampolla principal i per quina raó? (2) Quines dades indiquen que les estadístiques poden estar desactualitzades? (3) Quants blocs s'han llegit del disc i quin impacte té? (4) Proposeu dues millores concretes (índexs, particionament, configuració) i expliqueu per quin motiu espereu millora en cadascuna.