performance - tunear - Cómo entender un EXPLICAR ANALIZAR

tuning postgresql 10

Question

No estoy muy familiarizado con los resultados de EXPLAIN ANALYZE, tengo un gran problema con mis consultas demasiado lentas. Intenté leer cómo interpretar los resultados de las consultas de explicación, pero aún no sé qué debería buscar y qué podría estar mal. Tengo la sensación de que hay una gran luz roja parpadeando en alguna parte, simplemente no la veo.

Entonces la consulta es bastante simple, se ve así:

EXPLAIN ANALYZE SELECT "cars".* FROM "cars" WHERE "cars"."sales_state" = ''onsale'' AND "cars"."brand" = ''BMW'' AND "cars"."model_name" = ''318i'' AND "cars"."has_auto_gear" = TRUE  LIMIT 25 OFFSET 0

Y el resultado así:

Limit  (cost=0.00..161.07 rows=25 width=1245) (actual time=35.232..38.694 rows=25 loops=1)
  ->  Index Scan using index_cars_onsale_on_brand_and_model_name on cars  (cost=0.00..1179.06 rows=183 width=1245) (actual time=35.228..38.652 rows=25 loops=1)
        Index Cond: (((brand)::text = ''BMW''::text) AND ((model_name)::text = ''318i''::text))
        Filter: has_auto_gear"
Total runtime: 38.845 ms

Un poco de historia: estoy en Postgresql 9.1.6, ejecutándose en las bases de datos dedicadas de Herokus. Mi db tiene aproximadamente 7,5 Gb de RAM, los autos de mesa contienen 3,1 M filas y aproximadamente 2,0 M de filas tienen sales_state = ''onsale''. La tabla tiene 170 columnas. El índice que usa se ve algo como esto:

CREATE INDEX index_cars_onsale_on_brand_and_model_name
  ON cars
  USING btree
  (brand COLLATE pg_catalog."default" , model_name COLLATE pg_catalog."default" )
  WHERE sales_state::text = ''onsale''::text;

¿Alguien está viendo un gran problema obvio?

EDITAR:

SELECT pg_relation_size(''cars''), pg_total_relation_size(''cars'');

pg_relation_size: 2058444800 pg_total_relation_size: 4900126720

SELECT pg_relation_size(''index_cars_onsale_on_brand_and_model_name'');

pg_relation_size: 46301184

SELECT avg(pg_column_size(cars)) FROM cars limit 5000;

Promedio: 636.9732567210792995

SIN EL LÍMITE:

EXPLAIN ANALYZE SELECT "cars".* FROM "cars" WHERE "cars"."sales_state" = ''onsale'' AND "cars"."brand" = ''BMW'' AND "cars"."model_name" = ''318i'' AND "cars"."has_auto_gear" = TRUE

Bitmap Heap Scan on cars  (cost=12.54..1156.95 rows=183 width=4) (actual time=17.067..55.198 rows=2096 loops=1)
  Recheck Cond: (((brand)::text = ''BMW''::text) AND ((model_name)::text = ''318i''::text) AND ((sales_state)::text = ''onsale''::text))
  Filter: has_auto_gear
  ->  Bitmap Index Scan on index_cars_onsale_on_brand_and_model_name  (cost=0.00..12.54 rows=585 width=0) (actual time=15.211..15.211 rows=7411 loops=1)"
        Index Cond: (((brand)::text = ''BMW''::text) AND ((model_name)::text = ''318i''::text))
Total runtime: 56.851 ms

Answer 1

Aunque no es tan útil para un plan simple como este, http://explain.depesz.com es realmente útil. Ver http://explain.depesz.com/s/t4fi . Tenga en cuenta la pestaña "estadísticas" y el menú desplegable "opciones".

Cosas que debe tener en cuenta sobre este plan:

• El recuento de filas estimado (183) es razonablemente comparable al recuento de filas reales (25). No es cientos de veces más, ni lo es 1. Está más interesado en órdenes de magnitud cuando se trata de estimaciones de recuento de filas, o problemas de "1 vs no 1". (Ni siquiera necesita precisión "lo suficientemente cerca para el trabajo gubernamental" - "lo suficientemente cerca para la contabilidad de contratación militar" servirá). La estimación de selectividad y las estadísticas parecen razonables.

• Está utilizando el índice parcial de dos columnas proporcionado ( index scan using index_cars_onsale_on_brand_and_model_name ), por lo que coincide con la condición de índice parcial. Puedes ver eso en el Filter: has_auto_gear . La condición de búsqueda de índice también se muestra.

• El rendimiento de la consulta parece razonable dado que el recuento de filas de la tabla significará que el índice es bastante grande, especialmente porque tiene más de dos columnas. Las filas coincidentes estarán dispersas, por lo que es probable que cada fila requiera una página separada también.

No veo nada malo aquí. Sin embargo, esta consulta probablemente se beneficiará en gran medida de los escaneos de solo índice de PostgreSQL 9.2.

Es posible que haya algo de hinchazón en la tabla, pero dado el índice de 2 columnas y el número de filas, el tiempo de respuesta no es del todo irrazonable, especialmente para una tabla con 170 columnas (!) Que probablemente encajen relativamente pocas tuplas en cada una página. Si puede permitirse algún tiempo de inactividad intente VACUUM FULL para reorganizar la tabla y reconstruir el índice. Esto bloqueará la mesa de forma exclusiva durante un tiempo mientras la reconstruye. Si no puede pagar el tiempo de inactividad, consulte pg_reorg y / o CREATE INDEX CONCURRENTLY y ALTER INDEX ... RENAME TO .

Puede encontrar que EXPLAIN (ANALYZE, BUFFERS, VERBOSE) más informativo a veces, ya que puede mostrar accesos de buffer, etc.

Una opción que puede hacer que esta consulta sea más rápida (aunque corre el riesgo de ralentizar otras consultas) es dividir la tabla en la brand y habilitar constraint_exclusion . Ver partitioning

Answer 2

Bueno ... lo primero que puedo decirle es que su base de datos está esperando (de las estadísticas) obtener 183 filas. En realidad, está obteniendo 25 filas. Aunque probablemente esto no sea demasiado relevante en este caso (es decir, con estas pequeñas cantidades y sin operaciones pesadas, no debe preocuparse por estimarlo incorrectamente).

Un problema mayor (imho) es que una simple búsqueda de índice para 25 filas toma 35 ms. Eso parece un poco demasiado. ¿La base de datos es lo suficientemente pesada como para tener al menos todos los índices en la memoria? Aunque no es excesivo, me parece un poco lento.

En cuanto a mirar sus explicaciones, recomendaría usar explain.depesz.com: http://explain.depesz.com/s/sA6