ProHoster > Blog > administración > Antipatrones de PostgreSQL: usemos el ÚNETE pesado con un diccionario

Antipatrones de PostgreSQL: usemos el ÚNETE pesado con un diccionario

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

No creas que no me gusta tanto UNIRSE... :)

Pero a menudo sin él, la solicitud resulta mucho más productiva que con él. Así que hoy intentaremos deshacerse de JOIN que consume muchos recursos - usando un diccionario.

Antipatrones de PostgreSQL: usemos el ÚNETE pesado con un diccionario

A partir de PostgreSQL 12, algunas de las situaciones que se describen a continuación pueden reproducirse de forma ligeramente diferente debido a CTE de no materialización por defecto. Este comportamiento se puede revertir especificando la clave MATERIALIZED.

Muchos "hechos" en un vocabulario limitado

Tomemos una tarea de aplicación muy real: necesitamos mostrar una lista mensajes entrantes o tareas activas con remitentes:

25.01 | Иванов И.И. | Подготовить описание нового алгоритма.
22.01 | Иванов И.И. | Написать статью на Хабр: жизнь без JOIN.
20.01 | Петров П.П. | Помочь оптимизировать запрос.
18.01 | Иванов И.И. | Написать статью на Хабр: JOIN с учетом распределения данных.
16.01 | Петров П.П. | Помочь оптимизировать запрос.

En el mundo abstracto, los autores de las tareas deberían distribuirse uniformemente 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. - “desde la dirección” hasta la jerarquía o “desde los subcontratistas” de los departamentos vecinos (analistas, diseñadores, marketing, ...).

Aceptemos que en nuestra organización de 1000 personas, solo 20 autores (normalmente incluso menos) establecen tareas para cada intérprete específico y Usemos este conocimiento de la materia.para acelerar la consulta "tradicional".

Generador de guiones

-- сотрудники
CREATE TABLE person AS
SELECT
  id
, repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name"
, '2000-01-01'::date - (random() * 1e4)::integer birth_date
FROM
  generate_series(1, 1000) id;

ALTER TABLE person ADD PRIMARY KEY(id);

-- задачи с указанным распределением
CREATE TABLE task AS
WITH aid AS (
  SELECT
    id
  , array_agg((random() * 999)::integer + 1) aids
  FROM
    generate_series(1, 1000) id
  , generate_series(1, 20)
  GROUP BY
    1
)
SELECT
  *
FROM
  (
    SELECT
      id
    , '2020-01-01'::date - (random() * 1e3)::integer task_date
    , (random() * 999)::integer + 1 owner_id
    FROM
      generate_series(1, 100000) id
  ) T
, LATERAL(
    SELECT
      aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
    FROM
      aid
    WHERE
      id = T.owner_id
    LIMIT 1
  ) a;

ALTER TABLE task ADD PRIMARY KEY(id);
CREATE INDEX ON task(owner_id, task_date);
CREATE INDEX ON task(author_id);

Mostremos las últimas 100 tareas para un ejecutor 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;

Antipatrones de PostgreSQL: usemos el ÚNETE pesado con un diccionario
[mira explicar.tensor.ru]

Resulta que 1/3 del tiempo total y 3/4 de lecturas Se crearon páginas de datos solo para buscar al autor 100 veces, para cada tarea de salida. Pero sabemos que entre estos cientos solo 20 diferentes - ¿Es posible utilizar este conocimiento?

diccionario-hstore

usemos tipo de tienda para generar un valor-clave de "diccionario":

CREATE EXTENSION hstore

Sólo necesitamos poner el ID del autor y su nombre en el diccionario para luego poder 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;

Antipatrones de PostgreSQL: usemos el ÚNETE pesado con un diccionario
[mira explicar.tensor.ru]

Gastado en obtener información sobre personas. 2 veces menos tiempo y 7 veces menos datos leídos! Además del “vocabulario”, lo que también nos ayudó a lograr estos resultados fue recuperación masiva de registros de la mesa en una sola pasada usando = ANY(ARRAY(...)).

Entradas de tabla: serialización y deserialización

Pero ¿qué pasa si necesitamos guardar no sólo un campo de texto, sino una entrada completa en el diccionario? En este caso, la capacidad de PostgreSQL nos ayudará tratar una entrada de tabla como un valor único:

...
, 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 estaba pasando aquí:

  1. tomamos p como alias para la entrada completa de la tabla de personas y reunió una gran variedad de ellos.
  2. este Se reformuló el conjunto de grabaciones. a una matriz de cadenas de texto (persona[]::text[]) para colocarlo en el diccionario hstore como una matriz de valores.
  3. Cuando recibimos un registro relacionado, extraído del diccionario por clave como una cadena de texto.
  4. Necesitamos texto convertirse en un valor de tipo tabla persona (para cada tabla se crea automáticamente un tipo con el mismo nombre).
  5. “Expandir” el registro escrito en columnas usando (...).*.

diccionario json

Pero un truco como el que aplicamos anteriormente no funcionará si no existe un tipo de tabla correspondiente para realizar el "casting". Surgirá exactamente la misma situación, y si intentamos utilizar una fila CTE, no una tabla "real".

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

...
, 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 de origen, sino solo aquellos que realmente necesitamos. Si tenemos una tabla "nativa", entonces es mejor usar la función json_populate_record.

Todavía accedemos al diccionario una vez, pero Los costos de serialización json-[de] son ​​bastante altosPor lo tanto, es razonable utilizar este método sólo en algunos casos, cuando la exploración CTE "honesta" resulta peor.

Prueba de rendimiento

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

Comprobemos la eficacia de los diferentes tipos de serialización utilizando un ejemplo puramente sintético: serializar diferentes números de claves:

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

Guión de evaluación: serialización

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  hstore(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              TABLE dict
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  ,   LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

Antipatrones de PostgreSQL: usemos el ÚNETE pesado con un diccionario

En PostgreSQL 11, hasta aproximadamente un tamaño de diccionario de 2^12 claves la serialización a json lleva menos tiempo. En este caso, lo más efectivo es la combinación de json_object y conversión de tipo "interno". array_agg(i::text).

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

Guión de evaluación: lectura de un diccionario

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  json_object(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              SELECT
                (TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text
              FROM
                generate_series(1, $$ || (1 << (v + 3)) || $$) i
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  , LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

Antipatrones de PostgreSQL: usemos el ÚNETE pesado con un diccionario

Y... ya aproximadamente con 2 ^ 6 claves, la lectura de un diccionario json comienza a perderse varias veces leyendo desde hstore, para jsonb sucede lo mismo en 2^9.

Conclusiones finales:

  • si necesitas hacerlo ÚNETE con múltiples registros repetidos — es mejor usar el “diccionario” de la tabla
  • si se espera su diccionario pequeño y no leerás mucho de él - puedes usar json[b]
  • en todos los demás casos hstore + array_agg(i::texto) será más efectivo

Fuente: habr.com

Añadir un comentario