Оптимизировать запрос, доработать индекс

0

2

Использую POSTGRESQL. Есть запрос

EXPLAIN (ANALYZE, BUFFERS)
          SELECT
                        "Event",
                        NULL AS "User",
                        array_agg(DISTINCT "Subscriber") AS "Subscribers",
                        "Account" 
                    FROM "Document"                   
                    WHERE
                        "Account" = 3::bigint AND
                        "Event" IN (
                            SELECT
                               "@Event"
                            FROM "Event"
                            WHERE "Account" IS NULL
                        )
                        AND "User" IS NULL 
                        AND "Responsible" IS  TRUE
 
                    GROUP BY "Account", "Event"

План такой

"GroupAggregate  (cost=316074.96..317101.63 rows=45630 width=76) (actual time=873.803..1937.239 rows=614 loops=1)"
"  Group Key: "Document"."Account", "Document"."Event""
"  Buffers: shared hit=56295, temp read=3000 written=3020"
"  ->  Sort  (cost=316074.96..316189.03 rows=45630 width=17) (actual time=873.780..917.966 rows=325330 loops=1)"
"        Sort Key: "Document"."Event""
"        Sort Method: external merge  Disk: 11064kB"
"        Buffers: shared hit=56295, temp read=3000 written=3020"
"        ->  Hash Join  (cost=18182.35..311605.72 rows=45630 width=17) (actual time=50.095..709.771 rows=325330 loops=1)"
"              Hash Cond: ("Document"."Event" = "Event"."@Event")"
"              Buffers: shared hit=56295"
"              ->  Bitmap Heap Scan on "Document"  (cost=15425.55..307597.03 rows=476856 width=17) (actual time=35.353..623.525 rows=328867 loops=1)"
"                    Recheck Cond: (("Account" = '3'::bigint) AND ("User" IS NULL))"
"                    Rows Removed by Index Recheck: 3186345"
"                    Filter: ("Responsible" IS TRUE)"
"                    Rows Removed by Filter: 29341"
"                    Heap Blocks: exact=13476 lossy=38581"
"                    Buffers: shared hit=53938"
"                    ->  Bitmap Index Scan on "iUserNull"  (cost=0.00..15306.33 rows=491436 width=0) (actual time=32.748..32.748 rows=358208 loops=1)"
"                          Index Cond: ("Account" = '3'::bigint)"
"                          Buffers: shared hit=1881"
"              ->  Hash  (cost=2714.09..2714.09 rows=3417 width=4) (actual time=14.724..14.724 rows=3430 loops=1)"
"                    Buckets: 4096  Batches: 1  Memory Usage: 153kB"
"                    Buffers: shared hit=2357"
"                    ->  Seq Scan on "Event"  (cost=0.00..2714.09 rows=3417 width=4) (actual time=0.017..14.156 rows=3430 loops=1)"
"                          Filter: ("Account" IS NULL)"
"                          Rows Removed by Filter: 32279"
"                          Buffers: shared hit=2357"
"Planning time: 0.440 ms"
"Execution time: 1940.103 ms"

Проблема с условием с «Responsible» IS TRUE. Без этого условие план намного лучше

EXPLAIN (ANALYZE, BUFFERS)
          SELECT
                        "Event",
                        NULL AS "User",
                        array_agg(DISTINCT "Subscriber") AS "Subscribers",
                        "Account" 
                    FROM "Document"                   
                    WHERE
                        "Account" = 3::bigint AND
                        "Event" IN (
                            SELECT
                               "@Event"
                            FROM "Event"
                            WHERE "Account" IS NULL
                        )
                        AND "User" IS NULL 
                    GROUP BY "Account", "Event"

План такой:

"GroupAggregate  (cost=2915.22..63175.65 rows=47026 width=76) (actual time=17.555..377.618 rows=640 loops=1)"
"  Group Key: "Document"."Account", "Document"."Event""
"  Buffers: shared hit=208932"
"  ->  Merge Join  (cost=2915.22..62235.13 rows=47026 width=17) (actual time=17.533..169.674 rows=354287 loops=1)"
"        Merge Cond: ("Document"."Event" = "Event"."@Event")"
"        Buffers: shared hit=208932"
"        ->  Index Only Scan using "iUserNull" on "Document"  (cost=0.56..57605.15 rows=491436 width=17) (actual time=0.033..100.849 rows=358216 loops=1)"
"              Index Cond: ("Account" = '3'::bigint)"
"              Heap Fetches: 34720"
"              Buffers: shared hit=206575"
"        ->  Sort  (cost=2914.64..2923.19 rows=3417 width=4) (actual time=17.488..17.935 rows=3430 loops=1)"
"              Sort Key: "Event"."@Event""
"              Sort Method: quicksort  Memory: 257kB"
"              Buffers: shared hit=2357"
"              ->  Seq Scan on "Event"  (cost=0.00..2714.09 rows=3417 width=4) (actual time=0.019..16.530 rows=3430 loops=1)"
"                    Filter: ("Account" IS NULL)"
"                    Rows Removed by Filter: 32316"
"                    Buffers: shared hit=2357"
"Planning time: 0.433 ms"
"Execution time: 377.877 ms"

Предположительно нужно доработать индекс iUserNull, добавить предикат или поле «Responsible» IS TRUE, но это не хочется делать так как такое условие достаточно редкое.

Может есть другие варианты?

Ссылка

Похожие темы