PR04 — Pràctica: Anàlisi de rendiment amb Docker

Objectius

• Crear un entorn PostgreSQL aïllat amb Docker i generar un conjunt de dades de prova realista.
• Mesurar el temps d'execució de consultes amb \timing i EXPLAIN ANALYZE.
• Identificar operacions costoses (Sequential Scan) i optimitzar-les amb índexs adequats.
• Comprendre la diferència entre un índex simple, un índex compost i un covering index.
• Configurar el registre de consultes lentes (slow query log) i interpretar la informació de pg_stat_activity i pg_stat_user_indexes.
• Comparar els resultats equivalents a MySQL.

---

Requisits previs

Requisit	Detall
Docker	Docker Desktop (Windows/macOS) o Docker Engine (Linux) instal·lat i en marxa
RAM disponible	Mínim 4 GB (el contenidor usarà ~1-2 GB)
Pràctiques anteriors	Haver completat la pràctica d'instal·lació i la de consultes bàsiques
Temps estimat	5 hores
Client SQL	psql via docker exec, DBeaver, o pgAdmin

Nomenclatura dels contenidors

Tots els contenidors creats en aquesta pràctica han d'incloure el teu nom com a alumne al nom del contenidor per facilitar la identificació en entorns compartits. Substitueix [nom-alumne] pel teu nom real (sense espais ni accents).

---

Pas 1 — Iniciar el contenidor PostgreSQL

docker run \
    --name sgbd-rendiment-[nom-alumne] \
    -e POSTGRES_PASSWORD=secret \
    -e POSTGRES_DB=empresa \
    -p 5432:5432 \
    -d \
    postgres:17

Verifica que el contenidor és en marxa:

docker ps --filter name=sgbd-rendiment-[nom-alumne]

Connecta't al contenidor:

docker exec -it sgbd-rendiment-[nom-alumne] psql -U postgres -d empresa

---

Pas 2 — Generar dades de prova (500.000 files)

Un cop connectat amb psql, executa:

-- Crear la taula de vendes
CREATE TABLE vendes (
    id         BIGSERIAL     PRIMARY KEY,
    client_id  INTEGER       NOT NULL,
    producte   VARCHAR(100)  NOT NULL,
    import     NUMERIC(10,2) NOT NULL,
    data       DATE          NOT NULL,
    regio      VARCHAR(20)   NOT NULL,
    processat  BOOLEAN       NOT NULL DEFAULT false
);

-- Inserir 500.000 files amb dades aleatòries (pot trigar 20-60 s)
INSERT INTO vendes (client_id, producte, import, data, regio, processat)
SELECT
    (random() * 9999 + 1)::INT                                 AS client_id,
    (ARRAY['Portàtil','Telèfon','Tablet','Monitor','Teclat',
            'Ratolí','Auriculars','Càmera','Impressora','Disc dur'])
        [ceil(random() * 10)::INT]                             AS producte,
    ROUND((random() * 1999 + 1)::NUMERIC, 2)                  AS import,
    '2020-01-01'::DATE + (random() * 1826)::INT               AS data,
    (ARRAY['Nord','Sud','Est','Oest','Centre'])
        [ceil(random() * 5)::INT]                              AS regio,
    random() > 0.8                                             AS processat
FROM generate_series(1, 500000);

-- Verificar
SELECT COUNT(*) FROM vendes;
-- Resultat esperat: 500000

generate_series()

La funció generate_series(1, 500000) genera una seqüència de 500.000 files. És la manera estàndard de generar dades de prova en PostgreSQL. Per a MySQL, l'equivalent és usar un procediment emmagatzemat amb un bucle.

---

Pas 3 — Executar una consulta lenta i mesurar-la

Activa el temporitzador de psql i executa la consulta:

-- Activar el temporitzador
\timing on

-- Consulta lenta: vendes per any i client, ordenades per total descendent
SELECT
    client_id,
    SUM(import)  AS total_vendes,
    COUNT(*)     AS num_vendes
FROM vendes
WHERE EXTRACT(YEAR FROM data) = 2023
GROUP BY client_id
ORDER BY total_vendes DESC
LIMIT 20;

Anota el temps d'execució. Amb 500.000 files i cap índex, hauria de trigar entre 500 ms i 3 s depenent de la màquina.

---

Pas 4 — Analitzar el pla d'execució (EXPLAIN ANALYZE)

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
    client_id,
    SUM(import)  AS total_vendes,
    COUNT(*)     AS num_vendes
FROM vendes
WHERE EXTRACT(YEAR FROM data) = 2023
GROUP BY client_id
ORDER BY total_vendes DESC
LIMIT 20;

Busca en la sortida:

• Seq Scan on vendes → llegeix tota la taula (costós).
• cost=... → cost estimat per l'optimitzador.
• actual time=... → temps real mesurat.
• rows=... → nombre de files retornades.
• Buffers: shared hit=... read=... → blocs llegits de memòria vs disc.

Interpretació de l'output d'EXPLAIN

El format de l'arbre és de dins cap a fora: el node de baix s'executa primer. Els costos s'acumulen cap amunt. Un Seq Scan sobre una taula gran amb un filtre selectiu és la senyal que falta un índex.

---

Pas 5 — Reescriure la consulta per usar rang de dates

EXTRACT(YEAR FROM data) impedeix l'ús d'índexs sobre la columna data. Reescriu la consulta usant un rang de dates explícit:

\timing on

-- Consulta optimitzada: usa rang de dates (permet index scan)
SELECT
    client_id,
    SUM(import)  AS total_vendes,
    COUNT(*)     AS num_vendes
FROM vendes
WHERE data >= '2023-01-01'
  AND data <  '2024-01-01'
GROUP BY client_id
ORDER BY total_vendes DESC
LIMIT 20;

Anota el temps. Sense índex, encara farà Seq Scan, però la consulta és ara índex-compatible (sargable).

---

Pas 6 — Crear un índex sobre la columna data

-- Crear l'índex (pot trigar 5-15 s sobre 500k files)
CREATE INDEX idx_vendes_data ON vendes (data);

-- Verificar la creació
\d vendes
-- Ha d'aparèixer: "idx_vendes_data"

Torna a executar la consulta del Pas 5 i analitza el pla:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    client_id,
    SUM(import)  AS total_vendes,
    COUNT(*)     AS num_vendes
FROM vendes
WHERE data >= '2023-01-01'
  AND data <  '2024-01-01'
GROUP BY client_id
ORDER BY total_vendes DESC
LIMIT 20;

Ara ha d'aparèixer Index Scan using idx_vendes_data o Bitmap Index Scan en comptes de Seq Scan. Anota la diferència de cost i temps.

---

Pas 7 — Comparar els costos (taula resum)

Omple aquesta taula amb els resultats reals que has obtingut:

Consulta	Índex	Tipus d'accés	Cost estimat	Temps real
EXTRACT(YEAR...)	Cap	Seq Scan	?	? ms
Rang de dates	Cap	Seq Scan	?	? ms
Rang de dates	idx_vendes_data	Index Scan	?	? ms

---

Pas 8 — Crear un índex compost cobridor (covering index)

Un covering index inclou totes les columnes que la consulta necessita, de manera que el motor no ha de tornar a llegir la taula principal (heap).

-- Índex compost que cobreix la consulta de vendes per client
CREATE INDEX idx_vendes_data_client
    ON vendes (data, client_id)
    INCLUDE (import);
-- (data, client_id) = columnes de cerca/agrupació
-- INCLUDE (import)  = columna de projecció (evita heap fetch)

-- Analitzar de nou
EXPLAIN (ANALYZE, BUFFERS)
SELECT
    client_id,
    SUM(import)  AS total_vendes,
    COUNT(*)     AS num_vendes
FROM vendes
WHERE data >= '2023-01-01'
  AND data <  '2024-01-01'
GROUP BY client_id
ORDER BY total_vendes DESC
LIMIT 20;

Comprova si apareix Index Only Scan (la millor opció: no cal llegir la taula) o Index Scan.

Index Only Scan

Un Index Only Scan és possible quan l'índex conté totes les columnes que la consulta necessita (condicions WHERE, GROUP BY i SELECT). Usant la clàusula INCLUDE, s'afegeixen columnes a l'índex sense que formin part de l'estructura de cerca, mantenint l'índex compacte però permetent l'index only scan.

---

Pas 9 — Activar el registre de consultes lentes

-- Activar el log de consultes que tarden més de 100 ms
ALTER SYSTEM SET log_min_duration_statement = 100;

-- Recarregar la configuració (sense reiniciar el servidor)
SELECT pg_reload_conf();

-- Verificar que ha pres efecte
SHOW log_min_duration_statement;
-- Ha de mostrar: 100ms

Executa una consulta lenta forçant un Seq Scan:

-- Desactivar temporalment els índexs per forçar un Seq Scan
SET enable_indexscan  = off;
SET enable_bitmapscan = off;

SELECT regio, COUNT(*), AVG(import)
FROM vendes
WHERE processat = false
GROUP BY regio;

-- Restaurar
SET enable_indexscan  = on;
SET enable_bitmapscan = on;

Ara mira els logs del contenidor:

docker logs sgbd-rendiment-[nom-alumne] 2>&1 | grep "duration:" | tail -20

Hauries de veure línies com:

LOG:  duration: 1243.567 ms  statement: SELECT regio, COUNT(*)...

---

Pas 10 — Identificar índexs no usats

Amb el temps, s'acumulen índexs que mai no s'usen i que penalitzen les escriptures. PostgreSQL manté estadístiques d'ús a pg_stat_user_indexes.

-- Índexs de la taula vendes amb les seves estadístiques d'ús
SELECT
    indexrelname                                                  AS nom_index,
    idx_scan                                                      AS vegades_usat,
    idx_tup_read                                                  AS tuples_llegides,
    idx_tup_fetch                                                 AS tuples_retornades,
    pg_size_pretty(pg_relation_size(indexrelid))                  AS mida
FROM pg_stat_user_indexes
WHERE relname = 'vendes'
ORDER BY idx_scan ASC;

-- Resetar estadístiques per a una prova neta
SELECT pg_stat_reset();

Crea un índex de baixa utilitat i verifica-ho:

-- Índex sobre una columna booleana: selectivitat molt baixa, poques vegades útil
CREATE INDEX idx_vendes_processat ON vendes (processat);

-- Après d'executar les consultes anteriors, comprova que idx_scan = 0
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'vendes';

Índexs sobre columnes de baixa cardinalitat

Un índex sobre una columna booleana rarament és útil. Si el 80% de les files tenen processat = false, un Seq Scan serà més eficient que un Index Scan que ha de retornar 400.000 files. PostgreSQL ho detecta automàticament i ignora l'índex en aquets casos.

---

Pas 11 — Monitorar les consultes actives

-- Veure totes les connexions actives i les seves consultes
SELECT
    pid,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    ROUND(EXTRACT(EPOCH FROM (now() - query_start))::NUMERIC, 2) AS durada_s,
    LEFT(query, 80)                                               AS query_inici
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY durada_s DESC NULLS LAST;

-- Consultes que porten més de 5 segons (possible problema)
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - INTERVAL '5 seconds'
  AND pid != pg_backend_pid();

-- Cancel·lar una consulta per pid (suau — espera que acabi la transacció actual)
-- SELECT pg_cancel_backend(12345);

-- Finalitzar forçosament una connexió per pid (agressiu — tanca immediatament)
-- SELECT pg_terminate_backend(12345);

---

Equivalent MySQL — Resum de passos

MySQL / MariaDB

-- Pas 1: Arrancar el contenidor MySQL
-- docker run --name sgbd-rendiment-mysql-[nom-alumne]
--   -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=empresa
--   -p 3307:3306 -d mysql:8.4

-- Pas 2: Crear la taula
CREATE TABLE vendes (
    id         BIGINT        NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_id  INT           NOT NULL,
    producte   VARCHAR(100)  NOT NULL,
    import     DECIMAL(10,2) NOT NULL,
    data       DATE          NOT NULL,
    regio      VARCHAR(20)   NOT NULL,
    processat  TINYINT(1)    NOT NULL DEFAULT 0
);

-- Pas 4: Analitzar consulta (EXPLAIN)
EXPLAIN
SELECT client_id, SUM(import) AS total_vendes, COUNT(*) AS num_vendes
FROM vendes
WHERE YEAR(data) = 2023
GROUP BY client_id
ORDER BY total_vendes DESC
LIMIT 20;
-- type: ALL = full table scan (sense índex)

-- Pas 5: Reescriure amb rang de dates (sargable)
EXPLAIN
SELECT client_id, SUM(import) AS total_vendes, COUNT(*) AS num_vendes
FROM vendes
WHERE data BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY client_id
ORDER BY total_vendes DESC
LIMIT 20;
-- type: range = usa l'índex sobre data

-- Pas 6: Crear índex
CREATE INDEX idx_vendes_data ON vendes (data);

-- Pas 8: Índex compost (MySQL no té INCLUDE, però pot fer index-covering)
CREATE INDEX idx_data_client_import ON vendes (data, client_id, import);
-- Afegint import a l'índex, MySQL pot fer "Using index" (covering scan)

-- Pas 9: Slow query log
SET GLOBAL slow_query_log     = ON;
SET GLOBAL long_query_time    = 0.1;   -- 100 ms
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Pas 10: Estadístiques d'ús d'índexs (Performance Schema)
SELECT
    object_name  AS taula,
    index_name,
    count_star   AS vegades_usat
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'empresa'
  AND object_name   = 'vendes'
ORDER BY count_star ASC;

-- Pas 11: Consultes actives
SHOW PROCESSLIST;
-- O amb más detall:
SELECT id, user, host, db, command, time, state, LEFT(info, 80) AS query
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

---

Neteja

Un cop finalitzada la pràctica, elimina el contenidor:

docker stop sgbd-rendiment-[nom-alumne]
docker rm   sgbd-rendiment-[nom-alumne]

---

Preguntes de reflexió

Respon aquestes preguntes al teu informe de pràctica:

1. Quina és la diferència entre un Seq Scan i un Index Scan en termes de cost? En quin cas pot ser preferible el Seq Scan tot i existir un índex?

2. Per què EXTRACT(YEAR FROM data) = 2023 impedeix l'ús d'un índex sobre data, mentre que data >= '2023-01-01' AND data < '2024-01-01' sí que el permet? Defineix el concepte de consulta sargable.

3. Quin és el benefici d'un covering index (amb INCLUDE) respecte a un índex compost sense INCLUDE? Quan és recomanable usar-lo?

4. Per a quines operacions els índexs perjudiquen el rendiment? Posa exemples concrets (INSERT massiu, UPDATE, DELETE).

5. Compara el temps d'execució de la consulta del Pas 3 (sense índex, amb EXTRACT) amb el del Pas 8 (amb covering index i rang de dates). Quin és el factor de millora que has obtingut?

6. Per a una taula amb 500.000 files, quin és el límit aproximat de selectivitat a partir del qual PostgreSQL prefereix un Seq Scan a un Index Scan? Fes proves pràctiques per respondre (canvia el valor del filtre i observa quan l'optimitzador canvia de pla).