Continuamos a série de artigos dedicados ao estudo de maneiras pouco conhecidas de melhorar o desempenho de consultas PostgreSQL “aparentemente simples”:
Não pense que eu não gosto tanto do JOIN... :)
Mas muitas vezes sem ele, a solicitação acaba sendo significativamente mais produtiva do que com ele. Então hoje vamos tentar livre-se do JOIN que consome muitos recursos - usando um dicionário.
A partir do PostgreSQL 12, algumas das situações descritas abaixo podem ser reproduzidas de forma ligeiramente diferente devido a . Este comportamento pode ser revertido especificando a chave
MATERIALIZED.
Muitos “fatos” em um vocabulário limitado
Vejamos uma tarefa de aplicação muito real - precisamos exibir uma lista ou tarefas ativas com remetentes:
25.01 | Иванов И.И. | Подготовить описание нового алгоритма.
22.01 | Иванов И.И. | Написать статью на Хабр: жизнь без JOIN.
20.01 | Петров П.П. | Помочь оптимизировать запрос.
18.01 | Иванов И.И. | Написать статью на Хабр: JOIN с учетом распределения данных.
16.01 | Петров П.П. | Помочь оптимизировать запрос.
No mundo abstrato, os autores das tarefas deveriam ser distribuídos uniformemente entre todos os funcionários da nossa organização, mas na realidade as tarefas vêm, via de regra, de um número bastante limitado de pessoas - “da gestão” acima na hierarquia ou “de subcontratados” de departamentos vizinhos (analistas, designers, marketing, ...).
Vamos aceitar que em nossa organização de 1000 pessoas, apenas 20 autores (geralmente até menos) definem tarefas para cada executor específico e para acelerar a consulta "tradicional".
Gerador de script
-- сотрудники
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);
Vamos mostrar as últimas 100 tarefas de um executor 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;
Acontece que 1/3 do tempo total e 3/4 das leituras páginas de dados foram feitas apenas para pesquisar o autor 100 vezes - para cada tarefa de saída. Mas sabemos que entre estas centenas apenas 20 diferentes - É possível usar esse conhecimento?
dicionário hstore
Vamos usar para gerar um valor-chave de “dicionário”:
CREATE EXTENSION hstoreBasta colocar o ID do autor e seu nome no dicionário para depois extrair usando esta chave:
-- формируем целевую выборку
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;
Gasto na obtenção de informações sobre pessoas 2 vezes menos tempo e 7 vezes menos leitura de dados! Além do “vocabulário”, o que também nos ajudou a alcançar esses resultados foi recuperação de registros em massa da mesa em uma única passagem usando = ANY(ARRAY(...)).
Entradas de tabela: serialização e desserialização
Mas e se precisarmos salvar não apenas um campo de texto, mas uma entrada inteira no dicionário? Neste caso, a capacidade do PostgreSQL nos ajudará tratar uma entrada de tabela como um 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;Vejamos o que estava acontecendo aqui:
- Nós pegamos p como um alias para a entrada completa da tabela de pessoas e reuniu uma série deles.
- esta o conjunto de gravações foi reformulado a uma matriz de strings de texto (person[]::text[]) para colocá-la no dicionário hstore como uma matriz de valores.
- Quando recebemos um registro relacionado, retirado do dicionário por chave como uma sequência de texto.
- Precisamos de texto transformar em um valor do tipo tabela pessoa (para cada tabela é criado automaticamente um tipo com o mesmo nome).
- “Expanda” o registro digitado em colunas usando
(...).*.
dicionário json
Mas um truque como o que aplicamos acima não funcionará se não houver um tipo de tabela correspondente para fazer a “conversão”. Irá surgir exactamente a mesma situação, e se tentarmos usar uma linha CTE, não uma tabela "real".
Neste caso eles vão nos ajudar :
...
, 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;
Deve-se notar que ao descrever a estrutura de destino, não podemos listar todos os campos da string de origem, mas apenas aqueles que realmente precisamos. Se tivermos uma tabela “nativa”, então é melhor usar a função json_populate_record.
Ainda acessamos o dicionário uma vez, mas os custos de serialização json-[de] são bastante altos, portanto, é razoável usar este método apenas em alguns casos, quando o CTE Scan “honesto” se mostra pior.
Testando desempenho
Então, temos duas maneiras de serializar dados em um dicionário - hstore/json_object. Além disso, os próprios arrays de chaves e valores também podem ser gerados de duas formas, com conversão interna ou externa para texto: array_agg(i::texto) / array_agg(i)::texto[].
Vamos verificar a eficácia dos diferentes tipos de serialização usando um exemplo puramente sintético - serializar diferentes números de chaves:
WITH dict AS (
SELECT
hstore(
array_agg(i::text)
, array_agg(i::text)
)
FROM
generate_series(1, ...) i
)
TABLE dict;Script de avaliação: serialização
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;
No PostgreSQL 11, até aproximadamente um tamanho de dicionário de 2 ^ 12 chaves serialização para json leva menos tempo. Neste caso, o mais eficaz é a combinação de json_object e conversão de tipo “interno” array_agg(i::text).
Agora vamos tentar ler o valor de cada chave 8 vezes - afinal, se você não acessa o dicionário, por que ele é necessário?
Roteiro de avaliação: leitura de um dicionário
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;
E... já aproximadamente com chaves 2 ^ 6, a leitura de um dicionário json começa a perder várias vezes lendo do hstore, para jsonb o mesmo acontece em 2 ^ 9.
Conclusões finais:
- se você precisar fazer isso JOIN com vários registros repetidos — é melhor usar o “dicionário” da tabela
- se o seu dicionário é esperado pequeno e você não lerá muito dele - você pode usar json[b]
- em todos os outros casos hstore + array_agg(i::texto) será mais eficaz
Fonte: habr.com