PostgreSQL Antipatterns: hit dictionary on heavy JOIN

Continuamos una serie de artículos dedicados al estudio de formas poco conocidas para mejorar el rendimiento de consultas PostgreSQL "aparentemente simples":

• registro raro llegará a la mitad de JOIN
• sísifo unirse a matrices
• UNIÓN dañina y OR
• CTE ÚNETE A CTE

No pienses que no me gusta unirme tanto ... :)

Pero a menudo sin ella, la solicitud es significativamente más productiva que con él. Por lo tanto, hoy intentaremos deshacernos por completo de JOIN, que requiere muchos recursos , con la ayuda de un diccionario.

Comenzando con PostgreSQL 12, algunas de las situaciones descritas a continuación pueden reproducirse de manera un poco diferente debido a la no materialización de CTE por defecto . Este comportamiento puede revertirse utilizando la tecla MATERIALIZED .

Muchos "hechos" en un vocabulario limitado

Tomemos una aplicación muy real: debe enumerar los mensajes entrantes o las tareas activas con los remitentes:

 25.01 |  .. |    . 22.01 |  .. |    :   JOIN. 20.01 |  .. |   . 18.01 |  .. |    : JOIN    . 16.01 |  .. |   . 

En el mundo abstracto, los autores de las tareas deben distribuirse de manera uniforme entre todos los empleados de nuestra organización, pero en realidad las tareas provienen, por regla general, de un número bastante limitado de personas : "de los superiores" en la jerarquía o "de los aliados" de los departamentos vecinos (analistas, diseñadores comercialización ...).

Supongamos que en nuestra organización de 1000 personas, solo 20 autores (generalmente incluso menos) establecen tareas para cada artista específico y utilizan este conocimiento del tema para acelerar la solicitud "tradicional".


Mostramos las últimas 100 tareas para un artista específico:

 SELECT task.* , person.name FROM task LEFT JOIN person ON person.id = task.author_id WHERE owner_id = 777 ORDER BY task_date DESC LIMIT 100; 

[mira explicar.tensor.ru]

Resulta que 1/3 de todo el tiempo y 3/4 lecturas de las páginas de datos se realizaron solo para buscar al autor 100 veces, para cada tarea mostrada. Pero sabemos que entre este centenar solo hay 20 diferentes : ¿es posible utilizar este conocimiento?

diccionario hstore

Usamos el tipo hstore para generar un "diccionario" clave-valor:

 CREATE EXTENSION hstore 

Es suficiente para nosotros poner la identificación del autor y su nombre en el diccionario, para que luego podamos extraer usando esta clave:

 --    WITH T AS ( SELECT * FROM task WHERE owner_id = 777 ORDER BY task_date DESC LIMIT 100 ) --      , dict AS ( SELECT hstore( -- hstore(keys::text[], values::text[]) array_agg(id)::text[] , array_agg(name)::text[] ) FROM person WHERE id = ANY(ARRAY( SELECT DISTINCT author_id FROM T )) ) --     SELECT * , (TABLE dict) -> author_id::text -- hstore -> key FROM T; 

[mira explicar.tensor.ru]

¡Tomó 2 veces menos tiempo obtener información sobre personas y 7 veces menos datos leídos ! Además del "engaño", estos resultados nos ayudaron a lograr la extracción masiva de registros de la tabla en una sola pasada usando = ANY(ARRAY(...)) .

Entradas de tabla: serialización y deserialización

Pero, ¿qué sucede si necesitamos guardar en el diccionario no un campo de texto, sino un registro completo? En este caso, la capacidad de PostgreSQL para trabajar escribiendo una tabla como un solo valor nos ayudará a:

 ... , dict AS ( SELECT hstore( array_agg(id)::text[] , array_agg(p)::text[] --  #1 ) FROM person p WHERE ... ) SELECT * , (((TABLE dict) -> author_id::text)::person).* --  #2 FROM T; 

Veamos lo que sucedió aquí:

1. Tomamos p como un alias para el registro completo de la tabla de personas y reunimos una matriz de ellos.
2. Este conjunto de entradas se reformuló en un conjunto de cadenas de texto (persona [] :: texto []) para colocarlo en el diccionario hstore como un conjunto de valores.
3. Al recibir el registro vinculado, lo sacamos del diccionario por clave como una cadena de texto.
4. Necesitamos convertir el texto en el valor del tipo de la tabla de personas (para cada tabla, el tipo del mismo nombre se crea automáticamente).
5. "Desplegó" un registro escrito en columnas usando (...).* .

diccionario json

Pero tal truco, como hemos aplicado anteriormente, no funcionará si no hay un tipo de tabla correspondiente para hacer un "desabroche". Aparecerá exactamente la misma situación, y si como fuente de datos para la serialización tratamos de usar la fila CTE, y no la tabla "real" .

En este caso, las funciones para trabajar con json nos ayudarán:

 ... , p AS ( --   CTE SELECT * FROM person WHERE ... ) , dict AS ( SELECT json_object( --    json array_agg(id)::text[] , array_agg(row_to_json(p))::text[] --   json    ) FROM p ) SELECT * FROM T , LATERAL( SELECT * FROM json_to_record( ((TABLE dict) ->> author_id::text)::json --     json ) AS j(name text, birth_date date) --     ) j; 

Cabe señalar que al describir la estructura de destino, no podemos enumerar todos los campos de la cadena fuente, sino solo aquellos que realmente necesitamos. Si tenemos una tabla "nativa", entonces es mejor usar la función json_populate_record .

Todavía tenemos acceso al diccionario una vez, pero los costos de la serialización json- [de] son ​​bastante altos , por lo que es razonable utilizar este método solo en algunos casos cuando el CTE Scan "honesto" se muestra peor.

Prueba de rendimiento

Entonces, tenemos dos formas de serializar datos en un diccionario: hstore / json_object . Además, las matrices de claves y valores también se pueden generar de dos maneras, con conversión interna o externa a texto: array_agg (i :: text) / array_agg (i) :: text [] .

Verifiquemos la efectividad de los diferentes tipos de serialización utilizando un ejemplo puramente sintético: serializamos un número diferente de claves :

 WITH dict AS ( SELECT hstore( array_agg(i::text) , array_agg(i::text) ) FROM generate_series(1, ...) i ) TABLE dict; 

En PostgreSQL 11, hasta un tamaño de diccionario de 2 ^ 12 teclas, la serialización en json lleva menos tiempo . La combinación de json_object y la conversión de tipo "interno" de array_agg(i::text) es la más eficiente.

Ahora intentemos leer el valor de cada clave 8 veces, porque si no accede al diccionario, ¿por qué es necesario?




Y ... ya en aproximadamente 2 ^ 6 teclas, la lectura del diccionario json comienza a perder a la lectura de hstore varias veces, para jsonb sucede lo mismo en 2 ^ 9.

Conclusiones finales

• si necesita hacer una UNIÓN con registros repetidos repetidamente , es mejor usar la "coincidencia de tabla"
• si se espera que su diccionario sea pequeño y leerá un poco , puede usar json [b]
• en todos los demás casos, hstore + array_agg (i :: text) será más eficiente