Periodicamente, xorde a tarefa de buscar datos relacionados mediante un conxunto de claves. ata obter o número total de rexistros necesario.
O exemplo máis "real" é mostrar 20 problemas máis antigos, listado na lista de empregados (por exemplo, dentro dunha división). Para varios "paneis de control" de xestión con breves resumos das áreas de traballo, é necesario un tema similar con bastante frecuencia.
Neste artigo analizaremos a implementación en PostgreSQL dunha solución "inxenua" a tal problema, un algoritmo "máis intelixente" e moi complexo. "bucle" en SQL cunha condición de saída dos datos atopados, que pode ser útil tanto para o desenvolvemento xeral como para o seu uso noutros casos similares.
Tomemos un conxunto de datos de proba
CREATE INDEX ON task(owner_id, task_date, id);
-- а старый - удалим
DROP INDEX task_owner_id_task_date_idx;
Como se escoita, así está escrito
En primeiro lugar, esbocemos a versión máis sinxela da solicitude, pasando os ID dos intérpretes
SELECT
*
FROM
task
WHERE
owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
ORDER BY
task_date, id
LIMIT 20;
Un pouco triste: só pedimos 20 rexistros, pero Index Scan devolveunos 960 liñas, que logo tamén había que ordenar... Tentemos ler menos.
unnest + ARRAY
A primeira consideración que nos axudará é se o necesitamos só 20 clasificados rexistros, despois só ler non máis de 20 clasificados na mesma orde para cada un chave. ben, índice adecuado (owner_id, task_date, id) temos.
Usemos o mesmo mecanismo para extraer e "estender en columnas" rexistro de táboa integral, como en ARRAY()
:
WITH T AS (
SELECT
unnest(ARRAY(
SELECT
t
FROM
task t
WHERE
owner_id = unnest
ORDER BY
task_date, id
LIMIT 20 -- ограничиваем тут...
)) r
FROM
unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
(r).*
FROM
T
ORDER BY
(r).task_date, (r).id
LIMIT 20; -- ... и тут - тоже
Ah, xa moito mellor! Un 40 % máis rápido e 4.5 veces menos datos Tiven que lelo.
Materialización de rexistros de táboa mediante CTEPermíteme chamar a túa atención sobre o feito de que nalgúns casos Un intento de traballar inmediatamente cos campos dun rexistro despois de buscalo nunha subconsulta, sen "envolvelo" nun CTE, pode levar a "multiplicar" InitPlan proporcional ao número destes mesmos campos:
SELECT
((
SELECT
t
FROM
task t
WHERE
owner_id = 1
ORDER BY
task_date, id
LIMIT 1
).*);
Result (cost=4.77..4.78 rows=1 width=16) (actual time=0.063..0.063 rows=1 loops=1)
Buffers: shared hit=16
InitPlan 1 (returns $0)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.031..0.032 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t (cost=0.42..387.57 rows=500 width=48) (actual time=0.030..0.030 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4
InitPlan 2 (returns $1)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t_1 (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4
InitPlan 3 (returns $2)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t_2 (cost=0.42..387.57 rows=500 width=48) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4"
InitPlan 4 (returns $3)
-> Limit (cost=0.42..1.19 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using task_owner_id_task_date_id_idx on task t_3 (cost=0.42..387.57 rows=500 width=48) (actual time=0.009..0.009 rows=1 loops=1)
Index Cond: (owner_id = 1)
Buffers: shared hit=4
O mesmo rexistro foi "buscado" 4 veces... Ata PostgreSQL 11, este comportamento ocorre con regularidade, e a solución é "envolvelo" nun CTE, o que é un límite absoluto para o optimizador nestas versións.
Acumulador recursivo
Na versión anterior, en total lemos 200 liñas polo ben do necesario 20. Non 960, pero aínda menos - é posible?
Intentemos utilizar o coñecemento que necesitamos en total 20 rexistros. É dicir, iteraremos a lectura de datos só ata chegar á cantidade que necesitamos.
Paso 1: Lista de inicio
Obviamente, a nosa lista "obxectivo" de 20 rexistros debería comezar cos "primeiros" rexistros dunha das nosas chaves owner_id. Polo tanto, primeiro atoparemos tal "moi primeiro" para cada unha das teclas e engádeo á lista, clasificándoo na orde que queremos - (data_tarefa, id).
Paso 2: Busca as "seguintes" entradas
Agora si tomamos a primeira entrada da nosa lista e comezamos "paso" máis ao longo do índice conservando a chave owner_id, entón todos os rexistros atopados son exactamente os seguintes na selección resultante. Por suposto, só ata cruzar a chave de culata segunda entrada da lista.
Se resulta que "cruzamos" o segundo rexistro, entón a última entrada lida debería engadirse á lista en lugar da primeira (co mesmo owner_id), despois de que reordenamos a lista de novo.
É dicir, sempre obtemos que a lista non ten máis que unha entrada para cada unha das claves (se se esgotan as entradas e non nos “cruzamos”, entón a primeira entrada da lista simplemente desaparecerá e non se engadirá nada. ), e eles sempre ordenado en orde ascendente da clave da aplicación (task_date, id).
Paso 3: filtrar e "expandir" os rexistros
Nalgunhas das filas da nosa selección recursiva, algúns rexistros rv
duplícanse - primeiro atopamos como "atravesar a fronteira da 2ª entrada da lista", e despois substitúea como a primeira da lista. Polo tanto, hai que filtrar a primeira aparición.
A temida consulta final
WITH RECURSIVE T AS (
-- #1 : заносим в список "первые" записи по каждому из ключей набора
WITH wrap AS ( -- "материализуем" record'ы, чтобы обращение к полям не вызывало умножения InitPlan/SubPlan
WITH T AS (
SELECT
(
SELECT
r
FROM
task r
WHERE
owner_id = unnest
ORDER BY
task_date, id
LIMIT 1
) r
FROM
unnest('{1,2,4,8,16,32,64,128,256,512}'::integer[])
)
SELECT
array_agg(r ORDER BY (r).task_date, (r).id) list -- сортируем список в нужном порядке
FROM
T
)
SELECT
list
, list[1] rv
, FALSE not_cross
, 0 size
FROM
wrap
UNION ALL
-- #2 : вычитываем записи 1-го по порядку ключа, пока не перешагнем через запись 2-го
SELECT
CASE
-- если ничего не найдено для ключа 1-й записи
WHEN X._r IS NOT DISTINCT FROM NULL THEN
T.list[2:] -- убираем ее из списка
-- если мы НЕ пересекли прикладной ключ 2-й записи
WHEN X.not_cross THEN
T.list -- просто протягиваем тот же список без модификаций
-- если в списке уже нет 2-й записи
WHEN T.list[2] IS NULL THEN
-- просто возвращаем пустой список
'{}'
-- пересортировываем словарь, убирая 1-ю запись и добавляя последнюю из найденных
ELSE (
SELECT
coalesce(T.list[2] || array_agg(r ORDER BY (r).task_date, (r).id), '{}')
FROM
unnest(T.list[3:] || X._r) r
)
END
, X._r
, X.not_cross
, T.size + X.not_cross::integer
FROM
T
, LATERAL(
WITH wrap AS ( -- "материализуем" record
SELECT
CASE
-- если все-таки "перешагнули" через 2-ю запись
WHEN NOT T.not_cross
-- то нужная запись - первая из спписка
THEN T.list[1]
ELSE ( -- если не пересекли, то ключ остался как в предыдущей записи - отталкиваемся от нее
SELECT
_r
FROM
task _r
WHERE
owner_id = (rv).owner_id AND
(task_date, id) > ((rv).task_date, (rv).id)
ORDER BY
task_date, id
LIMIT 1
)
END _r
)
SELECT
_r
, CASE
-- если 2-й записи уже нет в списке, но мы хоть что-то нашли
WHEN list[2] IS NULL AND _r IS DISTINCT FROM NULL THEN
TRUE
ELSE -- ничего не нашли или "перешагнули"
coalesce(((_r).task_date, (_r).id) < ((list[2]).task_date, (list[2]).id), FALSE)
END not_cross
FROM
wrap
) X
WHERE
T.size < 20 AND -- ограничиваем тут количество
T.list IS DISTINCT FROM '{}' -- или пока список не кончился
)
-- #3 : "разворачиваем" записи - порядок гарантирован по построению
SELECT
(rv).*
FROM
T
WHERE
not_cross; -- берем только "непересекающие" записи
Así, nós negociou o 50% das lecturas de datos durante o 20% do tempo de execución. É dicir, se tes motivos para crer que a lectura pode levar moito tempo (por exemplo, os datos moitas veces non están na caché e tes que ir ao disco para iso), entón, deste xeito, podes depender menos da lectura. .
En calquera caso, o tempo de execución resultou ser mellor que na primeira opción "inxenua". Pero cal destas 3 opcións usar depende de ti.
Fonte: www.habr.com