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

polin11 · █ 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, чтобы улучшить производительность запроса.

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

OlegON · █ 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 без очень и очень весомых причин. У тебя же еще и неявное приведение типов, возможно, что оно влияет на использование индексов.