performance - postgres - optimizar consultas sql server 2008 r2
¿Por qué el rendimiento de la consulta PostgresQL disminuye con el tiempo, pero se restaura al reconstruir el índice? (5)
De acuerdo con esta page en el manual, indexes don''t need to be maintained . Sin embargo, nos estamos ejecutando con una tabla PostgresQL que tiene una tasa continua de updates , deletes e inserts que con el tiempo (unos pocos días) ve una degradación de consulta significativa. Si eliminamos y volvemos a crear el índice, se restaura el rendimiento de la consulta.
Estamos utilizando la configuración de la caja.
La tabla en nuestra prueba está comenzando actualmente vacía y crece a medio millón de filas. Tiene una fila bastante grande (muchos campos de texto).
Estamos searching based of an index, not the primary key (confirmé que se está utilizando el índice, al menos en condiciones normales)
La tabla se está utilizando como un almacén persistente para un solo proceso. Usando PostgresQL en Windows con un cliente Java.
Estoy dispuesto a renunciar a insert and update performance para mantener el rendimiento de la consulta.
Estamos considerando la arquitectura rearchitecting de la aplicación para que los datos se distribuyan en varias tablas dinámicas de una manera que nos permita eliminar y reconstruir los índices periódicamente sin afectar la aplicación. Sin embargo, como siempre, hay una crisis de tiempo para hacer que esto funcione y sospecho que nos falta algo básico en nuestra configuración o uso.
Hemos considerado forcing vacuuming y la rebuild to run at certain times , pero sospecho que el locking period for such an action would cause our query to block . Esta puede ser una opción, pero hay algunas implicaciones en tiempo real (ventanas de 3-5 segundos) que requieren otros cambios en nuestro código.
Información adicional: Tabla e índice.
CREATE TABLE icl_contacts
(
id bigint NOT NULL,
campaignfqname character varying(255) NOT NULL,
currentstate character(16) NOT NULL,
xmlscheduledtime character(23) NOT NULL,
...
25 or so other fields. Most of them fixed or varying character fiel
...
CONSTRAINT icl_contacts_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE icl_contacts OWNER TO postgres;
CREATE INDEX icl_contacts_idx
ON icl_contacts
USING btree
(xmlscheduledtime, currentstate, campaignfqname);
Analizar:
Limit (cost=0.00..3792.10 rows=750 width=32) (actual time=48.922..59.601 rows=750 loops=1)
-> Index Scan using icl_contacts_idx on icl_contacts (cost=0.00..934580.47 rows=184841 width=32) (actual time=48.909..55.961 rows=750 loops=1)
Index Cond: ((xmlscheduledtime < ''2010-05-20T13:00:00.000''::bpchar) AND (currentstate = ''SCHEDULED''::bpchar) AND ((campaignfqname)::text = ''.main.ee45692a-6113-43cb-9257-7b6bf65f0c3e''::text))
Y, sí, soy consciente de que hay una variedad de cosas we could do to normalize and improve the design of this table . Algunas de estas opciones pueden estar disponibles para nosotros.
Mi enfoque en esta pregunta es sobre how PostgresQL is managing the index and query over time (understand why, not just fix) . Si se hiciera una vez más o se reformulara significativamente, habría muchos cambios.
¿Es el valor ''2010-05-20T13: 00: 00.000'' con el que se compara xmlscheduledtime, parte de SQL, o se proporciona como un parámetro?
Al planear cómo ejecutar la consulta, decir que un campo debe ser menor que un parámetro provisto con un valor aún desconocido no le da mucho a PostgreSQL para continuar. No sabe si eso coincidirá con casi todas las filas, o apenas con alguna de las filas.
Leer sobre cómo el planificador usa las estadísticas ayuda enormemente cuando se trata de averiguar por qué su base de datos está usando los planes que es.
Puede obtener un mejor rendimiento de selección cambiando el orden de los campos en ese índice complejo, o creando un nuevo índice, con los campos ordenados (campaignfqname, currentstate, xmlscheduledtime) ya que el índice lo llevará directamente al nombre de la campaña fq y al estado actual en el que está interesado, y la exploración de índice sobre el rango xmlscheduledtime serán todas las filas que está buscando.
En cuanto al rendimiento, el uso de cadenas para almacenar el tiempo y la información de estado es un cuello de botella. En primer lugar, los índices en los textos son extremadamente ineficientes, ya que comparar dos veces en el mismo día necesita al menos 11 comparaciones (en el formato que usó), sin embargo, al usar el tipo de tiempo se puede reducir a una sola comparación. Esto también afecta el tamaño del índice, y es difícil buscar en un índice grande, y la db no lo guardará en la memoria. Las mismas consideraciones se aplican a la columna de estado. Si representa un pequeño conjunto de estados, debe usar números enteros asignados a estados, esto reducirá los nodos del índice y el tamaño del índice en consecuencia. Además, este índice será inútil incluso si utiliza estos tipos incorporados si no especifica la hora real en su consulta.
Eso es un caso de libro de texto. Debes configurar autovacuum para ser mucho más agresivo.
Esto me huele a índice hinchado. Te referiré a esta página
http://www.postgresql.org/docs/8.3/static/routine-reindex.html
que dice en la parte inferior:
Además, para los índices de árbol B, es más rápido acceder a un índice recién construido que a uno que se ha actualizado muchas veces, porque las páginas lógicamente adyacentes también suelen estar físicamente adyacentes en un índice recién creado. (Esta consideración no se aplica actualmente a los índices que no son de árbol B). Podría valer la pena reindexar periódicamente solo para mejorar la velocidad de acceso.
Lo que parece estar en conflicto con la página a la que hizo referencia diciendo que los índices "no requieren mantenimiento ni ajuste".
¿Has intentado "crear índice al mismo tiempo"?
La aspiradora automática debe resolver el problema, siempre que lo haya configurado para el rendimiento deseado.
Notas: VACÍO COMPLETO: esto reconstruirá las estadísticas de la tabla y reclamará cargas de espacio en disco. Bloquea toda la mesa.
VACÍO: esto reconstruirá las estadísticas de la tabla y reclamará algo de espacio en el disco. Puede ejecutarse en paralelo con el sistema de producción, pero genera una gran cantidad de IO que puede afectar el rendimiento.
ANALIZAR: esto reconstruirá las estadísticas del planificador de consultas. Esto se desencadena con VACUUM, pero se puede ejecutar por sí solo.