Использую 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, но это не хочется делать так как такое условие достаточно редкое.
Может есть другие варианты? |