Оптимизировать запрос в котором группируется большое кол-во данных

@polin11 · Регистрация: 24.03.2013

Студворк — интернет-сервис помощи студентам

Запрос

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
WITH         
        ar AS(
            SELECT *
            FROM "ActualDocuments"
            WHERE 
            "Account" = ANY('{3344433}'::BIGINT[]) AND
            "MinimalDate" < '2021-09-20'::DATE           
        ),      
        user_null AS(
            SELECT
                   DISTINCT
                pp."Account" AS "Account",
                pp."User" AS "Responsible",
                pp."Event" AS "Event",
                array_agg(DISTINCT pp."Subscriber") AS "Subscriber",
                MIN(pp."EndDate") AS "MinimalDate"
            FROM "Documents" pp           
                INNER JOIN ar ON (
                    pp."Account" = ar."Account" AND
                    pp."Event" = ar."Event"
                )          
            WHERE               
                pp."User" IS NULL                
            GROUP BY pp."Account", pp."User", pp."Event"
        ),
        user_not_null AS(
            SELECT DISTINCT
                pp."Account" AS "Account",
                pp."User" AS "Responsible",
                pp."Event" AS "Event",
                array_agg(DISTINCT pp."Subscriber") AS "Subscriber",
                MIN(pp."EndDate") AS "MinimalDate"
            FROM "Documents" pp         
                INNER JOIN ar ON (
                    pp."Account" = ar."Account" AND
                    pp."Event" = ar."Event"
                )
            WHERE                
                pp."User" IS NOT NULL                
            GROUP BY pp."Account", pp."User", pp."Event"
        )
        SELECT *
        FROM user_not_null 
        UNION
        SELECT *
        FROM user_null

План

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
[SQL][SQL]"HashAggregate  (cost=29881.28..29915.65 rows=3437 width=64) (actual time=4117.404..4151.053 rows=107270 loops=1)"
"  Group Key: user_not_null."Account", user_not_null."Responsible", user_not_null."Event", user_not_null."Subscriber", user_not_null."MinimalDate""
"  Buffers: shared hit=735944 dirtied=3, temp read=6722 written=8057"
"  CTE ar"
"    ->  Bitmap Heap Scan on "ActualDocuments"  (cost=36.30..1993.75 rows=465 width=73) (actual time=0.216..1.173 rows=120 loops=1)"
"          Recheck Cond: (("Account" = ANY ('{3344433}'::bigint[])) AND ("MinimalDate" IS NOT NULL))"
"          Filter: ("MinimalDate" < '2021-09-20'::date)"
"          Rows Removed by Filter: 119"
"          Heap Blocks: exact=277"
"          Buffers: shared hit=286"
"          ->  Bitmap Index Scan on "iMinimalDate"  (cost=0.00..36.18 rows=519 width=0) (actual time=0.171..0.172 rows=883 loops=1)"
"                Index Cond: ("Account" = ANY ('{3344433}'::bigint[]))"
"                Buffers: shared hit=9"
"  CTE user_null"
"    ->  HashAggregate  (cost=13603.55..13633.30 rows=2975 width=64) (actual time=1718.383..1718.436 rows=68 loops=1)"
"          Group Key: pp."Account", pp."USER", pp."Event", array_agg(DISTINCT pp."Subscriber"), min(pp."EndDate")"
"          Buffers: shared hit=308557 dirtied=3, temp read=3060 written=3080"
"          ->  GroupAggregate  (cost=13484.55..13566.36 rows=2975 width=64) (actual time=710.150..1714.046 rows=68 loops=1)"
"                Group Key: pp."Account", pp."USER", pp."Event""
"                Buffers: shared hit=308557 dirtied=3, temp read=3060 written=3080"
"                ->  Sort  (cost=13484.55..13491.99 rows=2975 width=37) (actual time=701.077..778.699 rows=343548 loops=1)"
"                      Sort Key: pp."Account", pp."USER", pp."Event""
"                      Sort Method: external merge  Disk: 11032kB"
"                      Buffers: shared hit=308557 dirtied=3, temp read=3060 written=3080"
"                      ->  Nested Loop  (cost=0.56..13312.91 rows=2975 width=37) (actual time=0.037..418.331 rows=343548 loops=1)"
"                            Buffers: shared hit=308557 dirtied=3"
"                            ->  CTE Scan on ar  (cost=0.00..9.30 rows=465 width=12) (actual time=0.001..0.143 rows=120 loops=1)"
"                            ->  Index Scan using "iUserNull" on "Documents" pp  (cost=0.56..28.55 rows=6 width=37) (actual time=0.012..3.119 rows=2863 loops=120)"
"                                  Index Cond: (("Account" = ar."Account") AND ("Event" = ar."Event"))"
"                                  Buffers: shared hit=308557 dirtied=3"
"  CTE user_not_null"
"    ->  Unique  (cost=14101.23..14108.16 rows=462 width=64) (actual time=2247.025..2294.759 rows=107202 loops=1)"
"          Buffers: shared hit=427387, temp read=3662 written=3677"
"          ->  Sort  (cost=14101.23..14102.38 rows=462 width=64) (actual time=2247.023..2260.200 rows=107202 loops=1)"
"                Sort Key: pp_1."Account", pp_1."USER", pp_1."Event", (array_agg(DISTINCT pp_1."Subscriber")), (min(pp_1."EndDate"))"
"                Sort Method: external sort  Disk: 8032kB"
"                Buffers: shared hit=427387, temp read=3662 written=3677"
"                ->  GroupAggregate  (cost=14068.07..14080.78 rows=462 width=64) (actual time=1712.571..2077.099 rows=107202 loops=1)"
"                      Group Key: pp_1."Account", pp_1."USER", pp_1."Event""
"                      Buffers: shared hit=427387, temp read=2658 written=2673"
"                      ->  Sort  (cost=14068.07..14069.23 rows=462 width=37) (actual time=1712.535..1783.618 rows=237252 loops=1)"
"                            Sort Key: pp_1."Account", pp_1."USER", pp_1."Event""
"                            Sort Method: external merge  Disk: 11200kB"
"                            Buffers: shared hit=427387, temp read=2658 written=2673"
"                            ->  Nested Loop  (cost=0.56..14047.62 rows=462 width=37) (actual time=0.258..1412.308 rows=237252 loops=1)"
"                                  Buffers: shared hit=427387"
"                                  ->  CTE Scan on ar ar_1  (cost=0.00..9.30 rows=465 width=12) (actual time=0.218..1.511 rows=120 loops=1)"
"                                        Buffers: shared hit=286"
"                                  ->  Index Scan using "iUser" on "Documents" pp_1  (cost=0.56..30.18 rows=1 width=37) (actual time=0.235..11.441 rows=1977 loops=120)"
"                                        Index Cond: (("Account" = ar_1."Account") AND ("Event" = ar_1."Event"))"
"                                        Buffers: shared hit=427101"
"  ->  Append  (cost=0.00..103.11 rows=3437 width=64) (actual time=2247.028..4050.176 rows=107270 loops=1)"
"        Buffers: shared hit=735944 dirtied=3, temp read=6722 written=8057"
"        ->  CTE Scan on user_not_null  (cost=0.00..9.24 rows=462 width=64) (actual time=2247.027..2320.721 rows=107202 loops=1)"
"              Buffers: shared hit=427387, temp read=3662 written=4678"
"        ->  CTE Scan on user_null  (cost=0.00..59.50 rows=2975 width=64) (actual time=1718.393..1721.813 rows=68 loops=1)"
"              Buffers: shared hit=308557 dirtied=3, temp read=3060 written=3379"
"Planning time: 0.961 ms"
"Execution time: 4164.426 ms"

[/SQL][/SQL]

Для некоторых аккаунтов получается большое количество записей, которые нужно сгруппировать, из-за этого
запрос долго выполняется по времени и shared hit memory.
У меня идей не осталось

@grgdvo · 29.09.2021, 22:48

У вас явно не хватает work_mem, потому что узлы сортировки говорят, что использовали дисковую память для сортировки, - это очень медленно. Стоит увеличить параметр work_mem до 32Мб хотя бы.

Ну и честно говоря запрос просится быть переписанным без CTE, что должно упростить его план и соответственно ускорить выполнение.
Как-то вот так вроде похоже, что будет одинаково

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
   SELECT DISTINCT
                pp."Account" AS "Account",
                pp."User" AS "Responsible",
                pp."Event" AS "Event",
                array_agg(DISTINCT pp."Subscriber") AS "Subscriber",
                MIN(pp."EndDate") AS "MinimalDate"
            FROM "Documents" pp         
                INNER JOIN "ActualDocuments" ar ON (
                    pp."Account" = ar."Account" AND
                    pp."Event" = ar."Event" AND
            "Account" = ANY('{3344433}'::BIGINT[]) AND
            "MinimalDate" < '2021-09-20'::DATE           
                )
            GROUP BY pp."Account", pp."User", pp."Event"
ORDER BY pp."User" NULLS LAST, pp."Account", pp."Event"

Добавлено через 2 минуты
А... нет, сорри, с сортировкой накосячил... Не будет одинаково

Добавлено через 6 минут

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH some_user AS (
SELECT DISTINCT
                pp."Account" AS "Account",
                pp."User" AS "Responsible",
                pp."Event" AS "Event",
                array_agg(DISTINCT pp."Subscriber") AS "Subscriber",
                MIN(pp."EndDate") AS "MinimalDate"
            FROM "Documents" pp         
                INNER JOIN "ActualDocuments" ar ON (
                    pp."Account" = ar."Account" AND
                    pp."Event" = ar."Event" AND
            "Account" = ANY('{3344433}'::BIGINT[]) AND
            "MinimalDate" < '2021-09-20'::DATE           
                )
            GROUP BY pp."Account", pp."User", pp."Event"
)
SELECT * FROM some_user WHERE "Responsible" IS NOT NULL
UNION ALL
SELECT * FROM some_user WHERE "Responsible" IS NULL;

Вот так вроде. И еще заметил у вас исходно было UNION?? Зачем - это же еще одно действие по слиянию двух выборок. Можно же просто UNION ALL, в конце уже не ожидается дубликатов, их DISTINCT уже должен был убрать к этому моменту.

@Аватар · 30.09.2021, 08:09

В последнем запросе DISTINCT не нужен - группировка и так дублей не сделает. И union не нужен, и WITH конечно - одним запросом без where, но с сортировкой:

SQL
1
ORDER BY CASE WHEN pp."User" IS NULL THEN 1 ELSE 0 END,pp."Account",pp."User",pp."Event"

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Реализация движения на Box2D v3 - трение и коллизии с повёрнутыми стенами 8Observer8 20.02.2026 Содержание блога Box2D позволяет легко создать главного героя, который не проходит сквозь стены и перемещается с заданным трением о препятствия, которые можно располагать под углом, как верхнее. . .	Конвертировать закладки radiotray-ng в m3u-плейлист damix 19.02.2026 Это можно сделать скриптом для PowerShell. Использование . \СonvertRadiotrayToM3U. ps1 <path_to_bookmarks. json> Рядом с файлом bookmarks. json появится файл bookmarks. m3u с результатом. # Check if. . .	Семь CDC на одном интерфейсе: 5 U[S]ARTов, 1 CAN и 1 SSI Eddy_Em 18.02.2026 Постепенно допиливаю свою "многоинтерфейсную плату". Выглядит вот так: https:/ / www. cyberforum. ru/ blog_attachment. php?attachmentid=11617&stc=1&d=1771445347 Основана на STM32F303RBT6. На борту пять. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .
И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»	SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .