Estoy tratando de almacenar algunos datos de medición en mi base de datos postgresql usando Python Django. Hasta ahora todo bien, hice un contenedor docker con django y otro con el servidor postgresql. Sin embargo, me estoy acercando a 2 millones de filas en mi tabla de medidas y las consultas empiezan a ser muy lentas, aunque no estoy seguro de por qué, no estoy haciendo consultas muy intensas.
esta consulta
SELECT ••• FROM "measurement" WHERE "measurement"."device_id" = 26 ORDER BY "measurement"."measure_timestamp" DESC LIMIT 20por ejemplo, tarda entre 3 y 5 segundos en ejecutarse, según el dispositivo que consulte.
Espero que esto funcione mucho más rápido, ya que no estoy haciendo nada sofisticado. La tabla de medidas
id INTEGER measure_timestamp TIMESTAMP WITH TIMEZONE sensor_height INTEGER device_id INTEGERcon índices en id y Measure_timestamp. El servidor no parece demasiado ocupado, aunque solo tiene 512 M de memoria, me queda mucho durante las consultas.
Configuré el servidor postgresql con shared_buffers=256MB y work_mem=128MB. La base de datos total tiene poco menos de 100 MB, por lo que debería caber fácilmente. Si ejecuto la consulta en PgAdmin, veo muchas E/S de bloque, por lo que sospecho que tiene que leer desde el disco, lo que obviamente es lento.
¿Alguien podría darme algunos consejos en la dirección correcta sobre cómo encontrar el problema?
EDITAR: Salida agregada de análisis de explicación en una consulta. Ahora agregué el índice en device_id, lo que ayudó mucho, pero esperaría tiempos de consulta aún más rápidos. https://pastebin.com/H30JSuWa
¿Tiene índices en measure_timestamp y device_id ? Si las consultas siempre toman esa forma, es posible que también le gusten los índices de varias columnas .
Por favor, mire la clave de distribución de su tabla. Es posible que los datos estén escasamente poblados, por lo que afecta el rendimiento. Seleccionar una clave de distribución adecuada es muy importante cuando tiene datos de 2 millones de registros. Para obtener más detalles, lea esto sobre por qué la clave de distribución es importante