Índexs avançats

Els índexs B-Tree, Hash, GIN/invertits, BRIN i GiST/espacials serveixen per a casos d'ús molt diferents segons el motor de base de dades. La tria correcta de l'índex marca la diferència entre un escaneig complet de la taula i un accés directe per índex sobre milions de registres, i pot reduir el temps d'una consulta de minuts a mil·lisegons.

Per què existeixen els índexs?

Imagineu una taula de vendes amb 100 milions de registres. Sense cap índex, trobar totes les vendes d'un client concret obliga el motor a llegir cada fila de la taula des del disc. Això es coneix com a escaneig seqüencial o complet de la taula (Seq Scan a PostgreSQL, ALL a MySQL, Table Scan a SQL Server, TABLE ACCESS FULL a Oracle) i té un cost lineal O(n): el temps creix proporcionalment amb el nombre de files.

Un índex és una estructura de dades auxiliar, mantinguda automàticament pel motor, que permet localitzar les files rellevants sense llegir tota la taula. El tipus d'índex per defecte als quatre motors —el B-Tree— té un cost de cerca O(log n): per a 100 milions de files, en lloc de 100 milions de comparacions en necessita aproximadament 27. La diferència és abismal.

El cost d'un índex no és zero. Cada vegada que s'insereix, actualitza o elimina una fila, tots els índexs d'aquella taula s'han d'actualitzar. Per tant, la regla pràctica és: indexeu el que es consulta sovint i s'escriu poc; eviteu indexar columnes que canvien contínuament si les consultes que les usen ja s'executen prou ràpid.

B-Tree: l'índex de propòsit general

El B-Tree (arbre equilibrat) és l'índex per defecte als quatre motors. Organitza els valors en una estructura d'arbre on tots els nodes fulla estan a la mateixa profunditat i estan encadenats en ordre, cosa que permet recórrer-los seqüencialment.

El motor pot usar un índex B-Tree per a les operacions:

• Igualtat: =
• Comparació: <, <=, >, >=
• Rangs: BETWEEN
• Ordenació: ORDER BY
• Prefixos de text: LIKE 'prefix%' (si la columna és text o varchar i la collation és adequada)

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Creació d'un índex B-Tree (per defecte)
CREATE INDEX idx_vendes_client ON vendes (client_id);

-- PostgreSQL l'usa automàticament per a:
SELECT * FROM vendes WHERE client_id = 4231;
SELECT * FROM vendes WHERE client_id BETWEEN 1000 AND 2000;
SELECT * FROM vendes ORDER BY client_id LIMIT 100;

-- Funciona amb LIKE si el patró és un prefix:
CREATE INDEX idx_productes_nom ON productes (nom text_pattern_ops);
SELECT * FROM productes WHERE nom LIKE 'Porta%';

-- Creació d'un índex B-Tree (per defecte a InnoDB)
CREATE INDEX idx_vendes_client ON vendes (client_id);

-- MySQL l'usa automàticament per a:
SELECT * FROM vendes WHERE client_id = 4231;
SELECT * FROM vendes WHERE client_id BETWEEN 1000 AND 2000;
SELECT * FROM vendes ORDER BY client_id LIMIT 100;

-- Funciona amb LIKE si el patró és un prefix:
SELECT * FROM productes WHERE nom LIKE 'Porta%';

-- Índex NONCLUSTERED (equivalent habitual al B-Tree de PostgreSQL)
CREATE NONCLUSTERED INDEX idx_vendes_client ON vendes (client_id);

-- SQL Server l'usa automàticament per a:
SELECT * FROM vendes WHERE client_id = 4231;
SELECT * FROM vendes WHERE client_id BETWEEN 1000 AND 2000;
SELECT * FROM vendes ORDER BY client_id OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

CLUSTERED vs NONCLUSTERED

A SQL Server cal distingir els dos tipus d'índex B-Tree: un índex CLUSTERED determina l'ordre físic d'emmagatzematge de les dades de la taula (cada taula només en pot tenir un, normalment sobre la clau primària), de forma similar al concepte de "taula organitzada per índex" d'Oracle (ORGANIZATION INDEX). Un índex NONCLUSTERED és una estructura separada que apunta a les files, equivalent a un índex B-Tree normal de PostgreSQL o Oracle.

-- Creació d'un índex B-Tree (tipus per defecte)
CREATE INDEX idx_vendes_client ON vendes (client_id);

-- Oracle l'usa automàticament per a:
SELECT * FROM vendes WHERE client_id = 4231;
SELECT * FROM vendes WHERE client_id BETWEEN 1000 AND 2000;
SELECT * FROM vendes ORDER BY client_id FETCH FIRST 100 ROWS ONLY;

LIKE amb comodí inicial

LIKE '%porta' (comodí a l'inici) no pot usar un índex B-Tree en cap dels quatre motors perquè no se sap per on de l'arbre ha de començar. A PostgreSQL cal considerar text search o un índex GIN sobre tsvector; a MySQL/MariaDB un FULLTEXT INDEX; a SQL Server un FULLTEXT INDEX amb CONTAINS; i a Oracle, Oracle Text.

Hash: per a igualtats exactes

L'índex Hash emmagatzema una taula de dispersió (hash map) dels valors. Només serveix per a l'operació = i no pot ordenar ni fer cerques per rang. A canvi, la cerca és O(1) —constant independentment de la mida de la taula— i l'índex ocupa menys espai que un B-Tree equivalent.

PostgreSQLMySQL / MariaDBSQL ServerOracle

CREATE INDEX idx_sessio_token ON sessions USING hash (token);

-- Òptim per a:
SELECT * FROM sessions WHERE token = 'abc123xyz';

-- InnoDB (el motor per defecte) NO permet crear índexs HASH explícits.
-- Internament manté un "adaptive hash index" automàtic i transparent
-- sobre les pàgines més consultades del buffer pool, però no es pot
-- controlar ni crear per SQL.

-- Només el motor MEMORY suporta índexs HASH explícits:
CREATE TABLE sessions (
    token   VARCHAR(64) NOT NULL,
    dades   VARCHAR(255),
    INDEX idx_sessio_token USING HASH (token)
) ENGINE = MEMORY;

-- Òptim per a:
SELECT * FROM sessions WHERE token = 'abc123xyz';

Adaptive Hash Index d'InnoDB

InnoDB no exposa cap sintaxi CREATE INDEX ... USING HASH. L'adaptive hash index és una optimització interna automàtica: InnoDB detecta patrons d'accés freqüents per igualtat sobre un índex B-Tree i en construeix una taula de dispersió en memòria, sense intervenció ni control de l'usuari.

-- No existeix un índex hash per a taules normals (basades en disc).
-- Només és disponible per a "memory-optimized tables" (In-Memory OLTP):
CREATE TABLE sessions (
    token   NVARCHAR(64) NOT NULL,
    dades   NVARCHAR(255),
    CONSTRAINT pk_sessions PRIMARY KEY NONCLUSTERED HASH (token)
        WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON);

-- O com a índex independent:
CREATE TABLE sessions2 (
    token   NVARCHAR(64) NOT NULL,
    dades   NVARCHAR(255),
    INDEX idx_sessio_token HASH (token) WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON);

Cas especial, no equivalent directe

L'índex HASH de SQL Server només existeix dins l'arquitectura In-Memory OLTP (taules MEMORY_OPTIMIZED), pensada per a càrregues de molt alt rendiment transaccional. No hi ha cap forma de crear un índex hash sobre una taula tradicional basada en disc.

-- No existeix CREATE INDEX ... USING HASH per a taules normals.
-- L'equivalent més proper és un HASH CLUSTER, que no és un índex
-- sinó una estructura d'emmagatzematge física compartida per taula(es):
CREATE CLUSTER sessions_cluster (token VARCHAR2(64))
    HASHKEYS 1000;

CREATE TABLE sessions (
    token   VARCHAR2(64) NOT NULL,
    dades   VARCHAR2(255)
) CLUSTER sessions_cluster (token);

-- Òptim per a:
SELECT * FROM sessions WHERE token = 'abc123xyz';

Concepte diferent: cluster, no índex

Un hash cluster a Oracle emmagatzema físicament les files segons el hash de la clau, en lloc de mantenir una estructura auxiliar separada com fa un índex. És un concepte arquitectònicament diferent i, en la pràctica, gairebé no s'usa fora de casos molt específics perquè limita la flexibilitat de la taula (mida fixa de hashkeys, problemes de col·lisions).

Quan usar Hash?

En la pràctica, el guany respecte a B-Tree per a igualtats exactes és modest (els B-Tree moderns ja són molt eficients). Reserveu Hash per a columnes molt llargues on l'estalvi d'espai compensa, o quan els benchmarks ho justifiquen clarament. Cal tenir present que, dels quatre motors, només PostgreSQL ofereix un índex Hash explícit i d'ús general sobre taules normals.

GIN: índex invertit per a arrays, JSONB i text search

GIN (Generalized Inverted Index) funciona com l'índex d'un llibre: per cada element (paraula, clau JSONB, valor d'array), guarda la llista de files que el contenen. És la millor opció per a:

• Arrays: operadors @> (conté), <@ (és contingut en), && (se superposen)
• JSONB: cerca de claus i valors niuats
• Text search: operador @@ amb tsvector i tsquery

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- GIN per a arrays
CREATE TABLE productes (
    id      serial PRIMARY KEY,
    nom     text,
    etiquetes text[]
);
CREATE INDEX idx_prod_etiquetes ON productes USING gin (etiquetes);

-- Cerca tots els productes que tenen 'oferta' i 'electrònica'
SELECT * FROM productes WHERE etiquetes @> ARRAY['oferta', 'electrònica'];

-- GIN per a JSONB
CREATE TABLE events (
    id      bigserial PRIMARY KEY,
    payload jsonb
);
CREATE INDEX idx_events_payload ON events USING gin (payload);

-- Cerca events on el JSON conté la clau 'usuari' amb valor 'admin'
SELECT * FROM events WHERE payload @> '{"usuari": "admin"}';

-- GIN per a text search
ALTER TABLE articles ADD COLUMN ts_contingut tsvector
    GENERATED ALWAYS AS (to_tsvector('catalan', contingut)) STORED;
CREATE INDEX idx_articles_ts ON articles USING gin (ts_contingut);

SELECT titol FROM articles
WHERE ts_contingut @@ to_tsquery('catalan', 'base & de & dades');

-- MySQL/MariaDB no té un índex GIN. Per a JSON, l'alternativa és
-- crear un índex sobre una "generated column" extreta del JSON:
CREATE TABLE events (
    id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    payload JSON
);

ALTER TABLE events
    ADD COLUMN usuari_id VARCHAR(50)
        GENERATED ALWAYS AS (payload->>'$.usuari_id') STORED,
    ADD INDEX idx_events_usuari (usuari_id);

-- Cerca events de l'usuari 'admin'
SELECT * FROM events WHERE usuari_id = 'admin';

-- Per a cerca de text complet, FULLTEXT INDEX:
CREATE FULLTEXT INDEX idx_articles_ft ON articles (contingut);

SELECT * FROM articles
WHERE MATCH(contingut) AGAINST('base de dades' IN NATURAL LANGUAGE MODE);

Sense equivalent directe a GIN

No hi ha un índex genèric per a arrays o JSON complet com GIN. Cal indexar una expressió concreta extreta del document (una "generated column"), una per cada clau que es vulgui consultar de forma eficient.

-- SQL Server tampoc té un índex GIN. Per a JSON, l'alternativa és
-- una columna calculada indexada:
CREATE TABLE events (
    id      BIGINT IDENTITY(1,1) PRIMARY KEY,
    payload NVARCHAR(MAX)
);

ALTER TABLE events
    ADD usuari_id AS JSON_VALUE(payload, '$.usuari_id');
CREATE INDEX idx_events_usuari ON events (usuari_id);

-- Cerca events de l'usuari 'admin'
SELECT * FROM events WHERE usuari_id = 'admin';

-- Per a cerca de text complet, FULLTEXT INDEX:
CREATE FULLTEXT INDEX ON articles (contingut)
    KEY INDEX PK_articles;

SELECT * FROM articles
WHERE CONTAINS(contingut, 'base de dades');

Sense equivalent directe a GIN

SQL Server emmagatzema el JSON com a text (NVARCHAR(MAX)); no hi ha un tipus binari indexable directament com jsonb. La indexació es fa sempre via columnes calculades persistides.

-- Oracle 12c+ permet indexar JSON amb un Search Index (similar a GIN)
-- o amb un índex funcional sobre un valor concret:
CREATE TABLE events (
    id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload CLOB CHECK (payload IS JSON)
);

-- Search index per a consultes JSON genèriques
CREATE SEARCH INDEX idx_events_payload ON events (payload) FOR JSON;

-- O un índex funcional per a una clau concreta
CREATE INDEX idx_events_usuari
    ON events (JSON_VALUE(payload, '$.usuari_id'));

-- Cerca events de l'usuari 'admin'
SELECT * FROM events WHERE JSON_VALUE(payload, '$.usuari_id') = 'admin';

-- Per a cerca de text complet, Oracle Text:
CREATE INDEX idx_articles_ft ON articles (contingut)
    INDEXTYPE IS CTXSYS.CONTEXT;

SELECT * FROM articles
WHERE CONTAINS(contingut, 'base AND dades') > 0;

Oracle Text com a alternativa a GIN per a text

El SEARCH INDEX ... FOR JSON d'Oracle és conceptualment el més proper a GIN (indexa tot el document JSON), però per a cerca de text complet l'eina pròpia és Oracle Text, amb una sintaxi i un motor de relevància propis, diferents dels de PostgreSQL.

GIN vs B-Tree per a text

Un B-Tree amb LIKE 'prefix%' serveix per a cerques de prefix. Per a cerques de paraules completes dins de textos llargs, GIN amb tsvector és molt superior a PostgreSQL; als altres motors, l'eina equivalent és sempre un índex de text complet dedicat (FULLTEXT a MySQL/SQL Server, Oracle Text a Oracle), no un índex B-Tree.

BRIN: índex petit per a taules molt grans

BRIN (Block Range INdex) és radicalment diferent: no indexa cada fila sinó rangs de blocs físics del disc. Per a cada rang de blocs consecutius, guarda els valors mínim i màxim de la columna indexada. Quan arriba una consulta, descarta els rangs on el valor buscat no pot estar.

BRIN és útil quan les dades estan físicament ordenades de manera similar a l'ordre lògic de la columna (correlació física alta). El cas ideal: timestamps d'inserció en taules de logs o sèries temporals, on les files s'insereixen sempre en ordre cronològic i cada bloc del disc conté valors de data contigus.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Taula de logs amb 500 milions de registres
CREATE TABLE logs_sistema (
    id          bigserial PRIMARY KEY,
    ts          timestamp NOT NULL DEFAULT now(),
    nivell      text,
    missatge    text
);

-- Índex BRIN sobre el timestamp (molt petit, molt eficient per a rangs)
CREATE INDEX idx_logs_ts_brin ON logs_sistema USING brin (ts);

-- PostgreSQL pot descartar la majoria de blocs per a:
SELECT * FROM logs_sistema
WHERE ts BETWEEN '2026-01-01' AND '2026-01-02';

No existeix cap índex equivalent a BRIN. L'alternativa pràctica per a taules molt grans amb dades físicament ordenades (com els logs per timestamp) és el particionament de la taula (vegeu particionament.md), que permet descartar particions senceres en lloc de blocs individuals.

-- Particionament per rang de dates com a alternativa pràctica
CREATE TABLE logs_sistema (
    id          BIGINT AUTO_INCREMENT,
    ts          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    nivell      VARCHAR(20),
    missatge    TEXT,
    PRIMARY KEY (id, ts)
)
PARTITION BY RANGE (YEAR(ts)) (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

No existeix BRIN, però l'índex Columnstore ofereix un benefici similar per a grans taules analítiques: gràcies a la compressió per columnes i al "segment elimination", pot descartar segments sencers de dades sense llegir-los, un concepte molt proper al block range pruning de BRIN.

-- Índex Columnstore com a alternativa més propera a BRIN
CREATE TABLE logs_sistema (
    id          BIGINT IDENTITY(1,1),
    ts          DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    nivell      NVARCHAR(20),
    missatge    NVARCHAR(MAX)
);

CREATE CLUSTERED COLUMNSTORE INDEX idx_logs_sistema_cs ON logs_sistema;

-- El segment elimination permet descartar segments sencers per a:
SELECT * FROM logs_sistema
WHERE ts BETWEEN '2026-01-01' AND '2026-01-02';

No existeix BRIN a Oracle. L'alternativa és el particionament estàndard combinat, en edicions amb l'opció Exadata o In-Memory, amb Zone Maps (no disponibles a totes les edicions d'Oracle).

-- Particionament per rang de dates com a alternativa pràctica
CREATE TABLE logs_sistema (
    id          NUMBER GENERATED ALWAYS AS IDENTITY,
    ts          TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    nivell      VARCHAR2(20),
    missatge    CLOB
)
PARTITION BY RANGE (ts) (
    PARTITION p2025 VALUES LESS THAN (TIMESTAMP '2026-01-01 00:00:00'),
    PARTITION p2026 VALUES LESS THAN (TIMESTAMP '2027-01-01 00:00:00'),
    PARTITION pmax  VALUES LESS THAN (MAXVALUE)
);

-- Zone Maps (només disponible amb determinades opcions/edicions)
-- CREATE MATERIALIZED ZONEMAP logs_sistema_zm ON logs_sistema (ts);

Comparativa de mida (orientativa per a 500M files, PostgreSQL):

Índex	Mida aproximada
B-Tree sobre ts	~11 GB
BRIN sobre ts (128 blocs/rang)	~48 KB

BRIN no serveix per a dades desordenades

Si els valors d'una columna no estan correlacionats amb l'ordre físic de les files (per exemple, un UUID aleatori), BRIN no és útil perquè no pot descartar cap rang de blocs. Aquesta limitació també s'aplica, per analogia, al particionament i al Columnstore: només aporten benefici si les dades estan físicament o lògicament ordenades segons la columna consultada.

GiST: per a dades geomètriques i text search avançat

GiST (Generalized Search Tree) és un marc genèric que permet implementar múltiples estratègies d'indexació. S'usa principalment per a:

• Tipus geomètrics: point, box, polygon, circle (operadors &&, @>, <->)
• Rangs: daterange, tsrange, int4range
• Text search: alternativa a GIN per a tsvector (menor espai, però cerques una mica més lentes)

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Índex GiST per a coordenades geogràfiques (sense PostGIS)
CREATE TABLE punts_interes (
    id      serial PRIMARY KEY,
    nom     text,
    ubicacio point
);
CREATE INDEX idx_punts_ubicacio ON punts_interes USING gist (ubicacio);

-- Trobar tots els punts dins d'un cercle de radi 10 unitats
SELECT nom FROM punts_interes
WHERE ubicacio <@ circle '((41.7, 2.9), 0.1)';

-- GiST per a rangs de dates (reserves d'hotel)
CREATE TABLE reserves (
    id          serial PRIMARY KEY,
    habitacio   int,
    periode     daterange
);
CREATE INDEX idx_reserves_periode ON reserves USING gist (periode);

-- Reserves que se solapen amb la setmana de Nadal
SELECT * FROM reserves
WHERE periode && daterange('2026-12-24', '2026-12-27');

-- Índex SPATIAL (R-Tree) per a tipus geomètrics
-- Requereix motor InnoDB i columna NOT NULL
CREATE TABLE punts_interes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(100),
    ubicacio    POINT NOT NULL SRID 0
);
CREATE SPATIAL INDEX idx_punts_ubicacio ON punts_interes (ubicacio);

-- Trobar punts dins d'un rectangle envoltant
SELECT nom FROM punts_interes
WHERE MBRContains(
    ST_GeomFromText('POLYGON((41.6 2.8, 41.6 3.0, 41.8 3.0, 41.8 2.8, 41.6 2.8))'),
    ubicacio
);

Sense equivalent per a rangs de dates

MySQL/MariaDB no té un tipus de dada de rang indexable equivalent a daterange de PostgreSQL. Les consultes de solapament de períodes s'han de resoldre amb condicions WHERE data_inici <= ... AND data_fi >= ... sobre un índex B-Tree compost, sense el suport especialitzat que dona GiST.

-- Índex espacial amb tipus geometry/geography
CREATE TABLE punts_interes (
    id          INT IDENTITY(1,1) PRIMARY KEY,
    nom         NVARCHAR(100),
    ubicacio    GEOMETRY NOT NULL
);
CREATE SPATIAL INDEX idx_punts_ubicacio
    ON punts_interes (ubicacio)
    USING GEOMETRY_AUTO_GRID;

-- Trobar punts dins d'un polígon
SELECT nom FROM punts_interes
WHERE ubicacio.STWithin(
    geometry::STGeomFromText('POLYGON((2.8 41.6, 3.0 41.6, 3.0 41.8, 2.8 41.8, 2.8 41.6))', 4326)
) = 1;

Sense equivalent per a rangs de dates

Igual que MySQL, SQL Server no disposa d'un tipus de rang indexable com daterange. Cal modelar el solapament amb dues columnes (data_inici, data_fi) i un índex B-Tree compost.

-- Oracle Spatial amb índex R-Tree
CREATE TABLE punts_interes (
    id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom         VARCHAR2(100),
    ubicacio    SDO_GEOMETRY
);

CREATE INDEX idx_punts_ubicacio
    ON punts_interes (ubicacio)
    INDEXTYPE IS MDSYS.SPATIAL_INDEX;

-- Trobar punts dins d'una distància de 1000 metres
SELECT nom FROM punts_interes p
WHERE SDO_WITHIN_DISTANCE(
    p.ubicacio,
    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(2.9, 41.7, NULL), NULL, NULL),
    'distance=1000 unit=METER'
) = 'TRUE';

Sense equivalent per a rangs de dates

Oracle tampoc té un tipus de rang indexable nadiu equivalent a daterange. La pràctica habitual és emmagatzemar data_inici/data_fi i indexar-les amb un B-Tree compost.

Índexs parcials: indexar només el que importa

Un índex parcial inclou una clàusula WHERE que limita quines files formen part de l'índex. Resulten molt útils quan les consultes freqüents sempre filtren per un subconjunt de dades petit.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Taula de comandes on el 95% estan en estat 'completada'
-- i les consultes operacionals sempre busquen les 'pendents'
CREATE INDEX idx_comandes_pendents ON comandes (data_creacio)
WHERE estat = 'pendent';

-- Aquesta consulta usa l'índex (condició coincideix amb la del WHERE de l'índex)
SELECT * FROM comandes
WHERE estat = 'pendent' AND data_creacio > '2026-06-01';

-- Altres exemples pràctics
-- Índex parcial per a columnes nullable (exclou els NULLs)
CREATE INDEX idx_usuaris_email_verificat ON usuaris (email)
WHERE email IS NOT NULL AND verificat = true;

-- Índex per a registres actius en una taula amb eliminació suau
CREATE INDEX idx_productes_actius ON productes (categoria_id, preu)
WHERE eliminat_en IS NULL;

MySQL/MariaDB no suporta índexs parcials (no existeix una clàusula WHERE a CREATE INDEX). Les alternatives habituals són crear una "generated column" booleana que resumeixi la condició i indexar-la, o aplicar el filtre a nivell d'aplicació/consulta sense l'avantatge de mida reduïda.

-- Alternativa: generated column + índex normal (no parcial,
-- però permet que l'optimitzador localitzi ràpid les files 'pendent')
ALTER TABLE comandes
    ADD COLUMN es_pendent TINYINT(1)
        GENERATED ALWAYS AS (estat = 'pendent') STORED,
    ADD INDEX idx_comandes_pendents (es_pendent, data_creacio);

SELECT * FROM comandes
WHERE es_pendent = 1 AND data_creacio > '2026-06-01';

SQL Server sí suporta índexs parcials, amb el nom de Filtered Index, i amb una sintaxi pràcticament idèntica a la de PostgreSQL.

-- Filtered Index: equivalent directe a l'índex parcial de PostgreSQL
CREATE NONCLUSTERED INDEX idx_comandes_pendents
ON comandes (data_creacio)
WHERE estat = 'pendent';

-- Aquesta consulta usa l'índex
SELECT * FROM comandes
WHERE estat = 'pendent' AND data_creacio > '2026-06-01';

-- Índex parcial per a columnes nullable
CREATE NONCLUSTERED INDEX idx_usuaris_email_verificat
ON usuaris (email)
WHERE email IS NOT NULL AND verificat = 1;

Oracle no té una clàusula CREATE INDEX ... WHERE, però existeix un truc clàssic: Oracle no indexa les files on totes les columnes indexades són NULL. Es pot simular un índex parcial amb un índex funcional que retorna NULL per a les files que es volen excloure.

-- Simulació d'índex parcial amb índex funcional
-- Les files amb estat != 'pendent' generen NULL i no s'indexen
CREATE INDEX idx_comandes_pendents
    ON comandes (CASE WHEN estat = 'pendent' THEN data_creacio END);

-- La consulta ha de reproduir exactament l'expressió de l'índex
SELECT * FROM comandes
WHERE CASE WHEN estat = 'pendent' THEN data_creacio END > DATE '2026-06-01';

Limitació pràctica

Aquest truc obliga a reescriure la condició de la consulta perquè coincideixi amb l'expressió de l'índex, cosa que el fa menys transparent que un índex parcial real de PostgreSQL o un Filtered Index de SQL Server.

L'avantatge és doble: l'índex és molt més petit (menys espai i manteniment) i les cerques sobre el subconjunt son més ràpides perquè l'índex és més compacte. D'entre els quatre motors, PostgreSQL i SQL Server ofereixen aquest avantatge de forma nativa i directa; MySQL/MariaDB i Oracle necessiten solucions alternatives.

Índexs funcionals: indexar el resultat d'una expressió

Quan les consultes apliquen funcions sobre columnes en el WHERE, el motor no pot usar un índex B-Tree normal sobre la columna original. La solució és crear un índex sobre l'expressió:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Sense índex funcional, aquesta consulta fa Seq Scan:
SELECT * FROM usuaris WHERE lower(email) = 'jdoe@exemple.cat';

-- Amb índex funcional, fa Index Scan:
CREATE INDEX idx_usuaris_email_ci ON usuaris (lower(email));

-- Altres exemples d'índexs funcionals
-- Índex sobre l'any d'una data (si no es pot particionar)
CREATE INDEX idx_vendes_any ON vendes (EXTRACT(year FROM data_venda));

-- Índex sobre una expressió de JSONB
CREATE INDEX idx_events_usuari ON events ((payload->>'usuari_id'));

-- MySQL 8.0.13+ i MariaDB suporten "functional key parts" directament
CREATE INDEX idx_usuaris_email_ci ON usuaris ((LOWER(email)));

-- La consulta ha d'usar la mateixa expressió per aprofitar l'índex:
SELECT * FROM usuaris WHERE LOWER(email) = 'jdoe@exemple.cat';

-- Índex sobre l'any d'una data
CREATE INDEX idx_vendes_any ON vendes ((YEAR(data_venda)));

SQL Server no té un índex funcional directe; cal crear una columna calculada (computed column) persistida i indexar-la.

ALTER TABLE usuaris
    ADD email_lower AS LOWER(email) PERSISTED;
CREATE INDEX idx_usuaris_email_ci ON usuaris (email_lower);

-- La consulta ha de filtrar per la columna calculada
SELECT * FROM usuaris WHERE email_lower = 'jdoe@exemple.cat';

-- Oracle suporta índexs funcionals nativament,
-- amb sintaxi gairebé idèntica a PostgreSQL:
CREATE INDEX idx_usuaris_email_ci ON usuaris (LOWER(email));

SELECT * FROM usuaris WHERE LOWER(email) = 'jdoe@exemple.cat';

-- Índex sobre l'any d'una data
CREATE INDEX idx_vendes_any ON vendes (EXTRACT(YEAR FROM data_venda));

Índex funcional i immutabilitat

L'expressió d'un índex funcional ha de ser immutable (o DETERMINISTIC, segons el motor): retornar sempre el mateix resultat per als mateixos arguments. lower()/LOWER(), EXTRACT()/YEAR() i els accessors JSON compleixen aquesta condició als quatre motors. PostgreSQL i Oracle indexen l'expressió directament; MySQL/MariaDB ho fa amb "functional key parts"; SQL Server requereix sempre una columna calculada intermèdia.

Índexs compostos: l'ordre de columnes importa

Un índex compost cobreix múltiples columnes. El motor el pot usar per a consultes que filtren per la primera columna, o per la primera i la segona, etc. (regla del prefix). No l'usa de forma eficient si la consulta filtra únicament per la segona columna sense mencionar la primera.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Índex sobre (pais, data_venda)
CREATE INDEX idx_vendes_pais_data ON vendes (pais, data_venda);

-- Usos vàlids:
SELECT * FROM vendes WHERE pais = 'ES';                         -- prefix
SELECT * FROM vendes WHERE pais = 'ES' AND data_venda > '2026-01-01';  -- prefix complet
SELECT * FROM vendes WHERE pais = 'ES' ORDER BY data_venda;   -- prefix + ordre

-- Ús invàlid (Seq Scan o Bitmap Scan, no Index Scan directe):
SELECT * FROM vendes WHERE data_venda > '2026-01-01';          -- no cobreix el prefix

CREATE INDEX idx_vendes_pais_data ON vendes (pais, data_venda);

SELECT * FROM vendes WHERE pais = 'ES';
SELECT * FROM vendes WHERE pais = 'ES' AND data_venda > '2026-01-01';
SELECT * FROM vendes WHERE pais = 'ES' ORDER BY data_venda;

-- No cobreix el prefix, no aprofita l'índex de forma eficient:
SELECT * FROM vendes WHERE data_venda > '2026-01-01';

CREATE NONCLUSTERED INDEX idx_vendes_pais_data ON vendes (pais, data_venda);

SELECT * FROM vendes WHERE pais = 'ES';
SELECT * FROM vendes WHERE pais = 'ES' AND data_venda > '2026-01-01';
SELECT * FROM vendes WHERE pais = 'ES' ORDER BY data_venda;

-- No cobreix el prefix:
SELECT * FROM vendes WHERE data_venda > '2026-01-01';

CREATE INDEX idx_vendes_pais_data ON vendes (pais, data_venda);

SELECT * FROM vendes WHERE pais = 'ES';
SELECT * FROM vendes WHERE pais = 'ES' AND data_venda > DATE '2026-01-01';
SELECT * FROM vendes WHERE pais = 'ES' ORDER BY data_venda;

-- No cobreix el prefix:
SELECT * FROM vendes WHERE data_venda > DATE '2026-01-01';

La recomanació general per a índexs compostos és: primer les columnes d'igualtat, després les de rang o ordenació. Els quatre motors segueixen la mateixa regla del prefix (leftmost prefix) i la sintaxi de creació és pràcticament idèntica.

Cost del manteniment dels índexs

Cada índex té un cost associat en operacions d'escriptura:

• INSERT: s'insereix una entrada a cada índex de la taula.
• UPDATE: equivaldria a un DELETE + INSERT a l'índex (si la columna indexada canvia).
• DELETE: s'elimina l'entrada de l'índex; sovint només es marca com a morta i un procés de manteniment l'elimina físicament més tard.
• Recollida d'espai mort: un procés de manteniment recull les entrades mortes dels índexs i retorna espai al sistema (VACUUM a PostgreSQL, OPTIMIZE TABLE a MySQL/MariaDB, etc.).
• Reconstrucció de l'índex: reconstrueix l'índex completament, eliminant la fragmentació acumulada (REINDEX a PostgreSQL, ALTER INDEX ... REBUILD a SQL Server i Oracle, vegeu el detall a cada pestanya).

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Reconstruir un índex sense bloquejar la taula (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_vendes_client;

-- Veure la mida dels índexs d'una taula
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS mida
FROM pg_indexes
WHERE tablename = 'vendes'
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- Reconstrueix la taula i tots els seus índexs, recupera espai
OPTIMIZE TABLE vendes;

-- Actualitza les estadístiques que usa l'optimitzador
ANALYZE TABLE vendes;

-- Veure la mida dels índexs d'una taula
SELECT index_name, stat_value * @@innodb_page_size AS mida_bytes
FROM mysql.innodb_index_stats
WHERE table_name = 'vendes' AND stat_name = 'size';

-- Reconstrucció completa, equivalent a REINDEX
ALTER INDEX idx_vendes_client ON vendes REBUILD;

-- Desfragmentació en calent (menys intrusiva que REBUILD)
ALTER INDEX idx_vendes_client ON vendes REORGANIZE;

-- Actualitza les estadístiques
UPDATE STATISTICS vendes;

-- Veure la mida dels índexs d'una taula
SELECT i.name, ps.avg_fragmentation_in_percent, ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('vendes'), NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id;

-- Reconstrucció sense bloquejar la taula, equivalent a REINDEX CONCURRENTLY
ALTER INDEX idx_vendes_client REBUILD ONLINE;

-- Actualitza les estadístiques
EXEC DBMS_STATS.GATHER_TABLE_STATS('ESQUEMA', 'VENDES');

-- Veure la mida dels índexs d'una taula
SELECT segment_name, bytes / 1024 / 1024 AS mida_mb
FROM user_segments
WHERE segment_name = 'IDX_VENDES_CLIENT';

EXPLAIN ANALYZE: veure l'índex en acció

La millor manera de confirmar que un índex s'usa és llegir el pla d'execució:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Sense índex: Seq Scan
EXPLAIN ANALYZE SELECT * FROM vendes WHERE client_id = 4231;
-- Seq Scan on vendes  (cost=0.00..289543.00 rows=142 width=56)
--                     (actual time=0.123..1854.341 rows=142 loops=1)

-- Amb índex B-Tree: Index Scan
CREATE INDEX idx_vendes_client ON vendes (client_id);
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)

-- Bitmap Index Scan: per a consultes que retornen moltes files
EXPLAIN ANALYZE SELECT * FROM vendes
WHERE client_id BETWEEN 4000 AND 5000;
-- Bitmap Heap Scan on vendes  (cost=234.12..8723.45 rows=14200 width=56)
--   ->  Bitmap Index Scan on idx_vendes_client
--         Index Cond: ((client_id >= 4000) AND (client_id <= 5000))

El Bitmap Index Scan és un mode intermedi: primer recull tots els punteres de l'índex (creant un mapa de bits), i després accedeix al disc en ordre físic per eficiència. S'usa quan el nombre de files retornades és massa gran per a un Index Scan directe però massa petit per a un Seq Scan.

-- Pla d'execució bàsic
EXPLAIN SELECT * FROM vendes WHERE client_id = 4231;

-- Des de MySQL 8.0.18, pla amb mètriques reals d'execució
-- (format d'arbre, conceptualment similar a EXPLAIN ANALYZE de PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM vendes WHERE client_id = 4231;
-- -> Index lookup on vendes using idx_vendes_client (client_id=4231)
--    (cost=12.50 rows=142) (actual time=0.045..1.234 rows=142 loops=1)

-- Mètriques textuals d'I/O i temps
SET STATISTICS IO, TIME ON;
SELECT * FROM vendes WHERE client_id = 4231;

-- Pla d'execució en format text (equivalent textual a EXPLAIN)
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM vendes WHERE client_id = 4231;
GO
SET SHOWPLAN_TEXT OFF;

-- A SSMS, el pla gràfic d'execució (Ctrl+M) és l'eina habitual
-- per inspeccionar visualment Index Seek vs Table/Clustered Index Scan.

-- Pla estimat (no executa la consulta)
EXPLAIN PLAN FOR
SELECT * FROM vendes WHERE client_id = 4231;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Pla real amb estadístiques d'execució, equivalent a EXPLAIN ANALYZE
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM vendes WHERE client_id = 4231;

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

---

AC5074/02/01 — Miniactivitat

Donada la taula vendes amb 100 milions de files i les columnes (id, data, client_id, producte_id, quantitat, preu_unitari, pais), analitzeu les consultes següents i determineu quins índexs cal crear, de quin tipus i per quin motiu: - Consulta 1: SELECT * FROM vendes WHERE pais = 'ES' AND data BETWEEN '2026-01-01' AND '2026-06-30' - Consulta 2: SELECT SUM(quantitat * preu_unitari) FROM vendes WHERE client_id = 12345 - Consulta 3: SELECT * FROM vendes WHERE data = CURRENT_DATE ORDER BY preu_unitari DESC LIMIT 10 - Consulta 4: SELECT * FROM vendes WHERE pais = 'FR' AND estat_enviament = 'pendent' - Consulta 5: SELECT * FROM vendes WHERE lower(pais) = 'es' Per a cada consulta, indiqueu: tipus d'índex, columnes i ordre, si és parcial o funcional, i raoneu per què descarteu altres alternatives. Lliureu un fitxer .sql amb les instruccions CREATE INDEX comentades.

Aquesta activitat es pot resoldre indistintament per a qualsevol dels quatre motors estudiats (PostgreSQL, MySQL/MariaDB, SQL Server o Oracle); adapteu la sintaxi de CREATE INDEX al motor triat segons les equivalències vistes en aquest tema.