resueltos - optimizar consultas sql server 2008 r2

Mejora de la velocidad de consulta: SELECT simple en la tabla de postgres grandes

Question

Tengo problemas con respecto a la velocidad en una consulta SELECT en una base de datos de Postgres.

Tengo una tabla con dos columnas enteras como clave: (int1, int2) Esta tabla tiene alrededor de 70 millones de filas.

Necesito hacer dos tipos de consultas SELECT simples en este entorno:

SELECT * FROM table WHERE int1=X;
SELECT * FROM table WHERE int2=X;

Estos dos seleccionados devuelven alrededor de 10.000 filas cada uno de estos 70 millones. Para que esto funcione lo más rápido posible, pensé en usar dos índices HASH, uno para cada columna. Desafortunadamente los resultados no son tan buenos:

                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on lec_sim  (cost=232.21..25054.38 rows=6565 width=36) (actual time=14.759..23339.545 rows=7871 loops=1)
   Recheck Cond: (lec2_id = 11782)
   ->  Bitmap Index Scan on lec_sim_lec2_hash_ind  (cost=0.00..230.56 rows=6565 width=0) (actual time=13.495..13.495 rows=7871 loops=1)
         Index Cond: (lec2_id = 11782)
 Total runtime: 23342.534 ms
(5 rows)

Este es un ejemplo de EXPLAIN ANALYZE de una de estas consultas. Está tomando alrededor de 23 segundos. Mis expectativas son obtener esta información en menos de un segundo.

Estos son algunos parámetros de la configuración db postgres:

work_mem = 128MB
shared_buffers = 2GB
maintenance_work_mem = 512MB
fsync = off
synchronous_commit = off
effective_cache_size = 4GB

Cualquier ayuda, comentario o pensamiento sería realmente apreciado.

Gracias de antemano.

Answer 1

Extrayendo mis comentarios en una respuesta: la búsqueda de índice aquí fue muy rápida: se dedicó todo el tiempo a recuperar las filas reales. 23 segundos / 7871 filas = 2,9 milisegundos por fila, lo cual es razonable para recuperar datos que están dispersos en el subsistema de discos. Las búsquedas son lentas; puede a) ajustar su conjunto de datos en la memoria RAM, b) comprar SSD, o c) organizar sus datos con anticipación para minimizar las búsquedas.

PostgreSQL 9.2 tiene una característica llamada análisis de solo índice que le permite (generalmente) responder consultas sin acceder a la tabla. Puede combinar esto con la propiedad de índice btree de mantener automáticamente el orden para que esta consulta sea rápida. Mencionas int1 , int2 y dos flotadores:

CREATE INDEX sometable_int1_floats_key ON sometable (int1, float1, float2);
CREATE INDEX sometable_int2_floats_key ON sometable (int2, float1, float2);

SELECT float1,float2 FROM sometable WHERE int1=<value>; -- uses int1 index
SELECT float1,float2 FROM sometable WHERE int2=<value>; -- uses int2 index

Tenga en cuenta también que esto no borra mágicamente las búsquedas en el disco, solo las mueve del tiempo de consulta al tiempo de inserción. También le cuesta espacio de almacenamiento, ya que está duplicando los datos. Sin embargo, esta es probablemente la compensación que desea.

Answer 2

Gracias willglyn Como notó, el problema fue la búsqueda a través del HD y no la búsqueda de los índices. Propuso muchas soluciones, como cargar el conjunto de datos en la memoria RAM o comprar un SSD HD. Pero olvidando estos dos, que implican administrar cosas fuera de la base de datos, propuso dos ideas:

1. Reorganizar los datos para reducir la búsqueda de los datos.
2. Utilice la característica de PostgreSQL 9.2 "escaneos de solo índice"

Como estoy bajo un servidor PostgreSQL 9.1, decidí tomar la opción "1".

Hice una copia de la mesa. Así que ahora tengo la misma tabla con los mismos datos dos veces. Creé un índice para cada uno, el primero fue indexado por (int1) y el segundo por (int2). Luego los agrupé a ambos (tabla CLUSTER USANDO ind_intX) por sus índices respectivos.

Estoy publicando ahora un ANALIZAR EXPLICACIÓN de la misma consulta, hecho en una de estas tablas agrupadas:

QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using lec_sim_lec2id_ind on lec_sim_lec2id (cost=0.00..21626.82 rows=6604 width=36) (actual time=0.051..1.500 rows=8119 loops=1) Index Cond: (lec2_id = 12300) Total runtime: 1.822 ms (3 rows)

Ahora la búsqueda es realmente rápida. Bajé de 23 segundos a ~ 2 milisegundos, lo cual es una mejora impresionante. Creo que este problema está resuelto para mí, espero que esto también sea útil para otras personas que experimentan el mismo problema.

Muchas gracias Willglynn.

Answer 3

Para una línea simple y efectiva, si tiene un almacenamiento rápido de estado sólido en su máquina postgres, intente configurar:

random_page_cost=1.0

En su en su postgresql.conf .

El valor predeterminado es random_page_cost=4.0 y está optimizado para el almacenamiento con tiempos de búsqueda elevados, como los discos giratorios antiguos. Esto cambia el cálculo del costo de búsqueda y depende menos de su memoria (que en última instancia podría cambiarse de todos modos)

Solo esta configuración mejoró mi consulta de filtrado de 8 segundos a 2 segundos en una tabla larga con un par de millones de registros.

La otra mejora importante vino de hacer índices con todas las columnas booleen en mi tabla. Esto redujo la consulta de 2 segundos a aproximadamente 1 segundo. Compruebe la respuesta de @ willglynn para eso.

¡Espero que esto ayude!

Answer 4

Tuve un caso de consultas súper lentas en las que se realizaron de una a varias combinaciones simples (en PG v9.1) entre una tabla que tenía 33 millones de filas a una tabla secundaria que tenía un tamaño de 2.4 mil millones de filas. Realicé un CLÚSTER en el índice de clave externa para la tabla secundaria, pero descubrí que esto no resolvía mi problema con los tiempos de espera de consulta, ni siquiera para las consultas más simples. Ejecutar ANALYZE tampoco solucionó el problema.

Lo que hizo una gran diferencia fue realizar un VACUUM manual tanto en la tabla principal como en la tabla secundaria. Incluso cuando la tabla de padres estaba completando su proceso de VACÍO, pasé de los tiempos de espera de 10 minutos a los resultados que regresan en un segundo.

Lo que estoy quitando de esto es que las operaciones regulares de VACUUM aún son críticas, incluso para v9.1. La razón por la que hice esto fue que noté que el autovacuum no se había ejecutado en ninguna de las mesas durante al menos dos semanas, y que desde entonces se habían producido muchos cambios e inserciones. Puede ser que necesite mejorar el activador de vacío automático para solucionar este problema en el futuro, pero lo que puedo decir es que una tabla de 640 GB con un par de miles de millones de filas funciona bien si se limpia todo. Todavía no he tenido que particionar la tabla para obtener un buen rendimiento.