Optimizar el rango de consultas de marca de tiempo de Postgres

Tengo la siguiente tabla e índices definidos:

CREATE TABLE ticket ( wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass), eid bigint, created timestamp with time zone NOT NULL DEFAULT now(), status integer NOT NULL DEFAULT 0, argsxml text, moduleid character varying(255), source_id bigint, file_type_id bigint, file_name character varying(255), status_reason character varying(255), ... ) 

Creé un índice en la marca de tiempo created siguiente manera:

 CREATE INDEX ticket_1_idx ON ticket USING btree (created ); 

y aquí está mi consulta

 select * from ticket where created between '2012-12-19 00:00:00' and '2012-12-20 00:00:00' 

Esto funcionó bien hasta que la cantidad de registros comenzó a crecer (alrededor de 5 millones) y ahora le está tomando mucho tiempo regresar.

Explicar el análisis revela esto:

 "Index Scan using ticket_1_idx on ticket (cost=0.00..10202.64 rows=52543 width=1297) (actual time=0.109..125.704 rows=53340 loops=1)" " Index Cond: ((created >= '2012-12-19 00:00:00+00'::timestamp with time zone) AND (created <= '2012-12-20 00:00:00+00'::timestamp with time zone))" "Total runtime: 175.853 ms" 

Hasta ahora he intentado configurar

 random_page_cost = 1.75 effective_cache_size = 3 

También creado

 create CLUSTER ticket USING ticket_1_idx; 

Nada funciona. ¿Qué estoy haciendo mal? ¿Por qué está seleccionando el escaneo secuencial? Se supone que los índices hacen que la consulta sea rápida. ¿Hay algo que se pueda hacer para optimizarlo?

CLUSTER

Si tiene la intención de usar CLUSTER , la syntax mostrada no es válida.

create CLUSTER ticket USING ticket_1_idx;

Corre una vez:

 CLUSTER ticket USING ticket_1_idx; 

Esto puede ayudar mucho con conjuntos de resultados más grandes. No tanto por una sola fila devuelta.
Postgres recuerda qué índice usar para llamadas posteriores. Si su tabla no es de solo lectura, el efecto se deteriora con el tiempo y necesita volver a ejecutar en ciertos intervalos:

 CLUSTER ticket; 

Posiblemente solo en particiones volátiles. Vea abajo.

Sin embargo , si tiene muchas actualizaciones, CLUSTER (o VACUUM FULL ) puede ser malo para el rendimiento. La cantidad correcta de hinchazón permite a UPDATE colocar nuevas versiones de fila en la misma página de datos y evita la necesidad de extender físicamente el archivo subyacente en el sistema operativo con demasiada frecuencia. Puede utilizar un FILLFACTOR cuidadosamente afinado para obtener lo mejor de ambos mundos:

  • Factor de relleno para un índice secuencial que es PK

pg_repack

CLUSTER toma un locking exclusivo en la mesa, lo que puede ser un problema en un entorno multiusuario. Citando el manual:

Cuando se agrupa una tabla, se adquiere un locking ACCESS EXCLUSIVE . Esto evita que otras operaciones de base de datos (tanto de lectura como de escritura ) operen en la tabla hasta que el CLUSTER finalice.

Negrita énfasis mío. Considere la alternativa pg_repack :

A diferencia de CLUSTER y VACUUM FULL , funciona en línea, sin mantener un locking exclusivo en las tablas procesadas durante el procesamiento. pg_repack es eficiente para arrancar, con un rendimiento comparable al de usar CLUSTER directamente.

y:

pg_repack debe tomar un locking exclusivo al final de la reorganización.

La versión 1.3.1 funciona con:

PostgreSQL 8.3, 8.4, 9.0, 9.1, 9.2, 9.3, 9.4

La versión 1.4.2 funciona con:

PostgreSQL 9.1, 9.2, 9.3, 9.4, 9.5, 9.6, 10

Consulta

La consulta es lo suficientemente simple como para no causar ningún problema de rendimiento per se.

Sin embargo, una palabra sobre la corrección : el constructo BETWEEN incluye bordes. Su consulta selecciona todo el 19 de diciembre, más los registros del 20 de diciembre a las 00:00 horas. Es un requisito extremadamente poco probable . Lo más probable es que realmente quieras:

 SELECT * FROM ticket WHERE created >= '2012-12-19 0:0' AND created < '2012-12-20 0:0'; 

Actuación

En primer lugar, preguntas:

¿Por qué está seleccionando el escaneo secuencial?

Su salida EXPLAIN muestra claramente una exploración de índice , no una exploración de tabla secuencial. Debe haber algún tipo de malentendido.

Si se le presiona para obtener un mejor rendimiento, es posible que pueda mejorar las cosas. Pero la información de fondo necesaria no está en la pregunta. Las posibles opciones incluyen:

  • Solo puede consultar las columnas requeridas en lugar de * para reducir el costo de transferencia (y posiblemente otros beneficios de rendimiento).

  • Podrías mirar las particiones y poner segmentos de tiempo prácticos en tablas separadas. Agregue índices a las particiones según sea necesario.

  • Si el particionamiento no es una opción, otra técnica relacionada pero menos intrusiva sería agregar uno o más índices parciales .
    Por ejemplo, si consulta principalmente el mes actual , puede crear el siguiente índice parcial:

     CREATE INDEX ticket_created_idx ON ticket(created) WHERE created >= '2012-12-01 00:00:00'::timestamp; 

    CREATE un nuevo índice justo antes del comienzo de un nuevo mes. Puede automatizar fácilmente la tarea con un trabajo cron. Opcionalmente DROP índices parciales para viejos meses más tarde.

  • Mantenga el índice total además de CLUSTER (que no puede operar en índices parciales). Si los registros antiguos nunca cambian, la partición de la tabla ayudaría mucho a esta tarea, ya que solo necesita volver a agrupar las particiones más nuevas. Por otra parte, si los registros nunca cambian en absoluto, es probable que no necesite CLUSTER .

Si combina los dos últimos pasos, el rendimiento debería ser impresionante.

Conceptos básicos de rendimiento

Es posible que te pierdas uno de los conceptos básicos. Se aplican todos los consejos de rendimiento habituales:

Intereting Posts