explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WMEo

Settings
# exclusive inclusive rows x rows loops node
1. 764.072 7,014.019 ↑ 1.3 49,999 1

HashAggregate (cost=194,153.50..194,797.27 rows=64,377 width=88) (actual time=6,985.594..7,014.019 rows=49,999 loops=1)

  • Group Key: el.entity_location_id, pe.entity_id, w.compliance_process_id, ae.account_id, o.origin_type_id
  • Buffers: shared hit=2733145
2. 297.011 6,249.947 ↓ 11.7 751,955 1

Hash Join (cost=161,262.96..192,705.02 rows=64,377 width=61) (actual time=4,440.766..6,249.947 rows=751,955 loops=1)

  • Hash Cond: (pel.entity_location_id = el.entity_location_id)
  • Buffers: shared hit=2733145
3. 214.879 5,797.684 ↓ 7.8 323,185 1

Nested Loop (cost=156,911.78..187,503.35 rows=41,344 width=49) (actual time=4,285.294..5,797.684 rows=323,185 loops=1)

  • Buffers: shared hit=2651601
4. 265.271 4,613.250 ↓ 8.0 323,185 1

Hash Join (cost=156,911.35..163,839.24 rows=40,399 width=53) (actual time=4,285.242..4,613.250 rows=323,185 loops=1)

  • Hash Cond: (pel.part_entity_location_id = pc.part_entity_location_id)
  • Buffers: shared hit=1356441
5. 63.420 63.420 ↑ 1.0 326,773 1

Seq Scan on part_entity_location pel (cost=0.00..5,672.73 rows=326,773 width=20) (actual time=0.017..63.420 rows=326,773 loops=1)

  • Buffers: shared hit=2405
6. 235.832 4,284.559 ↓ 8.0 323,185 1

Hash (cost=156,406.36..156,406.36 rows=40,399 width=49) (actual time=4,284.559..4,284.559 rows=323,185 loops=1)

  • Buckets: 524288 (originally 65536) Batches: 1 (originally 1) Memory Usage: 31315kB
  • Buffers: shared hit=1354036
7. 110.236 4,048.727 ↓ 8.0 323,185 1

Nested Loop (cost=11,516.57..156,406.36 rows=40,399 width=49) (actual time=265.567..4,048.727 rows=323,185 loops=1)

  • Buffers: shared hit=1354036
8. 136.408 2,968.936 ↓ 8.0 323,185 1

Hash Join (cost=11,516.14..137,512.26 rows=40,399 width=44) (actual time=265.493..2,968.936 rows=323,185 loops=1)

  • Hash Cond: (w.compliance_process_id = cp.compliance_process_id)
  • Buffers: shared hit=59502
9. 1,921.653 2,832.495 ↑ 1.0 323,188 1

Hash Join (cost=11,512.73..135,892.91 rows=323,188 width=44) (actual time=265.446..2,832.495 rows=323,188 loops=1)

  • Hash Cond: (w.work_id = o.work_id)
  • Buffers: shared hit=59499
10. 721.243 721.243 ↑ 1.0 4,791,959 1

Seq Scan on work w (cost=0.00..103,178.22 rows=4,792,022 width=36) (actual time=0.004..721.243 rows=4,791,959 loops=1)

  • Buffers: shared hit=55258
11. 99.064 189.599 ↑ 1.0 323,188 1

Hash (cost=7,472.88..7,472.88 rows=323,188 width=16) (actual time=189.599..189.599 rows=323,188 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 19246kB
  • Buffers: shared hit=4241
12. 90.535 90.535 ↑ 1.0 323,188 1

Seq Scan on origin o (cost=0.00..7,472.88 rows=323,188 width=16) (actual time=0.027..90.535 rows=323,188 loops=1)

  • Buffers: shared hit=4241
13. 0.016 0.033 ↑ 1.0 34 1

Hash (cost=2.99..2.99 rows=34 width=4) (actual time=0.033..0.033 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=3
14. 0.017 0.017 ↑ 1.0 34 1

Index Scan using "IX_compliance_process_functional_area_id" on compliance_process cp (cost=0.15..2.99 rows=34 width=4) (actual time=0.010..0.017 rows=34 loops=1)

  • Index Cond: (functional_area_id = 2)
  • Buffers: shared hit=3
15. 969.555 969.555 ↑ 1.0 1 323,185

Index Scan using "PK_priority_cache" on priority_cache pc (cost=0.43..0.46 rows=1 width=21) (actual time=0.002..0.003 rows=1 loops=323,185)

  • Index Cond: (priority_cache_id = w.priority_cache_id)
  • Buffers: shared hit=1294534
16. 969.555 969.555 ↑ 1.0 1 323,185

Index Scan using "IX_part_entity_part_id_relationship_type_id" on part_entity pe (cost=0.43..0.58 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=323,185)

  • Index Cond: ((part_id = pel.part_id) AND (relationship_type_id = 1))
  • Buffers: shared hit=1295160
17. 19.902 155.252 ↓ 1.2 64,206 1

Hash (cost=3,684.39..3,684.39 rows=53,343 width=16) (actual time=155.252..155.252 rows=64,206 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3522kB
  • Buffers: shared hit=81544
18. 31.788 135.350 ↓ 1.2 64,206 1

Merge Join (cost=0.64..3,684.39 rows=53,343 width=16) (actual time=0.052..135.350 rows=64,206 loops=1)

  • Merge Cond: (el.entity_id = ae.entity_id)
  • Buffers: shared hit=81544
19. 18.295 18.295 ↑ 1.0 34,166 1

Index Scan using "IX_entity_location_entity_id_location_id_entity_location_type_~" on entity_location el (cost=0.29..860.09 rows=34,258 width=12) (actual time=0.007..18.295 rows=34,166 loops=1)

  • Buffers: shared hit=20285
20. 14.843 85.267 ↓ 1.7 64,206 1

Materialize (cost=0.29..2,035.72 rows=37,854 width=8) (actual time=0.030..85.267 rows=64,206 loops=1)

  • Buffers: shared hit=61259
21. 70.424 70.424 ↓ 1.0 37,978 1

Index Scan using "IX_account_entity_entity_id" on account_entity ae (cost=0.29..1,941.08 rows=37,854 width=8) (actual time=0.023..70.424 rows=37,978 loops=1)

  • Filter: (recipient_type = ANY ('{To,CC}'::citext[]))
  • Rows Removed by Filter: 31204
  • Buffers: shared hit=61259