optimizacion - optimizar consultas postgresql

Optimización de consultas Postgres(forzando un escaneo de índice)

Question

A continuación se muestra mi consulta. Estoy intentando hacer que use un escaneo de índice, pero solo se secará.

Por cierto, la tabla metric_data tiene 130 millones de filas. La tabla de metrics tiene alrededor de 2000 filas.

columnas de la tabla metric_data :

  metric_id integer
, t timestamp
, d double precision
, PRIMARY KEY (metric_id, t)

¿Cómo puedo obtener esta consulta para usar mi índice de CLAVE PRIMARIA?

SELECT
    S.metric,
    D.t,
    D.d
FROM metric_data D
INNER JOIN metrics S
    ON S.id = D.metric_id
WHERE S.NAME = ANY (ARRAY [''cpu'', ''mem''])
  AND D.t BETWEEN ''2012-02-05 00:00:00''::TIMESTAMP
              AND ''2012-05-05 00:00:00''::TIMESTAMP;

EXPLIQUE:

Hash Join  (cost=271.30..3866384.25 rows=294973 width=25)
  Hash Cond: (d.metric_id = s.id)
  ->  Seq Scan on metric_data d  (cost=0.00..3753150.28 rows=29336784 width=20)
        Filter: ((t >= ''2012-02-05 00:00:00''::timestamp without time zone)
             AND (t <= ''2012-05-05 00:00:00''::timestamp without time zone))
  ->  Hash  (cost=270.44..270.44 rows=68 width=13)
        ->  Seq Scan on metrics s  (cost=0.00..270.44 rows=68 width=13)
              Filter: ((sym)::text = ANY (''{cpu,mem}''::text[]))

Answer 1

¿Has tratado de usar:

DONDE S.NAME = ANY (VALORES (''cpu''), (''mem'')) en lugar de ARRAY

como here

Answer 2

No puede forzar la exploración de índice en este caso porque no lo hará más rápido.

Actualmente tiene un índice en metric_data (metric_id, t) , pero el servidor no puede aprovechar este índice para su consulta, ya que debe poder discriminar solo con metric_data.t (sin metric_id ), pero no existe tal índice. El servidor puede usar subcampos en índices compuestos, pero solo desde el principio. Por ejemplo, la búsqueda por metric_id podrá emplear este índice.

Si crea otro índice en metric_data (t) , su consulta utilizará ese índice y trabajará mucho más rápido.

Además, debe asegurarse de tener un índice en las metrics (id) .

Answer 3

Para fines de prueba, puede forzar el uso del índice al "deshabilitar" los escaneos secuenciales, lo mejor en su sesión actual solamente:

SET enable_seqscan = OFF;

Detalles en el manual aquí. Cité "deshabilitar", porque en realidad no se pueden deshabilitar las exploraciones de tablas secuenciales. Pero cualquier otra opción disponible ahora es preferible para Postgres. Esto probará que se puede usar el índice de (metric_id, t) , pero no es tan efectivo como un índice en la columna principal.

Es probable que obtenga mejores resultados al cambiar el orden de las columnas en su PRIMARY KEY (y el índice utilizado para implementarlo detrás de las cortinas) a (t, metric_id) . O crea un índice adicional con columnas invertidas como esa.

• ¿Un índice compuesto también es bueno para consultas en el primer campo?

Normalmente, no es necesario forzar mejores planes de consulta mediante la intervención manual. Si la configuración de enable_seqscan = OFF conduce a un plan mucho mejor, es probable que algo no esté bien en su base de datos. Considera esta respuesta relacionada:

• Evita que PostgreSQL elija un plan de consulta incorrecto

Answer 4

Parece que te faltan las restricciones FK adecuadas:

CREATE TABLE metric_data
( metric_id integer
, t timestamp
, d double precision
, PRIMARY KEY (metric_id, t)
, FOREIGN KEY metrics_xxx_fk (metric_id) REFERENCES metrics (id)
)

y en métricas de mesa:

CREATE TABLE metrics
( id INTEGER PRIMARY KEY
...
);

También verifique si sus estadísticas son suficientes (y lo suficientemente detalladas, ya que tiene la intención de seleccionar el 0.2% de la tabla metrics_data)