explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 63qI

Settings
# exclusive inclusive rows x rows loops node
1. 882.263 6,733.119 ↑ 1.3 49,999 1

HashAggregate (cost=194,152.73..194,796.50 rows=64,377 width=88) (actual time=6,703.224..6,733.119 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
2. 298.393 5,850.856 ↓ 11.7 751,955 1

Hash Join (cost=161,262.19..192,704.25 rows=64,377 width=61) (actual time=4,038.394..5,850.856 rows=751,955 loops=1)

  • Hash Cond: (pel.entity_location_id = el.entity_location_id)
3. 210.717 5,388.916 ↓ 7.8 323,185 1

Nested Loop (cost=156,911.01..187,502.58 rows=41,344 width=49) (actual time=3,874.788..5,388.916 rows=323,185 loops=1)

4. 270.274 4,208.644 ↓ 8.0 323,185 1

Hash Join (cost=156,910.58..163,838.47 rows=40,399 width=53) (actual time=3,874.770..4,208.644 rows=323,185 loops=1)

  • Hash Cond: (pel.part_entity_location_id = pc.part_entity_location_id)
5. 64.307 64.307 ↑ 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.025..64.307 rows=326,773 loops=1)

6. 180.070 3,874.063 ↓ 8.0 323,185 1

Hash (cost=156,405.60..156,405.60 rows=40,399 width=49) (actual time=3,874.063..3,874.063 rows=323,185 loops=1)

  • Buckets: 524288 (originally 65536) Batches: 1 (originally 1) Memory Usage: 31315kB
7. 376.282 3,693.993 ↓ 8.0 323,185 1

Nested Loop (cost=11,516.57..156,405.60 rows=40,399 width=49) (actual time=189.849..3,693.993 rows=323,185 loops=1)

8. 130.948 2,671.341 ↓ 8.0 323,185 1

Hash Join (cost=11,516.14..137,511.49 rows=40,399 width=44) (actual time=189.832..2,671.341 rows=323,185 loops=1)

  • Hash Cond: (w.compliance_process_id = cp.compliance_process_id)
9. 1,556.077 2,540.354 ↑ 1.0 323,188 1

Hash Join (cost=11,512.73..135,892.14 rows=323,188 width=44) (actual time=189.782..2,540.354 rows=323,188 loops=1)

  • Hash Cond: (w.work_id = o.work_id)
10. 809.032 809.032 ↑ 1.0 4,791,959 1

Seq Scan on work w (cost=0.00..103,177.66 rows=4,791,966 width=36) (actual time=0.006..809.032 rows=4,791,959 loops=1)

11. 87.154 175.245 ↑ 1.0 323,188 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 19246kB
12. 88.091 88.091 ↑ 1.0 323,188 1

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

13. 0.019 0.039 ↑ 1.0 34 1

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

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

  • Index Cond: (functional_area_id = 2)
15. 646.370 646.370 ↑ 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.002 rows=1 loops=323,185)

  • Index Cond: (priority_cache_id = w.priority_cache_id)
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))
17. 21.186 163.547 ↓ 1.2 64,206 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3522kB
18. 35.680 142.361 ↓ 1.2 64,206 1

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

  • Merge Cond: (el.entity_id = ae.entity_id)
19. 19.306 19.306 ↑ 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.010..19.306 rows=34,166 loops=1)

20. 15.363 87.375 ↓ 1.7 64,206 1

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

21. 72.012 72.012 ↓ 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..72.012 rows=37,978 loops=1)

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