reducir - PostgreSQL: ¿cómo estructurar e indexar los datos relacionados con el tiempo para un rendimiento de consulta óptimo?

reducir tiempo de consulta sql

Question

El problema:

Tengo datos relacionados con el tiempo en mi base de datos y estoy luchando por organizar, estructurar e indexar esos datos de manera que los usuarios puedan recuperarlos de manera eficiente; incluso las consultas simples de bases de datos tardan más que lo aceptable.

Contexto del proyecto:

Si bien esta es una pregunta de base de datos pura, algunos contextos pueden ayudar a comprender el modelo de datos:

El proyecto se centra en investigar sobre una máquina grande y compleja. No sé mucho sobre la máquina en sí, pero los rumores en el laboratorio indican que hay un condensador de flujo en algún lugar, y creo que ayer vi la cola del gato de Schrödinger colgando al costado ;-)

Medimos muchos parámetros diferentes mientras la máquina se está ejecutando utilizando sensores ubicados en toda la máquina en diferentes puntos de medición (los denominados puntos ) en ciertos intervalos durante un período de tiempo. Usamos no solo un dispositivo para medir estos parámetros, sino un rango completo de ellos; difieren en la calidad de sus datos de medición (creo que esto involucra tasas de muestreo, calidad del sensor, precio y muchos otros aspectos que no me conciernen); uno de los objetivos del proyecto en realidad es establecer una comparación entre estos dispositivos. Puede visualizar estos dispositivos de medición como un conjunto de carros de laboratorio, cada uno con una gran cantidad de cables conectados a la máquina, cada uno entregando datos de medición.

El modelo de datos:

Hay datos de medición de cada punto y cada dispositivo para cada parámetro, por ejemplo, una vez por minuto durante un período de 6 días. Mi trabajo es almacenar esos datos en una base de datos y proporcionar un acceso eficiente a ellos.

En una palabra:

• un dispositivo tiene un nombre único
• un parámetro también tiene un nombre; aunque no son únicos, también tiene una ID
• un lugar tiene una identificación

La base de datos del proyecto es más compleja, por supuesto, pero estos detalles no parecen relevantes para el problema.

• un índice de datos de medición tiene una ID, una marca de tiempo para cuando se realizó la medición y referencias al dispositivo y al lugar donde se realizó la medición
• un valor de datos de medición tiene una referencia al parámetro y al valor que realmente se midió

Inicialmente, modelé el valor de los datos de medición para tener su propia identificación como clave principal; la relación n:m entre el índice y el valor de los datos de medición era una tabla separada que solo almacenaba index:value pares de ID de index:value , pero como esa tabla consumía bastante espacio en el disco duro, lo eliminamos y cambiamos el ID de valor para que sea un número entero simple que almacena la ID del índice de datos de medición al que pertenece; la clave principal del valor de los datos de medición ahora está compuesta de esa ID y la ID del parámetro.

En una nota al margen : cuando creé el modelo de datos, seguí cuidadosamente las pautas de diseño comunes como 3NF y las restricciones de tabla apropiadas (como las claves únicas); Otra regla general fue crear un índice para cada clave externa. Sospecho que la desviación en las tablas de índice / valor de datos de medición de 3NF "estricto" podría ser una de las razones de los problemas de rendimiento que estoy viendo ahora, pero el hecho de cambiar el modelo de datos no ha resuelto el problema.

El modelo de datos en DDL:

NOTA: hay una actualización de este código más abajo.

El siguiente script crea la base de datos y todas las tablas involucradas. Tenga en cuenta que aún no hay índices explícitos. Antes de ejecutar esto, asegúrese de que ya no tenga una base de datos llamada so_test con datos valiosos ...

/c postgres
DROP DATABASE IF EXISTS so_test;
CREATE DATABASE so_test;
/c so_test
CREATE TABLE device
(
name VARCHAR(16) NOT NULL,
CONSTRAINT device_pk PRIMARY KEY (name)
);
CREATE TABLE parameter
(
-- must have ID as names are not unique
id SERIAL,
name VARCHAR(64) NOT NULL,
CONSTRAINT parameter_pk PRIMARY KEY (id)
);
CREATE TABLE spot
(
id SERIAL,
CONSTRAINT spot_pk PRIMARY KEY (id)
);
CREATE TABLE measurement_data_index
(
id SERIAL,
fk_device_name VARCHAR(16) NOT NULL,
fk_spot_id INTEGER NOT NULL,
t_stamp TIMESTAMP NOT NULL,
CONSTRAINT measurement_pk PRIMARY KEY (id),
CONSTRAINT measurement_data_index_fk_2_device FOREIGN KEY (fk_device_name)
REFERENCES device (name) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT measurement_data_index_fk_2_spot FOREIGN KEY (fk_spot_id)
REFERENCES spot (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT measurement_data_index_uk_all_cols UNIQUE (fk_device_name, fk_spot_id, t_stamp)
);
CREATE TABLE measurement_data_value
(
id INTEGER NOT NULL,
fk_parameter_id INTEGER NOT NULL,
value VARCHAR(16) NOT NULL,
CONSTRAINT measurement_data_value_pk PRIMARY KEY (id, fk_parameter_id),
CONSTRAINT measurement_data_value_fk_2_parameter FOREIGN KEY (fk_parameter_id)
REFERENCES parameter (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION
);

También he creado una secuencia de comandos para llenar la tabla con algunos datos de prueba:

CREATE OR REPLACE FUNCTION insert_data()
RETURNS VOID
LANGUAGE plpgsql
AS
$BODY$
DECLARE
t_stamp TIMESTAMP := ''2012-01-01 00:00:00'';
index_id INTEGER;
param_id INTEGER;
dev_name VARCHAR(16);
value VARCHAR(16);
BEGIN
FOR dev IN 1..5
LOOP
INSERT INTO device (name) VALUES (''dev_'' || to_char(dev, ''FM00''));
END LOOP;
FOR param IN 1..20
LOOP
INSERT INTO parameter (name) VALUES (''param_'' || to_char(param, ''FM00''));
END LOOP;
FOR spot IN 1..10
LOOP
INSERT INTO spot (id) VALUES (spot);
END LOOP;
WHILE t_stamp < ''2012-01-07 00:00:00''
LOOP
FOR dev IN 1..5
LOOP
dev_name := ''dev_'' || to_char(dev, ''FM00'');
FOR spot IN 1..10
LOOP
INSERT INTO measurement_data_index
(fk_device_name, fk_spot_id, t_stamp)
VALUES (dev_name, spot, t_stamp) RETURNING id INTO index_id;
FOR param IN 1..20
LOOP
SELECT id INTO param_id FROM parameter
WHERE name = ''param_'' || to_char(param, ''FM00'');
value := ''d'' || to_char(dev, ''FM00'')
|| ''_s'' || to_char(spot, ''FM00'')
|| ''_p'' || to_char(param, ''FM00'');
INSERT INTO measurement_data_value (id, fk_parameter_id, value)
VALUES (index_id, param_id, value);
END LOOP;
END LOOP;
END LOOP;
t_stamp := t_stamp + ''1 minute''::INTERVAL;
END LOOP;
END;
$BODY$;
SELECT insert_data();

El planificador de consultas PostgreSQL requiere estadísticas actualizadas, por lo tanto, analice todas las tablas. Puede que no se requiera aspirar, pero hazlo de todos modos:

VACUUM ANALYZE device;
VACUUM ANALYZE measurement_data_index;
VACUUM ANALYZE measurement_data_value;
VACUUM ANALYZE parameter;
VACUUM ANALYZE spot;

Una consulta de muestra:

Si ahora ejecuto una consulta muy simple para, por ejemplo, obtener todos los valores para un cierto parámetro, ya lleva unos segundos, aunque la base de datos aún no es muy grande:

EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT measurement_data_value.value
FROM measurement_data_value, parameter
WHERE measurement_data_value.fk_parameter_id = parameter.id
AND parameter.name = ''param_01'';

Resultado ejemplar en mi máquina de desarrollo (vea a continuación algunos detalles sobre mi entorno):

 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.26..178153.26 rows=432000 width=12) (actual time=0.046..2281.281 rows=432000 loops=1)
Hash Cond: (measurement_data_value.fk_parameter_id = parameter.id)
Buffers: shared hit=55035
-> Seq Scan on measurement_data_value (cost=0.00..141432.00 rows=8640000 width=16) (actual time=0.004..963.999 rows=8640000 loops=1)
Buffers: shared hit=55032
-> Hash (cost=1.25..1.25 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
Buffers: shared hit=1
-> Seq Scan on parameter (cost=0.00..1.25 rows=1 width=4) (actual time=0.004..0.008 rows=1 loops=1)
Filter: ((name)::text = ''param_01''::text)
Buffers: shared hit=1
Total runtime: 2313.615 ms
(12 rows)

No hay índices en la base de datos además de los implícitos, por lo que no es sorprendente que el planificador solo realice escaneos secuenciales. Si sigo lo que parece ser una regla empírica y agrego índices btree para cada clave externa como

CREATE INDEX measurement_data_index_idx_fk_device_name
ON measurement_data_index (fk_device_name);
CREATE INDEX measurement_data_index_idx_fk_spot_id
ON measurement_data_index (fk_spot_id);
CREATE INDEX measurement_data_value_idx_fk_parameter_id
ON measurement_data_value (fk_parameter_id);

luego haga otro análisis de vacío (solo para estar seguro) y vuelva a ejecutar la consulta, el planificador utiliza el mapa de bits y los escaneos de índice de mapa de bits, y el tiempo total de consulta mejora algo:

 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=8089.19..72842.42 rows=431999 width=12) (actual time=66.773..1336.517 rows=432000 loops=1)
Buffers: shared hit=55033 read=1184
-> Seq Scan on parameter (cost=0.00..1.25 rows=1 width=4) (actual time=0.005..0.012 rows=1 loops=1)
Filter: ((name)::text = ''param_01''::text)
Buffers: shared hit=1
-> Bitmap Heap Scan on measurement_data_value (cost=8089.19..67441.18 rows=431999 width=16) (actual time=66.762..1237.488 rows=432000 loops=1)
Recheck Cond: (fk_parameter_id = parameter.id)
Buffers: shared hit=55032 read=1184
-> Bitmap Index Scan on measurement_data_value_idx_fk_parameter_id (cost=0.00..7981.19 rows=431999 width=0) (actual time=65.222..65.222 rows=432000 loops=1)
Index Cond: (fk_parameter_id = parameter.id)
Buffers: shared read=1184
Total runtime: 1371.716 ms
(12 rows)

Sin embargo, esto es más de un segundo del tiempo de ejecución para una consulta realmente simple.

Lo que he hecho hasta ahora

• obtuve una copia de PostgreSQL 9.0 High Performance - ¡gran libro!
• hizo alguna configuración básica del servidor PostgreSQL, consulte el entorno a continuación
• creó un marco para ejecutar una serie de pruebas de rendimiento utilizando consultas reales del proyecto y para mostrar los resultados de manera gráfica; estas consultas usan dispositivos, spots, parámetros y un intervalo de tiempo como parámetros de entrada y las series de prueba ejecutadas, por ejemplo, 5, 10 dispositivos, 5, 10 puntos, 5, 10, 15, 20 parámetros y 1..7 días. El resultado básico es que son demasiado lentos, pero su plan de consulta era demasiado complejo para que lo entendiera, así que volví a la consulta realmente simple utilizada anteriormente.

He analizado la partición de la tabla de valores. Los datos están relacionados con el tiempo y la partición parece un medio apropiado para organizar ese tipo de datos; incluso los ejemplos en la documentación de PostgreSQL usan algo similar. Sin embargo, leo en el mismo artículo :

Los beneficios normalmente valdrán la pena solo cuando una mesa sea muy grande. El punto exacto en el que una tabla se beneficiará de la partición depende de la aplicación, aunque una regla general es que el tamaño de la tabla debe exceder la memoria física del servidor de la base de datos.

Toda la base de datos de prueba tiene menos de 1GB de tamaño y estoy ejecutando mis pruebas en una máquina de desarrollo con 8GB de RAM y en una máquina virtual con 1GB (ver también el entorno más abajo), por lo que la tabla está lejos de ser muy grande o incluso exceder la memoria física. De todos modos, podría implementar la partición en algún momento, pero tengo la sensación de que el enfoque no se centra en el problema del rendimiento en sí mismo.

Además, estoy considerando agrupar la tabla de valores. No me gusta el hecho de que la agrupación debe volver a realizarse cada vez que se insertan nuevos datos y que además requiere un bloqueo exclusivo de lectura / escritura, pero al mirar esta pregunta SO, parece que de todos modos tiene sus ventajas y podría ser una opción. Sin embargo, la agrupación se realiza en un índice y dado que hay hasta 4 criterios de selección en una consulta (dispositivos, spots, parámetros y tiempo), tendría que crear clusters para todos ellos, lo que a su vez me da la impresión de que Simplemente no estoy creando los índices correctos ...

Mi entorno:

• el desarrollo se lleva a cabo en una MacBook Pro (mediados de 2009) con una CPU de doble núcleo y 8 GB de RAM
• Estoy ejecutando pruebas de rendimiento de bases de datos en una máquina virtual Debian 6.0 con 1 GB de RAM, alojada en el MBP
• La versión de PostgreSQL es 9.1 ya que era la última versión cuando la instalé, la actualización a 9.2 sería posible
• Cambié shared_buffers de shared_buffers por defecto a 25% de RAM en ambas máquinas, como se recomienda en los documentos de PostgreSQL (que implicaba ampliar la configuración del kernel, como SHMALL, SHMMAX, etc.)
• Del mismo modo, he cambiado effective_cache_size de los 128 MB predeterminados al 50% de la RAM disponible
• Ejecuté la prueba de rendimiento con diferentes configuraciones de work_mem , pero no vi ninguna diferencia importante en el rendimiento

NOTA: Un aspecto que creo que es importante es que la serie de pruebas de rendimiento con consultas reales del proyecto no difieren en rendimiento entre la MacBook con 8 GB y la máquina virtual con 1 GB; es decir, si una consulta tarda 10 segundos en la MacBook, también tarda 10 segundos en la VM. Además, ejecuté las mismas pruebas de rendimiento antes y después de cambiar shared_buffers , effective_cache_size y work_mem y los cambios de configuración no mejoraron el rendimiento en más del 10%; algunos resultados incluso empeoraron, por lo que parece que cualquier diferencia se debe más a la variación de la prueba que al cambio de configuración. Estas observaciones me llevan a creer que las configuraciones de RAM y postgres.conf no son los factores limitantes aquí todavía.

Mis preguntas:

No sé si los índices diferentes o adicionales acelerarían la consulta y, si lo hicieron, cuáles crear. Al observar el tamaño de la base de datos y lo simple que es mi consulta, tengo la impresión de que hay algo fundamentalmente erróneo en mi modelo de datos o de cómo he elegido mis índices hasta el momento.

¿Alguien tiene algún consejo sobre cómo estructurar e indexar mi tiempo para mejorar el rendimiento de las consultas?

Preguntado más ampliamente, está afinando el rendimiento de las consultas

• generalmente hecho ''en una base de incidentes'', es decir, una vez que una consulta no funciona satisfactoriamente? Parece que todas mis consultas son demasiado lentas ...
• principalmente una cuestión de mirar (y comprender) los planes de consulta, luego agregar índices y medir si las cosas mejoraron, posiblemente acelerando el proceso al aplicar la propia experiencia.

¿Cómo hago para que esta base de datos funcione?

Actualización 01:

Mirando las respuestas hasta ahora, creo que no he explicado la necesidad de tablas de índices / valores de datos de medición correctamente, así que permítanme intentarlo de nuevo. El espacio de almacenamiento es el problema aquí.

NOTA:

• las cifras utilizadas aquí tienen un propósito más ilustrativo y solo para comparación, es decir, los números en sí no son relevantes, lo que importa es la diferencia porcentual en los requisitos de almacenamiento entre el uso de una sola tabla y el uso de un índice y una tabla de valores
• Los tamaños de almacenamiento del tipo de datos PostgreSQL están documentados en este capítulo
• esto no pretende ser científicamente correcto, por ejemplo, las unidades son probablemente matemáticas falsas; los números deberían sumar aunque

Asumiendo

• 1 día de mediciones
• 1 conjunto de medidas por minuto
• 10 dispositivos
• 10 parámetros
• 10 lugares

Esto se suma a

1 meas/min x 60 min/hour x 24 hour/day = 1440 meas/day

Cada medición tiene datos de cada punto y cada dispositivo para cada parámetro, por lo que

10 spots x 10 devices x 10 parameters = 1000 data sets/meas

Entonces en total

1440 meas/day x 1000 data sets/meas = 1 440 000 data sets/day

Si almacenamos todas las medidas en una sola tabla como sugiere Catcall , por ejemplo

CREATE TABLE measurement_data
(
device_name character varying(16) NOT NULL,
spot_id integer NOT NULL,
parameter_id integer NOT NULL,
t_stamp timestamp without time zone NOT NULL,
value character varying(16) NOT NULL,
-- constraints...
);

una sola fila se sumaría a

17 + 4 + 4 + 8 + 17 = 50 bytes/row

en el peor de los casos donde todos los campos varchar están completamente llenos. Esto equivale a

50 bytes/row x 1 440 000 rows/day = 72 000 000 bytes/day

o ~ 69 MB por día.

Si bien esto no suena demasiado, el requisito de espacio de almacenamiento en la base de datos real sería prohibitivo (una vez más, los números utilizados aquí son solo ilustrativos). Por lo tanto, hemos dividido los datos de medición en un índice y una tabla de valores como se explicó anteriormente en la pregunta:

CREATE TABLE measurement_data_index
(
id SERIAL,
fk_device_name VARCHAR(16) NOT NULL,
fk_spot_id INTEGER NOT NULL,
t_stamp TIMESTAMP NOT NULL,
-- constraints...
);
CREATE TABLE measurement_data_value
(
id INTEGER NOT NULL,
fk_parameter_id INTEGER NOT NULL,
value VARCHAR(16) NOT NULL,
-- constraints...
);

donde el ID de una fila de valor es igual al ID del índice al que pertenece .

Los tamaños de una fila en el índice y las tablas de valores son

index: 4 + 17 + 4 + 8 = 33 bytes value: 4 + 4 + 17 = 25 bytes

(de nuevo, peor escenario posible). La cantidad total de filas es

index: 10 devices x 10 spots x 1440 meas/day = 144 000 rows/day value: 10 parameters x 144 000 rows/day = 1 440 000 rows/day

entonces el total es

index: 33 bytes/row x 144 000 rows/day = 4 752 000 bytes/day value: 25 bytes/row x 1 440 000 rows/day = 36 000 000 bytes/day total: = 40 752 000 bytes/day

o ~ 39 MB por día, en comparación con ~ 69 MB para una sola solución de tabla.

Actualización 02 (re: respuesta de wildplassers ):

Esta pregunta se está haciendo bastante larga, por lo que estaba considerando actualizar el código en su lugar en la pregunta original anterior, pero creo que podría ser útil tener las primeras y mejores soluciones aquí para ver mejor las diferencias.

Cambios en comparación con el enfoque original (algo en orden de importancia):

• swap timestamp y parameter, es decir, mueva el campo t_stamp de la tabla measurement_data_index a measurement_data_value y mueva el campo fk_parameter_id del valor a la tabla de índice: con este cambio, todos los campos de la tabla de índice son constantes y los nuevos datos de medición se escriben únicamente en la tabla de valores. No esperaba ninguna mejora importante en el rendimiento de las consultas (estaba equivocado), pero creo que hace que el concepto del índice de datos de medición sea más claro. Si bien requiere un espacio de almacenamiento algo más pequeño (de acuerdo con algunas estimaciones aproximadas), tener una tabla de índice "estático" también podría ayudar en la implementación cuando los espacios de tabla se muevan a diferentes discos duros de acuerdo con sus requisitos de lectura / escritura.
• utilizar una clave sustituta en la tabla del dispositivo: por lo que entiendo, una clave sustituta es una clave principal que no es estrictamente necesaria desde el punto de vista del diseño de la base de datos (por ejemplo, el nombre del dispositivo ya es único, por lo que también podría servir como PK), pero podría ayudar a mejorar el rendimiento de la consulta. Lo agregué porque, de nuevo, creo que aclara el concepto si la tabla de índice solo hace referencia a los ID (en lugar de algunos nombres y algunos ID).
• reescribir insert_data() : use generate_series() lugar de bucles FOR anidados; hace que el código sea más ''ágil''.
• Como efecto secundario de estos cambios, la inserción de los datos de prueba lleva solo aproximadamente el 50% del tiempo requerido por la primera solución.
• No agregué la vista como sugirió wildplasser; no se requiere compatibilidad hacia atrás.
• Los índices adicionales para las FK en la tabla de índice parecen ser ignorados por el planificador de consultas y no tienen ningún impacto en el plan de consulta o el rendimiento.

(parece que sin esta línea, el código siguiente no se muestra correctamente como código en la página SO ...)

/c postgres
DROP DATABASE IF EXISTS so_test_03;
CREATE DATABASE so_test_03;
/c so_test_03
CREATE TABLE device
(
id SERIAL,
name VARCHAR(16) NOT NULL,
CONSTRAINT device_pk PRIMARY KEY (id),
CONSTRAINT device_uk_name UNIQUE (name)
);
CREATE TABLE parameter
(
id SERIAL,
name VARCHAR(64) NOT NULL,
CONSTRAINT parameter_pk PRIMARY KEY (id)
);
CREATE TABLE spot
(
id SERIAL,
name VARCHAR(16) NOT NULL,
CONSTRAINT spot_pk PRIMARY KEY (id)
);
CREATE TABLE measurement_data_index
(
id SERIAL,
fk_device_id INTEGER NOT NULL,
fk_parameter_id INTEGER NOT NULL,
fk_spot_id INTEGER NOT NULL,
CONSTRAINT measurement_pk PRIMARY KEY (id),
CONSTRAINT measurement_data_index_fk_2_device FOREIGN KEY (fk_device_id)
REFERENCES device (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT measurement_data_index_fk_2_parameter FOREIGN KEY (fk_parameter_id)
REFERENCES parameter (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT measurement_data_index_fk_2_spot FOREIGN KEY (fk_spot_id)
REFERENCES spot (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT measurement_data_index_uk_all_cols UNIQUE (fk_device_id, fk_parameter_id, fk_spot_id)
);
CREATE TABLE measurement_data_value
(
id INTEGER NOT NULL,
t_stamp TIMESTAMP NOT NULL,
value VARCHAR(16) NOT NULL,
-- NOTE: inverse field order compared to wildplassers version
CONSTRAINT measurement_data_value_pk PRIMARY KEY (id, t_stamp),
CONSTRAINT measurement_data_value_fk_2_index FOREIGN KEY (id)
REFERENCES measurement_data_index (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE OR REPLACE FUNCTION insert_data()
RETURNS VOID
LANGUAGE plpgsql
AS
$BODY$
BEGIN
INSERT INTO device (name)
SELECT ''dev_'' || to_char(item, ''FM00'')
FROM generate_series(1, 5) item;
INSERT INTO parameter (name)
SELECT ''param_'' || to_char(item, ''FM00'')
FROM generate_series(1, 20) item;
INSERT INTO spot (name)
SELECT ''spot_'' || to_char(item, ''FM00'')
FROM generate_series(1, 10) item;
INSERT INTO measurement_data_index (fk_device_id, fk_parameter_id, fk_spot_id)
SELECT device.id, parameter.id, spot.id
FROM device, parameter, spot;
INSERT INTO measurement_data_value(id, t_stamp, value)
SELECT index.id,
item,
''d'' || to_char(index.fk_device_id, ''FM00'') ||
''_s'' || to_char(index.fk_spot_id, ''FM00'') ||
''_p'' || to_char(index.fk_parameter_id, ''FM00'')
FROM measurement_data_index index,
generate_series(''2012-01-01 00:00:00'', ''2012-01-06 23:59:59'', interval ''1 min'') item;
END;
$BODY$;
SELECT insert_data();

En algún momento, cambiaré mis propias convenciones para usar las frases en línea PRIMARY KEY y REFERENCES lugar de las CONSTRAINT explícitas; por el momento, creo que mantener esto como estaba hace que sea más fácil comparar las dos soluciones.

No olvides actualizar las estadísticas para el planificador de consultas:

VACUUM ANALYZE device;
VACUUM ANALYZE measurement_data_index;
VACUUM ANALYZE measurement_data_value;
VACUUM ANALYZE parameter;
VACUUM ANALYZE spot;

Ejecute una consulta que debe producir el mismo resultado que la del primer enfoque:

EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT measurement_data_value.value
FROM measurement_data_index,
measurement_data_value,
parameter
WHERE measurement_data_index.fk_parameter_id = parameter.id
AND measurement_data_index.id = measurement_data_value.id
AND parameter.name = ''param_01'';

Resultado:

Nested Loop (cost=0.00..34218.28 rows=431998 width=12) (actual time=0.026..696.349 rows=432000 loops=1)
Buffers: shared hit=435332
-> Nested Loop (cost=0.00..29.75 rows=50 width=4) (actual time=0.012..0.453 rows=50 loops=1)
Join Filter: (measurement_data_index.fk_parameter_id = parameter.id)
Buffers: shared hit=7
-> Seq Scan on parameter (cost=0.00..1.25 rows=1 width=4) (actual time=0.005..0.010 rows=1 loops=1)
Filter: ((name)::text = ''param_01''::text)
Buffers: shared hit=1
-> Seq Scan on measurement_data_index (cost=0.00..16.00 rows=1000 width=8) (actual time=0.003..0.187 rows=1000 loops=1)
Buffers: shared hit=6
-> Index Scan using measurement_data_value_pk on measurement_data_value (cost=0.00..575.77 rows=8640 width=16) (actual time=0.013..12.157 rows=8640 loops=50)
Index Cond: (id = measurement_data_index.id)
Buffers: shared hit=435325
Total runtime: 726.125 ms

Esto es casi la mitad de los ~ 1.3 s del primer enfoque requerido; teniendo en cuenta que estoy cargando 432.000 filas, es un resultado con el que puedo vivir por el momento.

NOTA: El orden de campo en la tabla de valores PK es id, t_stamp ; el orden en la respuesta de t_stamp, whw_id es t_stamp, whw_id . Hice esto así porque siento que un orden de campo ''regular'' es aquel en que los campos se enumeran en la declaración de la tabla (y ''inversa'' es al revés), pero esa es solo mi propia convención que me impide obtener confuso. De cualquier manera, como señaló Erwin Brandstetter , este orden es absolutamente crítico para la mejora del rendimiento; si es el camino equivocado (y falta un índice inverso como en la solución wildplassers), el plan de consulta se ve a continuación y el rendimiento es más de 3 veces peor:

Hash Join (cost=22.14..186671.54 rows=431998 width=12) (actual time=0.460..2570.941 rows=432000 loops=1)
Hash Cond: (measurement_data_value.id = measurement_data_index.id)
Buffers: shared hit=63537
-> Seq Scan on measurement_data_value (cost=0.00..149929.58 rows=8639958 width=16) (actual time=0.004..1095.606 rows=8640000 loops=1)
Buffers: shared hit=63530
-> Hash (cost=21.51..21.51 rows=50 width=4) (actual time=0.446..0.446 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
Buffers: shared hit=7
-> Hash Join (cost=1.26..21.51 rows=50 width=4) (actual time=0.015..0.359 rows=50 loops=1)
Hash Cond: (measurement_data_index.fk_parameter_id = parameter.id)
Buffers: shared hit=7
-> Seq Scan on measurement_data_index (cost=0.00..16.00 rows=1000 width=8) (actual time=0.002..0.135 rows=1000 loops=1)
Buffers: shared hit=6
-> Hash (cost=1.25..1.25 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
Buffers: shared hit=1
-> Seq Scan on parameter (cost=0.00..1.25 rows=1 width=4) (actual time=0.004..0.007 rows=1 loops=1)
Filter: ((name)::text = ''param_01''::text)
Buffers: shared hit=1
Total runtime: 2605.277 ms

Answer 1

La idea detrás de esta "solución" es: evitar los dominios clave separados para {dispositivo, punto, parámetro}. Solo hay 1000 combinaciones posibles de estos tres. (podría verse como un caso grave de violación BCNF). Así que los combino en una tabla what_how_where, que hace referencia a los dominios independientes de árbol. El número de elementos clave en la tabla de medición (datos) se reduce de 4 a 2, y la clave sustituta se omite (ya que no se utiliza). La tabla what_how_where tiene una clave sustituta. El significado de I se puede expresar como: si hay una tupla presente en esta tabla: el parámetro "qué" se puede medir por el dispositivo "cómo" en la ubicación "donde".

-- temp schema for scratch
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp;
SET search_path=tmp;
-- tables for the three "key domain"s
CREATE TABLE device
( id SERIAL NOT NULL PRIMARY KEY
, dname VARCHAR NOT NULL -- ''name'' might be a reserve word
, CONSTRAINT device_name UNIQUE (dname)
);
CREATE TABLE parameter
( id SERIAL PRIMARY KEY -- must have ID as names are not unique
, pname VARCHAR NOT NULL
);
CREATE TABLE spot
( id SERIAL PRIMARY KEY
, sname VARCHAR NOT NULL
);
-- One table to combine the three "key domain"s
CREATE TABLE what_how_where
( id SERIAL NOT NULL PRIMARY KEY
, device_id INTEGER NOT NULL REFERENCES device(id)
, spot_id INTEGER NOT NULL REFERENCES spot(id)
, parameter_id INTEGER NOT NULL REFERENCES parameter(id)
, CONSTRAINT what_natural UNIQUE (device_id,spot_id,parameter_id)
);
CREATE TABLE measurement
( whw_id INTEGER NOT NULL REFERENCES what_how_where(id)
, t_stamp TIMESTAMP NOT NULL
, value VARCHAR(32) NOT NULL
, CONSTRAINT measurement_natural PRIMARY KEY (t_stamp,whw_id)
);
INSERT INTO device (dname)
SELECT ''dev_'' || d::text
FROM generate_series(1,10) d;
INSERT INTO parameter (pname)
SELECT ''param_'' || p::text
FROM generate_series(1,10) p;
INSERT INTO spot (sname)
SELECT ''spot_'' || s::text
FROM generate_series(1,10) s;
INSERT INTO what_how_where (device_id,spot_id,parameter_id)
SELECT d.id,s.id,p.id
FROM device d
JOIN spot s ON(1=1)
JOIN parameter p ON(1=1)
;
ANALYSE what_how_where;
INSERT INTO measurement(whw_id, t_stamp, value)
SELECT w.id
, g
, random()::text
FROM what_how_where w
JOIN generate_series(''2012-01-01''::date, ''2012-09-23''::date, ''1 day''::interval) g
ON (1=1)
;
CREATE UNIQUE INDEX measurement_natural_reversed ON measurement(whw_id,t_stamp);
ANALYSE measurement;
-- A view to *more or less* emulate the original behaviour
DROP VIEW measurement_data ;
CREATE VIEW measurement_data AS (
SELECT d.dname AS dname
, p.pname AS pname
, w.spot_id AS spot_id
, w.parameter_id AS parameter_id
, m.t_stamp AS t_stamp
, m.value AS value
FROM measurement m
JOIN what_how_where w ON m.whw_id = w.id
JOIN device d ON w.device_id = d.id
JOIN parameter p ON w.parameter_id = p.id
);
EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT md.value
FROM measurement_data md
WHERE md.pname = ''param_8''
AND md.t_stamp >= ''2012-07-01''
AND md.t_stamp < ''2012-08-01''
;

ACTUALIZACIÓN: hay un problema práctico, que solo puede resolverse mediante algún tipo de agrupamiento:

• dado un tamaño de fila estimado de 50 bytes
• y se busca una especificidad de la consulta del 5% solamente (1/20) de los parámetros
• lo que significa que alrededor de 4 tuplas "deseadas" viven en una página de disco del sistema operativo (+76 tuplas no deseadas)

Sin clustering, esto significa que todas las páginas deben ser jaladas + inspeccionadas. Los índices no ayudan aquí (solo ayudan si pueden evitar que se introduzcan páginas, este podría ser el caso para una búsqueda (rango) en la (s) primera (s) columna (s) de clave) Los índices pueden ayudar un poco a escanear la memoria páginas después de que estos hayan sido captados

Como consecuencia, esto significa que (una vez que la huella de su consulta es mayor que el espacio de búfer disponible) su consulta realmente mide la velocidad de E / S de su máquina.

Answer 2

No veo cómo se relaciona un valor medido particular, con una combinación particular de dispositivo, lugar y tiempo. Me estoy perdiendo algo obvio?

Veamos una manera diferente.

CREATE TABLE measurement_data
(
device_name character varying(16) NOT NULL,
spot_id integer NOT NULL,
parameter_id integer NOT NULL,
t_stamp timestamp without time zone NOT NULL,
value character varying(16) NOT NULL,
CONSTRAINT measurement_data_pk PRIMARY KEY (device_name , spot_id , t_stamp , parameter_id ),
CONSTRAINT measurement_data_fk_device FOREIGN KEY (device_name)
REFERENCES device (name) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT measurement_data_fk_parameter FOREIGN KEY (parameter_id)
REFERENCES parameter (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT measurement_data_fk_spot FOREIGN KEY (spot_id)
REFERENCES spot (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION
);

(Una mejor nombre para esta tabla es "mediciones". Cada tabla contiene datos).

Yo esperaría un rendimiento mucho mejor en este tipo de mesa. Pero también me espero cualquier consulta que devuelve muchas, muchas filas para luchar con el rendimiento. (A menos que el hardware y la red coincide con la tarea).

Answer 3

Yo, básicamente, revisé toda su configuración. Sometido a pruebas en PostgreSQL 9.1.5.

esquema de DB

• Creo que el diseño de la mesa tiene un fallo lógico principal (como también se ha señalado @Catcall). He cambiado la forma en que sospecho que debe ser:
  Su última mesa measurement_data_value(que Retitulé a measure_val) se supone que guardar un valor por parameter(ahora: param) para cada fila en la measurement_data_index(ahora: measure). Vea abajo.

• A pesar de que "un dispositivo tiene un nombre único" utilizar una clave primaria sustituta número entero de todos modos. Las cadenas de texto son inherentemente más voluminoso y más lento para ser utilizados como claves foráneas en grandes mesas. También están sujetos a colación , lo que puede ralentizar significativamente las consultas.

  Bajo esta cuestión conexa , se encontró que la unión y la clasificación en un tamaño mediano textcolumna fue el mayor desaceleración. Si usted insiste en el uso de una cadena de texto como clave primaria, leer sobre el apoyo de colación en PostgreSQL 9.1 o posterior.

• No caiga en el anti-patrón de utilizar idcomo nombre para una clave principal. Cuando se une a un par de mesas (como usted tendrá que hacer un montón!) Que terminan con nombre varias columnas id- ¡qué lío! (Por desgracia, algunos lo utilizan ORM).

  En su lugar, el nombre de una columna de clave primaria sustituta después de la mesa de alguna manera para que sea significativa por sí misma. A continuación, puede tener las claves externas referencia que tienen el mismo nombre (que es una buena, ya que contienen los mismos datos).

  CREATE TABLE spot
  ( spot_id SERIAL PRIMARY KEY);

• No utilice identificadores super-largos. Son difíciles de escribir y difícil de leer. La regla de oro: siempre una condición necesaria para que quede claro, lo más corto posible.

• No utilice varchar(n)si usted no tiene una razón de peso. Sólo tiene que usar varchar, o más sencillo: bastatext .

Todo esto y mucho más entró en mi propuesta para un mejor esquema de db:

CREATE TABLE device
( device_id serial PRIMARY KEY
,device text NOT NULL
);
CREATE TABLE param
( param_id serial PRIMARY KEY
,param text NOT NULL
);
CREATE INDEX param_param_idx ON param (param); -- you are looking up by name!
CREATE TABLE spot
( spot_id serial PRIMARY KEY);
CREATE TABLE measure
( measure_id serial PRIMARY KEY
,device_id int NOT NULL REFERENCES device (device_id) ON UPDATE CASCADE
,spot_id int NOT NULL REFERENCES spot (spot_id) ON UPDATE CASCADE
,t_stamp timestamp NOT NULL
,CONSTRAINT measure_uni UNIQUE (device_id, spot_id, t_stamp)
);
CREATE TABLE measure_val -- better name?
( measure_id int NOT NULL REFERENCES measure (measure_id)
ON UPDATE CASCADE ON DELETE CASCADE -- guessing it fits
,param_id int NOT NULL REFERENCES param (param_id)
ON UPDATE CASCADE ON DELETE CASCADE -- guessing it fits
,value text NOT NULL
,CONSTRAINT measure_val_pk PRIMARY KEY (measure_id, param_id)
);
CREATE INDEX measure_val_param_id_idx ON measure_val (param_id); -- !crucial!

Me cambió el nombre del voluminosos measurement_data_valuea measure_val, porque eso es lo que hay en la mesa: los valores de los parámetros para las mediciones. Ahora, el pk varias columnas tiene sentido, también.

Pero he añadido un índice separado enparam_id . La forma en que lo tenía, la columna param_idfue la segunda columna en un índice de varias columnas, lo que conduce a resultados pobres para param_id. Lea todos los detalles morbosos acerca de que bajo esta pregunta relacionada en dba.SE .

Después de implementar esto por sí solo, la consulta debe ser más rápido. Pero hay mucho más que puede hacer.

Datos de prueba

Esto rellena los datos mucho más rápido . El punto es que yo uso los comandos DML basadas en conjuntos, la ejecución de los medios de inserciones en lugar de bucles que se ejecutan insertos individuales, lo que lleva siempre. Hace una gran diferencia por la cantidad considerable de datos de prueba que desea insertar. También es mucho más corto y más simple.

Para hacerlo aún más eficiente, utilizo una técnica de modificación de CTE (nuevo en PostgreSQL 9.1) que al instante vuelve a utilizar la enorme cantidad de filas en el último paso.

CREATE OR REPLACE FUNCTION insert_data()
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
INSERT INTO device (device)
SELECT ''dev_'' || to_char(g, ''FM00'')
FROM generate_series(1,5) g;
INSERT INTO param (param)
SELECT ''param_'' || to_char(g, ''FM00'')
FROM generate_series(1,20) g;
INSERT INTO spot (spot_id)
SELECT nextval(''spot_spot_id_seq''::regclass)
FROM generate_series(1,10) g; -- to set sequence, too
WITH x AS (
INSERT INTO measure (device_id, spot_id, t_stamp)
SELECT d.device_id, s.spot_id, g
FROM device d
CROSS JOIN spot s
CROSS JOIN generate_series(''2012-01-06 23:00:00'' -- smaller set
,''2012-01-07 00:00:00'' -- for quick tests
,interval ''1 min'') g
RETURNING *
)
INSERT INTO measure_val (measure_id, param_id, value)
SELECT x.measure_id
,p.param_id
,x.device_id || ''_'' || x.spot_id || ''_'' || p.param
FROM x
CROSS JOIN param p;
END
$BODY$;

Llamada:

SELECT insert_data();

Consulta

• Utilice explícita JOINsintaxis y la tabla de alias para hacer sus consultas más fácil de leer y depurar:

SELECT v.value
FROM param p
JOIN measure_val v USING (param_id)
WHERE p.param = ''param_01'';

La USINGcláusula es sólo para simplificar la sintaxis, pero no superior a ONotra cosa.

Esto debería ser mucho más rápido ahora por dos razones:

• Índice param_param_idxde param.param.
• Índice measure_val_param_id_idxde measure_val.param_id, como se explica en detalle aquí .

Edición después de retroalimentación

Mi mayor supervisión era que ya había añadido el índice crucial en la forma de measurement_data_value_idx_fk_parameter_idmás abajo en su pregunta. (Culpo sus nombres crípticos: p) En una inspección más cercana, tiene más de 10 millones (7 * 24 * 60 * 5 * 10 * 20) filas de la configuración de la prueba y su consulta recupera> 500K. Yo sólo probado con un subconjunto más pequeño.

También, como se recupera el 5% de toda la tabla, los índices van solamente hasta cierto punto. Estaba a optimista, una cantidad de datos, está obligado a llevar algún tiempo. ¿Es un requisito realista que se consulta 500k filas? Yo supongo que se agregan en su aplicación a la vida real?

otras opciones

• Particionamiento .

• Más RAM y configuraciones que hacen uso de ella.

  Una máquina virtual Debian 6.0 con 1 GB de RAM

  es muy por debajo de lo que necesita.

• Índices parciales , especialmente en relación con exploraciones de índices de sólo de PostgreSQL 9.2.

• Materializado vistas de los datos agregados. Obviamente, no se va a mostrar 500K filas, pero algún tipo de agregación. Se puede calcular que una vez y guardar los resultados en una vista materializada, desde donde se puede recuperar datos mucho más rápido.

• Si las consultas son en su mayoría por el parámetro (como el ejemplo), usted podría utilizar CLUSTERpara volver a escribir la tabla física de acuerdo a un índice:

  CLUSTER measure_val USING measure_val_param_id_idx

  De esta manera todas las filas de un parámetro se almacenan en la serie. Significa un menor número de bloques de leer y más fácil de caché. Debe realizar la consulta a la mano mucho más rápido. O INSERTlas filas en orden favorable para empezar, en el mismo sentido.
  Particionamiento se mezclaría bien con CLUSTER, ya que no tendría que volver a escribir el conjunto (enorme) mesa cada vez. A medida que sus datos son, obviamente, sólo inserta y no actualizada, una partición se quedaría "con el fin" después CLUSTER.

• En general, PostgreSQL 9.2 debe ser grande para usted como sus mejoras se centran en el rendimiento con grandes volúmenes de datos .

Answer 4

Al parecer, de los números que se están viendo afectadas por el momento por encima. Esto se puede comprobar mediante el uso de pg_test_timing o añadir timing offa sus parámetros de explicar (ambos se introducen en PostgreSQL versión 9.2). Aproximadamente puedo replicar sus resultados girando entorno mi clocksource a HPET en lugar de TSC.

Con HPET:

 Nested Loop (cost=8097.73..72850.98 rows=432000 width=12) (actual time=29.188..905.765 rows=432000 loops=1)
Buffers: shared hit=56216
-> Seq Scan on parameter (cost=0.00..1.25 rows=1 width=4) (actual time=0.004..0.008 rows=1 loops=1)
Filter: ((name)::text = ''param_01''::text)
Rows Removed by Filter: 19
Buffers: shared hit=1
-> Bitmap Heap Scan on measurement_data_value (cost=8097.73..68529.73 rows=432000 width=16) (actual time=29.180..357.848 rows=432000 loops=1)
Recheck Cond: (fk_parameter_id = parameter.id)
Buffers: shared hit=56215
-> Bitmap Index Scan on measurement_data_value_idx_fk_parameter_id (cost=0.00..7989.73 rows=432000 width=0) (actual time=21.710..21.710 rows=432000 loops=1)
Index Cond: (fk_parameter_id = parameter.id)
Buffers: shared hit=1183
Total runtime: 1170.409 ms

Con HPET y el tiempo de:

 Nested Loop (cost=8097.73..72850.98 rows=432000 width=12) (actual rows=432000 loops=1)
Buffers: shared hit=56216
-> Seq Scan on parameter (cost=0.00..1.25 rows=1 width=4) (actual rows=1 loops=1)
Filter: ((name)::text = ''param_01''::text)
Rows Removed by Filter: 19
Buffers: shared hit=1
-> Bitmap Heap Scan on measurement_data_value (cost=8097.73..68529.73 rows=432000 width=16) (actual rows=432000 loops=1)
Recheck Cond: (fk_parameter_id = parameter.id)
Buffers: shared hit=56215
-> Bitmap Index Scan on measurement_data_value_idx_fk_parameter_id (cost=0.00..7989.73 rows=432000 width=0) (actual rows=432000 loops=1)
Index Cond: (fk_parameter_id = parameter.id)
Buffers: shared hit=1183
Total runtime: 156.537 ms

Con TSC:

 Nested Loop (cost=8097.73..72850.98 rows=432000 width=12) (actual time=29.090..156.233 rows=432000 loops=1)
Buffers: shared hit=56216
-> Seq Scan on parameter (cost=0.00..1.25 rows=1 width=4) (actual time=0.004..0.008 rows=1 loops=1)
Filter: ((name)::text = ''param_01''::text)
Rows Removed by Filter: 19
Buffers: shared hit=1
-> Bitmap Heap Scan on measurement_data_value (cost=8097.73..68529.73 rows=432000 width=16) (actual time=29.083..114.908 rows=432000 loops=1)
Recheck Cond: (fk_parameter_id = parameter.id)
Buffers: shared hit=56215
-> Bitmap Index Scan on measurement_data_value_idx_fk_parameter_id (cost=0.00..7989.73 rows=432000 width=0) (actual time=21.667..21.667 rows=432000 loops=1)
Index Cond: (fk_parameter_id = parameter.id)
Buffers: shared hit=1183
Total runtime: 168.869 ms

Por lo que su lentitud parece ser causada sobre todo por la sobrecarga de instrumentación. Sin embargo, la selección de grandes cantidades de filas no será extremadamente rápido en PostgreSQL. Si lo que necesita hacer cálculos numéricos en amplios sectores de datos que podría ser una buena idea para estructurar los datos para que pueda buscarlo en trozos grandes. (Por ejemplo, si necesita siempre proceso por lo menos un día por valor de los datos, todas las mediciones agregadas por un día en una matriz)

En general, usted tiene que tener una idea de lo que su carga de trabajo va a ser hacer sintonía. ¿Qué es una victoria en un caso podría ser una gran pérdida de algún otro caso. Te recomiendo que compruebes pg_stat_statements de averiguar dónde están sus cuellos de botella.