explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hk2 : Optimization for: plan #WROt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 5,853.492 31,761.699 ↓ 1,414.3 1,326,610 1

Sort (cost=751,075.17..751,077.52 rows=938 width=49) (actual time=31,624.503..31,761.699 rows=1,326,610 loops=1)

  • Sort Key: t.trace_id, m.created_on
  • Sort Method: quicksort Memory: 181031kB
2.          

CTE initial_events

3. 0.000 3,824.362 ↓ 1.0 372,860 1

Gather (cost=1,000.00..578,639.42 rows=368,173 width=8) (actual time=3,611.625..3,824.362 rows=372,860 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 4,097.318 4,097.318 ↑ 1.2 124,287 3

Parallel Seq Scan on money_transfer_management (cost=0.00..540,822.12 rows=153,405 width=8) (actual time=3,603.464..4,097.318 rows=124,287 loops=3)

  • Filter: ((created_on >= '2018-12-01 00:00:00'::timestamp without time zone) AND (created_on <= '2018-12-31 23:59:59.999'::timestamp without time zone) AND (auditable_event_state_type_id = 1))
  • Rows Removed by Filter: 9154270
5. 458.302 25,908.207 ↓ 1,414.3 1,326,610 1

Hash Join (cost=163,506.93..172,389.45 rows=938 width=49) (actual time=18,135.572..25,908.207 rows=1,326,610 loops=1)

  • Hash Cond: (a.legacy_transaction_state_id = l.legacy_transaction_state_id)
6. 551.176 25,449.879 ↓ 1,414.3 1,326,610 1

Hash Join (cost=163,505.77..172,384.34 rows=938 width=40) (actual time=18,135.496..25,449.879 rows=1,326,610 loops=1)

  • Hash Cond: (m.auditable_event_state_type_id = a.auditable_event_state_type_id)
7. 464.697 24,898.674 ↓ 1,414.3 1,326,610 1

Nested Loop (cost=163,504.30..172,379.90 rows=938 width=24) (actual time=18,135.438..24,898.674 rows=1,326,610 loops=1)

  • Join Filter: (ie.money_transfer_event_id = m.money_transfer_event_id)
8. 1,315.530 19,794.503 ↓ 2,651.1 331,391 1

Hash Join (cost=163,503.74..172,249.10 rows=125 width=28) (actual time=18,125.434..19,794.503 rows=331,391 loops=1)

  • Hash Cond: (ie.money_transfer_event_id = t.money_transfer_event_id)
9. 3,965.544 3,965.544 ↓ 1.0 372,860 1

CTE Scan on initial_events ie (cost=0.00..7,363.46 rows=368,173 width=8) (actual time=3,611.632..3,965.544 rows=372,860 loops=1)

10. 2,540.201 14,513.429 ↓ 2,641.4 6,299,631 1

Hash (cost=163,473.93..163,473.93 rows=2,385 width=20) (actual time=14,513.429..14,513.429 rows=6,299,631 loops=1)

  • Buckets: 8388608 (originally 4096) Batches: 2 (originally 1) Memory Usage: 241665kB
11. 1,694.814 11,973.228 ↓ 2,641.4 6,299,631 1

Nested Loop (cost=12,810.39..163,473.93 rows=2,385 width=20) (actual time=8,010.588..11,973.228 rows=6,299,631 loops=1)

12. 0.010 0.146 ↑ 1.0 1 1

Nested Loop (cost=0.28..10.42 rows=1 width=8) (actual time=0.133..0.146 rows=1 loops=1)

13. 0.095 0.095 ↑ 1.0 1 1

Index Scan using fki_client_id_fk on account_holder ah (cost=0.28..8.30 rows=1 width=16) (actual time=0.091..0.095 rows=1 loops=1)

  • Index Cond: (company_id = '107150'::bigint)
14. 0.041 0.041 ↑ 1.0 1 1

Seq Scan on company c (cost=0.00..2.11 rows=1 width=8) (actual time=0.033..0.041 rows=1 loops=1)

  • Filter: (company_id = '107150'::bigint)
  • Rows Removed by Filter: 88
15. 2,343.452 10,278.268 ↓ 48.6 6,299,631 1

Bitmap Heap Scan on money_transfer_auditable_event t (cost=12,810.11..162,166.02 rows=129,749 width=28) (actual time=8,010.442..10,278.268 rows=6,299,631 loops=1)

  • Recheck Cond: (initiator_id = ah.account_holder_id)
  • Heap Blocks: exact=145299
16. 7,934.816 7,934.816 ↓ 48.6 6,302,598 1

Bitmap Index Scan on money_transfer_auditable_event_trace_id (cost=0.00..12,777.68 rows=129,749 width=0) (actual time=7,934.816..7,934.816 rows=6,302,598 loops=1)

  • Index Cond: (initiator_id = ah.account_holder_id)
17. 4,639.474 4,639.474 ↑ 1.8 4 331,391

Index Scan using money_transfer_management_event_state_user on money_transfer_management m (cost=0.56..0.96 rows=7 width=20) (actual time=0.006..0.014 rows=4 loops=331,391)

  • Index Cond: (money_transfer_event_id = t.money_transfer_event_id)
18. 0.012 0.029 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=24) (actual time=0.029..0.029 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.017 0.017 ↑ 1.0 21 1

Seq Scan on auditable_event_state_type a (cost=0.00..1.21 rows=21 width=24) (actual time=0.011..0.017 rows=21 loops=1)

20. 0.012 0.026 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=17) (actual time=0.026..0.026 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.014 0.014 ↑ 1.0 7 1

Seq Scan on legacy_transaction_state l (cost=0.00..1.07 rows=7 width=17) (actual time=0.011..0.014 rows=7 loops=1)