Ejemplo de índices parciales de Postgresql

A índice parcial es un índice construido sobre un subconjunto de una tabla; el subconjunto está definido por una expresión condicional (llamada predicado del índice parcial). El índice contiene entradas solo para aquellas filas de la tabla que satisfacen el predicado. Los índices parciales son una característica especializada, pero hay varias situaciones en las que son útiles.

Una de las principales razones para utilizar un índice parcial es evitar la indexación de valores comunes. Dado que una consulta que busca un valor común (uno que representa más de un pequeño porcentaje de todas las filas de la tabla) no usará el índice de todos modos, no tiene sentido mantener esas filas en el índice en absoluto. Esto reduce el tamaño del índice, lo que acelerará las consultas que utilizan el índice. También acelerará muchas operaciones de actualización de tablas porque no es necesario actualizar el índice en todos los casos. El ejemplo 11.1 muestra una posible aplicación de esta idea.

Ejemplo 11.1. Configuración de un índice parcial para excluir valores comunes

Suponga que está almacenando registros de acceso al servidor web en una base de datos. La mayoría de los accesos se originan en el rango de direcciones IP de su organización, pero algunos provienen de otros lugares (por ejemplo, empleados en conexiones de acceso telefónico). Si sus búsquedas por IP son principalmente para accesos externos, probablemente no necesite indexar el rango de IP que corresponde a la subred de su organización.

Suponga una tabla como esta:

CREATETABLE access_log (
    url varchar,
    client_ip inet,...);

Para crear un índice parcial que se adapte a nuestro ejemplo, use un comando como este:

CREATEINDEX access_log_client_ip_ix ON access_log (client_ip)WHERENOT(client_ip > inet '192.168.100.0'AND
           client_ip < inet '192.168.100.255');

Una consulta típica que puede usar este índice sería:

SELECT*FROM access_log
WHERE url ='/index.html'AND client_ip = inet '212.78.10.32';

Aquí, la dirección IP de la consulta está cubierta por el índice parcial. La siguiente consulta no puede usar el índice parcial, ya que usa una dirección IP que está excluida del índice:

SELECT*FROM access_log
WHERE url ='/index.html'AND client_ip = inet '192.168.100.23';

Observe que este tipo de índice parcial requiere que los valores comunes estén predeterminados, por lo que dichos índices parciales se utilizan mejor para distribuciones de datos que no cambian. Dichos índices se pueden volver a crear ocasionalmente para ajustarse a las nuevas distribuciones de datos, pero esto agrega esfuerzo de mantenimiento.

Otro uso posible de un índice parcial es excluir valores del índice que no interesan a la carga de trabajo de consulta típica; esto se muestra en el ejemplo 11.2. Esto da como resultado las mismas ventajas enumeradas anteriormente, pero evita “poco interesante“ valores de acceso a través de ese índice, incluso si un escaneo de índice podría ser rentable en ese caso. Obviamente, la creación de índices parciales para este tipo de escenario requerirá mucho cuidado y experimentación.

Ejemplo 11.2. Configurar un índice parcial para excluir valores poco interesantes

Si tiene una tabla que contiene tanto pedidos facturados como no facturados, donde los pedidos no facturados ocupan una pequeña fracción del total de la tabla y, sin embargo, esas son las filas a las que se accede más, puede mejorar el rendimiento creando un índice solo en las filas no facturadas. El comando para crear el índice se vería así:

CREATEINDEX orders_unbilled_index ON orders (order_nr)WHERE billed isnottrue;

Una posible consulta para utilizar este índice sería:

SELECT*FROM orders WHERE billed isnottrueAND order_nr <10000;

Sin embargo, el índice también se puede utilizar en consultas que no involucran order_nr en absoluto, por ejemplo:

SELECT*FROM orders WHERE billed isnottrueAND amount >5000.00;

Esto no es tan eficiente como un índice parcial en el amount columna sería, ya que el sistema tiene que escanear todo el índice. Sin embargo, si hay relativamente pocos pedidos no facturados, usar este índice parcial solo para encontrar los pedidos no facturados podría ser una ventaja.

Tenga en cuenta que esta consulta no puede utilizar este índice:

SELECT*FROM orders WHERE order_nr =3501;

El pedido 3501 puede estar entre los pedidos facturados o no facturados.

El ejemplo 11.2 también ilustra que la columna indexada y la columna usada en el predicado no necesitan coincidir. PostgreSQL admite índices parciales con predicados arbitrarios, siempre que solo estén involucradas las columnas de la tabla que se indexa. Sin embargo, tenga en cuenta que el predicado debe coincidir con las condiciones utilizadas en las consultas que se supone que se benefician del índice. Para ser precisos, un índice parcial se puede utilizar en una consulta solo si el sistema puede reconocer que el WHERE La condición de la consulta implica matemáticamente el predicado del índice. PostgreSQL no tiene un demostrador de teoremas sofisticado que pueda reconocer expresiones matemáticamente equivalentes que están escritas en diferentes formas. (No solo es extremadamente difícil crear un demostrador de teoremas general, sino que probablemente sería demasiado lento para ser de utilidad real). El sistema puede reconocer implicaciones simples de desigualdad, por ejemplo “x <1“ implica “x <2“; de lo contrario, la condición del predicado debe coincidir exactamente con parte de la consulta WHERE condición o el índice no se reconocerá como utilizable. La coincidencia se lleva a cabo en el momento de la planificación de la consulta, no en el tiempo de ejecución. Como resultado, las cláusulas de consulta parametrizadas no funcionan con un índice parcial. Por ejemplo, una consulta preparada con un parámetro podría especificar “x “ que nunca implicará “x <2“ para todos los valores posibles del parámetro.

Un tercer uso posible para índices parciales no requiere que el índice se use en consultas. La idea aquí es crear un índice único sobre un subconjunto de una tabla, como en el Ejemplo 11.3. Esto impone la unicidad entre las filas que satisfacen el predicado de índice, sin restringir las que no lo hacen.

Ejemplo 11.3. Configuración de un índice único parcial

Suponga que tenemos una tabla que describe los resultados de la prueba. Deseamos asegurarnos de que solo haya una “exitoso“ entrada para una determinada combinación de tema y objetivo, pero puede haber cualquier número de “fracasado“ entradas. Aquí hay una forma de hacerlo:

CREATETABLE tests (
    subject text,
    target text,
    success boolean,...);CREATEUNIQUEINDEX tests_success_constraint ON tests (subject, target)WHERE success;

Este es un enfoque particularmente eficiente cuando hay pocas pruebas exitosas y muchas no exitosas. También es posible permitir solo una null en una columna creando un índice parcial único con un IS NULL restricción.

Por último, también se puede utilizar un índice parcial para anular las opciones del plan de consulta del sistema. Además, los conjuntos de datos con distribuciones peculiares pueden hacer que el sistema use un índice cuando realmente no debería. En ese caso, el índice se puede configurar para que no esté disponible para la consulta infractora. Normalmente, PostgreSQL toma decisiones razonables sobre el uso del índice (por ejemplo, las evita cuando recupera valores comunes, por lo que el ejemplo anterior realmente solo guarda el tamaño del índice, no es necesario para evitar el uso del índice), y las elecciones de planes extremadamente incorrectas son la causa de un error. reporte.

Tenga en cuenta que la configuración de un índice parcial indica que sabe al menos tanto como sabe el planificador de consultas, en particular, sabe cuándo un índice podría ser rentable. Formar este conocimiento requiere experiencia y comprensión de cómo funcionan los índices en PostgreSQL. En la mayoría de los casos, la ventaja de un índice parcial sobre un índice regular será mínima. Hay casos en los que son bastante contraproducentes, como en el ejemplo 11.4.

Ejemplo 11.4. No utilice índices parciales como sustituto de las particiones

Puede tener la tentación de crear un gran conjunto de índices parciales que no se superpongan, por ejemplo

CREATEINDEX mytable_cat_1 ON mytable (data)WHERE category =1;CREATEINDEX mytable_cat_2 ON mytable (data)WHERE category =2;CREATEINDEX mytable_cat_3 ON mytable (data)WHERE category =3;...CREATEINDEX mytable_cat_N ON mytable (data)WHERE category = N;

¡Esta es una mala idea! Casi con certeza, estará mejor con un único índice no parcial, declarado como

CREATEINDEX mytable_cat_data ON mytable (category,data);

(Coloque la columna de categoría primero, por las razones descritas en la Sección 11.3.) Si bien una búsqueda en este índice más grande podría tener que descender a través de un par de niveles de árbol más que una búsqueda en un índice más pequeño, es casi seguro que eso será más barato que el Esfuerzo del planificador necesario para seleccionar el apropiado de los índices parciales. El núcleo del problema es que el sistema no comprende la relación entre los índices parciales y probará laboriosamente cada uno para ver si es aplicable a la consulta actual.

Si su tabla es lo suficientemente grande como para que un solo índice sea realmente una mala idea, debería considerar el uso de particiones en su lugar (consulte la Sección 5.11). Con ese mecanismo, el sistema comprende que las tablas y los índices no se superponen, por lo que es posible un mejor rendimiento.

Puede encontrar más información sobre índices parciales en [ston89b], [olson93], y [seshadri95].