0
472
ViewsEl índice de PostgreSQL reduce el tamaño de los datos pero hace que la consulta sea más lenta
Tengo una tabla PostgreSQL con 7,9 GB de datos JSON. Mi objetivo es realizar agregaciones en toda la tabla diariamente, los resultados de la agregación se utilizarán más tarde para informes analíticos en Google Data Studio.
Una de las consultas que estoy tratando de ejecutar tiene el siguiente aspecto:
explain analyze select tender->>'procurementMethodType' as procurement_method, tender->>'status' as tender_status, sum(cast(tender->'value'->>'amount' as decimal)) as total_expected_value from tenders group by 1,2El plan de consulta y el tiempo de ejecución son los siguientes:
El problema es que la base de datos tiene que escanear todos los 7,9 GB de datos, aunque la consulta utiliza solo 3 valores de campo de aproximadamente 100. Así que decidí crear el siguiente índice:
create index on tenders((tender->>'procurementMethodType'), (tender->>'status'), (cast(tender->'value'->>'amount' as decimal)))El tamaño del índice es de 44 MB, que es mucho más pequeño que el tamaño de toda la tabla, por lo que espero que la consulta sea mucho más rápida. Sin embargo, cuando ejecuto la misma consulta con el índice creado, obtengo el siguiente resultado:
¡La consulta con índice es más lenta! como puede ser esto posible?
EDITAR: la tabla en sí contiene dos columnas: la columna ID y la columna de datos jsonb:
create table tenders ( id uuid primary key, tender jsonb )1 answers
Answer question0
El código que realiza un escaneo de solo índice es algo deficiente en este caso. Cree que necesita que "oferta" esté disponible en el índice para satisfacer la demanda de cast(tender->'value'->>'amount' as decimal) . No se da cuenta de que tener cast(tender->'value'->>'amount' as decimal) en el índice obvia la necesidad de "oferta" en sí. Por lo tanto, está realizando un escaneo de índice regular, en el que tiene que saltar del índice a la tabla por cada fila que devolverá, para extraer "oferta" y luego calcular cast(tender->'value'->>'amount' as decimal) . Esto significa que está saltando por toda la tabla haciendo io aleatorio, que es mucho más lento que simplemente leer la tabla secuencialmente y luego hacer una ordenación.
Puede probar un índice en ((tender->>'procurementMethodType'), (tender->>'status'), tender) . Este índice sería enorme (tan grande como la tabla) si pudiera construirse, pero eliminaría la necesidad de una ordenación.
Pero su consulta actual finaliza en 30 segundos. Para una consulta que solo se ejecuta una vez al día, ¿realmente necesita ser más rápida que esto?