postgresql: оптимизация хранимой процедуры при выполнении однотипных запросов в CASE

Question

есть таблица библиотек libraries и таблица files, которые связаны через таблицу library_file_relationships через связку (library_id, file_id)

необходимо обновить библиотек поле status из принципа:

если все файлы, связанные с библиотекой имеет статус analyzed - выставить статус analyzed
если все файлы, связанные с библиотекой имеет статус waiting - выставить статус waiting
если хотя бы 1 файл имеет статус in progress - выставить статус in progress
иначе если хотя бы 1 файл имеет статус pending - выставить статус pending
иных статусов нет, только эти 4

также необходимо обновить поле 'analyzed_at' но только для библиотек у которых все, связанные с ними файлы, имеют статус 'analyzed' по принципу:

analyzed_at выставляется самым максимальным (поле timestamp) из всех analyzed_at связанных файлов

создал такую хранимую процедуру:

CREATE OR REPLACE FUNCTION libraries_update_statuses(
    )
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    WITH src_t AS (
        SELECT rel_t.library_id, f.status, f.analyzed_at
        FROM library_file_relationships AS rel_t
        JOIN files AS f ON f.id = rel_t.file_id
    )
    UPDATE libraries AS dst_t SET
        -- задать статус библиотеке
        status = CASE
            -- если хотя бы один файл имеет статус 'in progress' - статус библиотеки - 'in progress'
            WHEN EXISTS (
                SELECT 1 FROM src_t 
                WHERE library_id = dst_t.id AND status = 'in progress'
            ) THEN 'in progress'
            -- если хотя бы один файл имеет статус 'pending' - статус библиотеки - 'pending'
            WHEN EXISTS (
                SELECT 1 FROM src_t 
                WHERE library_id = dst_t.id AND status = 'pending'
            ) THEN 'pending'
            -- если все файлы имеют статус 'analyzed' - статус библиотеки - 'analyzed'
            WHEN NOT EXISTS (
                SELECT 1 FROM src_t 
                WHERE library_id = dst_t.id AND status != 'analyzed'
            ) THEN 'analyzed'
            -- если все файлы имеют статус 'waiting' - статус библиотеки - 'waiting'
            ELSE 'waiting'
        END,
        -- задать время анализа библиотеке
        analyzed_at = CASE
            WHEN NOT EXISTS (
                SELECT 1 FROM src_t 
                WHERE library_id = dst_t.id AND status != 'analyzed'
            ) THEN (
                SELECT max(analyzed_at) 
                FROM src_t 
                WHERE library_id = dst_t.id AND status = 'analyzed'
            )
            ELSE dst_t.analyzed_at
        END
    WHERE EXISTS (
        SELECT 1 FROM src_t 
        WHERE library_id = dst_t.id
    );
END;
$BODY$;

Хранимка работает - тут вопросов нет, НО...

можно ли ее ускорить - потому что очень много на мой взгляд одного и того же кода выполняется, который можно как-то выполнить однократно
можно ли сделать ее эстетически красивее :)
дополнительно для ускорения какие поля стоит проиндексировать?

Answer 1

Сделал вот такой запрос - позволил более, чем в 4 раза сократить время исполнения

WITH src_t AS (
    SELECT 
        l.id AS id,
        ARRAY_AGG(DISTINCT f.status) AS statuses,
        MAX(f.analyzed_at) AS analyzed_at
    FROM libraries l
    JOIN library_file_relationships r ON l.id = r.library_id
    JOIN files f ON r.file_id = f.id
    GROUP BY l.id
)
UPDATE libraries as dst_t
SET 
    status = CASE
        WHEN 'in progress' = ANY(src_t.statuses) THEN 'in progress'
        WHEN 'pending' = ANY(src_t.statuses) THEN 'pending'
        WHEN 'analyzed' = ALL(src_t.statuses) THEN 'analyzed'
        ELSE 'waiting'
    END,
    analyzed_at = CASE
        WHEN 'analyzed' = ALL(src_t.statuses) 
        THEN src_t.analyzed_at
        ELSE dst_t.analyzed_at
    END 
FROM src_t
WHERE dst_t.id = src_t.id AND array_length(src_t.statuses, 1) > 0;

Кстати он почему-то немного быстрее, схожего, но без WITH:

UPDATE libraries as dst_t
SET 
    status = CASE
        WHEN 'in progress' = ANY(src_t.statuses) THEN 'in progress'
        WHEN 'pending' = ANY(src_t.statuses) THEN 'pending'
        WHEN 'analyzed' = ALL(src_t.statuses) THEN 'analyzed'
        ELSE 'waiting'
    END,
    analyzed_at = CASE
        WHEN 'analyzed' = ALL(src_t.statuses) 
        THEN src_t.analyzed_at
        ELSE dst_t.analyzed_at
    END 
FROM (
    SELECT 
        l.id AS id,
        ARRAY_AGG(DISTINCT f.status) AS statuses,
        MAX(f.analyzed_at) AS analyzed_at
    FROM libraries l
    JOIN library_file_relationships r ON l.id = r.library_id
    JOIN files f ON r.file_id = f.id
    GROUP BY l.id
) src_t
WHERE dst_t.id = src_t.id AND array_length(src_t.statuses, 1) > 0;

Не подскажете, в чем тут дело?

Кстати может и этот алгоритм можно улучшить?

БЛОГ НА HUSL

postgresql: оптимизация хранимой процедуры при выполнении однотипных запросов в CASE

Ответы (1 шт):