Ускорить простой запрос с CTE : PostgreSQL

█ 08.07.2023 22:24

Использую PGSQL, есть простой запрос,

SQL код:

 
    EXPLAIN (ANALYZE,BUFFERS)

    WITH data_to_cte AS (

        SELECT

            unnest[1]::integer account

        ,    unnest[2]::text user_id

        ,    unnest[3]::integer target_id

        FROM

            (

                SELECT

                    unnest('{ "{ 7267996,4,96394594 }" }'::text[])::text[]

            ) f

    )

    SELECT

        "DocumentsId",

        data_to_cte.*

    FROM

        data_to_cte

    JOIN

        "Documents"

            on "Account" = account AND 

            "Target" = target_id and

            "User" = user_id  AND

            "User" IS NOT NULL

план такой

SQL код:

 
Nested Loop  (cost=0.58..8.68 rows=1 width=48) (actual time=23.352..23.353 rows=0 loops=1)

  Buffers: shared hit=16730

  ->  Result  (cost=0.00..0.04 rows=1 width=32) (actual time=0.008..0.012 rows=1 loops=1)

        ->  ProjectSet  (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.007 rows=1 loops=1)

              ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)

  ->  Index Scan using "iDocuments-AccountUser" on "Documents"  (cost=0.58..8.61 rows=1 width=28) (actual time=23.334..23.334 rows=0 loops=1)

        Index Cond: (("Account" = (((((unnest('{"{ 7267996,4,96394594 }"}'::text[])))::text[]))[1])::integer) AND ("User" = ((((unnest('{"{ 7267996,4,96394594 }"}'::text[])))::text[]))[2]))

        Filter: ((((((unnest('{"{ 7267996,4,96394594 }"}'::text[])))::text[]))[3])::integer = "Target")

        Rows Removed by Filter: 19163

        Buffers: shared hit=16730

Planning Time: 0.318 ms

Execution Time: 23.397 ms

Хочется чтобы использовался такой индекс

SQL код:

 
CREATE INDEX "iDocuments-AccountTarget"

    ON "Documents" USING btree

    ("Account" NULLS LAST, "Target" NULLS LAST, "User" NULLS LAST)

    WHERE ("User" IS NOT NULL);

Что нужно сделать, возможно переписать запрос?

Например, если указать напрямую Target, то используется нужный индекс

SQL код:

 
EXPLAIN (ANALYZE,BUFFERS)

    WITH data_to_cte AS (

        SELECT

            unnest[1]::integer account

        ,    unnest[2]::text user_id

        ,    unnest[3]::integer target_id

        FROM

            (

                SELECT

                    unnest('{ "{ 7267996,4,96394594 }" }'::text[])::text[]

            ) f

    )

    SELECT

        "DocumentsId",

        data_to_cte.*

    FROM

        data_to_cte

    JOIN

        "Documents"

            on "Account" = account AND 

            "Target" = 4 and

            "User" = user_id  AND

            "User" IS NOT NULL

█ 08.07.2023 22:34

Для ускорения запроса с использованием Common Table Expressions (CTE) в PostgreSQL вы можете выполнить следующие действия:

1. Установите индексы на столбцы, используемые в предикатах соединения (JOIN). Например, установите индексы на столбцы "Account" и "_id" в таблице "Docs".

2. Перепишите запрос, чтобы в нем использовались простые подзапросы или внутренние объединения (INNER JOIN) вместо CTE. Например:

Код:

sql
SELECT da.*, "Docs".*
FROM da
JOIN (
    SELECT *
    FROM "Docs"
    WHERE "Account" = 4
) AS "Docs" ON da."Account" = "Docs"."Account" AND da."_id" = "Docs"."_id"

3. Убедитесь, что статистика PostgreSQL актуальна для таблиц, используемых в запросе. Вы можете вручную обновить статистику с помощью команды ANALYZE:

Код:

sql
ANALYZE da;
ANALYZE "Docs";

4. Проверьте план выполнения запроса с помощью команды EXPLAIN и определите, какие операции занимают больше всего времени. Возможно, вам понадобится более детальное понимание структуры таблиц и объема данных, чтобы оптимизировать запрос.

5. Если возможно, увеличьте объем доступной памяти для PostgreSQL, чтобы улучшить производительность запроса.

Обратите внимание, что эти рекомендации предполагают, что вы уже оптимизировали структуру базы данных и используете подходящие индексы на других запросах.

█ 08.07.2023 22:40

SQL код:

 
SELECT

    "DocumentsId",

    data_to_cte.*

FROM

    unnest(ARRAY[7267996, 4, 96394594]) WITH ORDINALITY AS data_to_cte(account, user_id, target_id)

JOIN

    "Documents"

        ON "Account" = account

        AND "Target" = target_id

        AND "User" = user_id

        AND "User" IS NOT NULL;

не оно?
Настоятельно не рекомендую использовать with без очень и очень весомых причин. У тебя же еще и неявное приведение типов, возможно, что оно влияет на использование индексов.