explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sNen

Settings
# exclusive inclusive rows x rows loops node
1. 3,830.248 62,091.560 ↓ 14.0 80,590 1

HashAggregate (cost=405,813.92..405,871.66 rows=5,774 width=61) (actual time=61,903.942..62,091.560 rows=80,590 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. 3,335.607 58,261.312 ↓ 298.5 1,723,760 1

Nested Loop (cost=75,911.64..405,655.14 rows=5,774 width=61) (actual time=4,518.871..58,261.312 rows=1,723,760 loops=1)

3. 1,534.120 46,788.108 ↓ 160.4 625,969 1

Nested Loop (cost=75,911.35..404,094.33 rows=3,902 width=57) (actual time=4,518.844..46,788.108 rows=625,969 loops=1)

4. 1,815.390 41,498.174 ↓ 164.9 625,969 1

Nested Loop (cost=75,910.92..402,086.27 rows=3,797 width=61) (actual time=4,518.822..41,498.174 rows=625,969 loops=1)

5. 1,100.443 36,552.939 ↓ 164.9 625,969 1

Hash Join (cost=75,910.63..400,877.25 rows=3,797 width=53) (actual time=4,518.772..36,552.939 rows=625,969 loops=1)

  • Hash Cond: (w.compliance_process_id = cp.compliance_process_id)
6. 1,696.181 35,452.440 ↓ 20.6 627,238 1

Hash Join (cost=75,907.20..400,721.92 rows=30,379 width=53) (actual time=4,518.706..35,452.440 rows=627,238 loops=1)

  • Hash Cond: (w.priority_cache_id = pc.priority_cache_id)
7. 9,136.562 30,753.205 ↓ 1.0 627,238 1

Merge Join (cost=28,400.23..350,559.03 rows=627,236 width=44) (actual time=1,514.430..30,753.205 rows=627,238 loops=1)

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

Index Scan using "PK_work" on work w (cost=0.43..415,967.41 rows=12,181,168 width=36) (actual time=0.013..20,612.034 rows=12,018,707 loops=1)

9. 1,004.609 1,004.609 ↓ 1.0 627,238 1

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

10. 511.407 3,003.054 ↓ 1.0 532,481 1

Hash (cost=41,109.93..41,109.93 rows=511,763 width=25) (actual time=3,003.054..3,003.054 rows=532,481 loops=1)

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

Merge Join (cost=5.90..41,109.93 rows=511,763 width=25) (actual time=0.046..2,491.647 rows=532,481 loops=1)

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

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

13. 575.404 575.404 ↓ 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.016..575.404 rows=610,050 loops=1)

14. 0.020 0.056 ↑ 1.0 34 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.036 0.036 ↑ 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.012..0.036 rows=34 loops=1)

  • Index Cond: (functional_area_id = 2)
16. 3,129.845 3,129.845 ↑ 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.004..0.005 rows=1 loops=625,969)

  • Index Cond: (entity_location_id = pel.entity_location_id)
17. 3,755.814 3,755.814 ↑ 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.005..0.006 rows=1 loops=625,969)

  • Index Cond: ((part_id = pel.part_id) AND (relationship_type_id = 1))
  • Heap Fetches: 8736
18. 8,137.597 8,137.597 ↓ 1.5 3 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.006..0.013 rows=3 loops=625,969)

  • Index Cond: (entity_id = el.entity_id)
  • Filter: (recipient_type = ANY ('{To,CC}'::citext[]))
  • Rows Removed by Filter: 2