PostgreSQL: índices de varias columnas

En PostgreSQL, el

Puede crear un índice en más de una columna de una tabla. Este índice se denomina índice de varias columnas, índice compuesto, índice combinado o índice concatenado. Un índice de varias columnas puede tener un máximo de 32 columnas de una tabla. El límite se puede cambiar modificando pg_config_manual.h al compilar PostgreSQL. Además, solo los tipos de índice B-tree, GIST, GIN y BRIN admiten índices de varias columnas.

La siguiente sintaxis muestra cómo crear un índice de varias columnas:

Syntax: 
CREATE INDEX index_name
ON table_name(a, b, c, ...);

Al definir un índice de varias columnas, se deben colocar las columnas que se usan con frecuencia en la cláusula WHERE al principio de la lista de columnas y las columnas que se usan con menos frecuencia en la condición posterior.

En la sintaxis anterior, el optimizador de PostgreSQL considerará usar el índice en los siguientes casos:

WHERE a = v1 and b = v2 and c = v3;

o,

WHERE a = v1 and b = v2;

o,

WHERE a = v1;

WHERE  c = v3;

o,

WHERE b = v2 and c = v3;

Ejemplo:

gente

CREATE TABLE people(
    id INT GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

Puede agregar datos a las personas a través de este archivo.

adams

SELECT
    *
FROM
    people
WHERE
    last_name = 'Adams';

Esto dará como resultado lo siguiente:

Como se muestra claramente en el resultado, PostgreSQL realizó el escaneo secuencial en la tabla de personas para encontrar las filas correspondientes porque no había un índice definido para la columna last_name.

Definamos un índice de árbol B en las columnas last_name y first_name. Suponiendo que la búsqueda de personas por el apellido es más frecuente que por el nombre, definimos el índice con el siguiente orden de columnas:

CREATE INDEX idx_people_names 
ON people (last_name, first_name);

adams

EXPLAIN SELECT
    *
FROM
    people
WHERE
    last_name = 'Adams';

Esto generará lo siguiente:

AdamsLou:

SELECT
    *
FROM
    people
WHERE
    last_name = 'Adams'
AND first_name = 'Lou';

Esto conducirá a lo siguiente:

DÓNDE

EXPLAIN SELECT
    *
FROM
    people
WHERE
    last_name = 'Adams'
AND first_name = 'Lou';

lou

EXPLAIN SELECT
    *
FROM
    people
WHERE
    first_name = 'Lou';

Producción: