explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5rsI

Settings
# exclusive inclusive rows x rows loops node
1. 2,038.796 42,438.759 ↓ 14.2 68,639 1

HashAggregate (cost=405,598.57..405,647.00 rows=4,843 width=61) (actual time=42,361.235..42,438.759 rows=68,639 loops=1)

  • Group Key: el.entity_location_id, el.location_id, el.entity_id, pe.entity_id, w.compliance_process_id, ae.account_id, o.origin_type_id
2. 1,766.116 40,399.963 ↓ 256.9 1,244,213 1

Nested Loop (cost=75,885.86..405,465.38 rows=4,843 width=61) (actual time=3,854.348..40,399.963 rows=1,244,213 loops=1)

3. 1,151.662 33,626.095 ↓ 160.4 625,969 1

Nested Loop (cost=75,885.57..403,904.58 rows=3,902 width=57) (actual time=3,854.269..33,626.095 rows=625,969 loops=1)

4. 1,457.794 29,970.557 ↓ 164.9 625,969 1

Nested Loop (cost=75,885.14..401,896.97 rows=3,797 width=61) (actual time=3,854.227..29,970.557 rows=625,969 loops=1)

5. 757.340 26,634.856 ↓ 164.9 625,969 1

Hash Join (cost=75,884.85..400,687.95 rows=3,797 width=53) (actual time=3,854.192..26,634.856 rows=625,969 loops=1)

  • Hash Cond: (w.compliance_process_id = cp.compliance_process_id)
6. 1,073.724 25,877.458 ↓ 20.6 627,238 1

Hash Join (cost=75,881.41..400,532.62 rows=30,379 width=53) (actual time=3,854.122..25,877.458 rows=627,238 loops=1)

  • Hash Cond: (w.priority_cache_id = pc.priority_cache_id)
7. 6,402.276 22,180.366 ↓ 1.0 627,238 1

Merge Join (cost=28,384.83..350,380.12 rows=627,236 width=44) (actual time=1,229.104..22,180.366 rows=627,238 loops=1)

  • Merge Cond: (w.work_id = o.work_id)
8. 14,985.524 14,985.524 ↑ 1.0 12,018,707 1

Index Scan using "PK_work" on work w (cost=0.43..415,725.41 rows=12,181,168 width=36) (actual time=0.010..14,985.524 rows=12,018,707 loops=1)

9. 792.566 792.566 ↓ 1.0 627,238 1

Index Scan using "IX_origin_work_id" on origin o (cost=0.42..20,788.05 rows=627,236 width=16) (actual time=0.008..792.566 rows=627,238 loops=1)

10. 496.742 2,623.368 ↓ 1.0 532,481 1

Hash (cost=41,099.54..41,099.54 rows=511,763 width=25) (actual time=2,623.368..2,623.368 rows=532,481 loops=1)

  • Buckets: 1048576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 41473kB
11. 940.426 2,126.626 ↓ 1.0 532,481 1

Merge Join (cost=5.90..41,099.54 rows=511,763 width=25) (actual time=0.050..2,126.626 rows=532,481 loops=1)

  • Merge Cond: (pc.part_entity_location_id = pel.part_entity_location_id)
12. 708.395 708.395 ↑ 19.8 532,482 1

Index Scan using "IX_priority_cache_part_entity_location_id" on priority_cache pc (cost=0.43..364,632.60 rows=10,566,343 width=21) (actual time=0.028..708.395 rows=532,482 loops=1)

13. 477.805 477.805 ↓ 1.0 610,050 1

Index Scan using "PK_part_entity_location" on part_entity_location pel (cost=0.42..15,539.48 rows=601,460 width=20) (actual time=0.018..477.805 rows=610,050 loops=1)

14. 0.020 0.058 ↑ 1.0 34 1

Hash (cost=3.01..3.01 rows=34 width=4) (actual time=0.058..0.058 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.038 0.038 ↑ 1.0 34 1

Index Scan using "IX_compliance_process_functional_area_id" on compliance_process cp (cost=0.15..3.01 rows=34 width=4) (actual time=0.014..0.038 rows=34 loops=1)

  • Index Cond: (functional_area_id = 2)
16. 1,877.907 1,877.907 ↑ 1.0 1 625,969

Index Scan using "PK_entity_location" on entity_location el (cost=0.29..0.31 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=625,969)

  • Index Cond: (entity_location_id = pel.entity_location_id)
17. 2,503.876 2,503.876 ↑ 1.0 1 625,969

Index Only Scan using "IX_part_entity_part_id_entity_id_relationship_type_id" on part_entity pe (cost=0.43..0.52 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=625,969)

  • Index Cond: ((part_id = pel.part_id) AND (relationship_type_id = 1))
  • Heap Fetches: 8736
18. 5,007.752 5,007.752 ↑ 1.0 2 625,969

Index Scan using "IX_account_entity_entity_id" on account_entity ae (cost=0.29..0.38 rows=2 width=8) (actual time=0.004..0.008 rows=2 loops=625,969)

  • Index Cond: (entity_id = el.entity_id)
  • Filter: (recipient_type = 'To'::citext)
  • Rows Removed by Filter: 3