explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Noz : Optimization for: plan #GPJR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.619 2,306.088 ↓ 9.9 138 1

HashAggregate (cost=63,841.72..63,841.86 rows=14 width=4) (actual time=2,306.018..2,306.088 rows=138 loops=1)

  • Group Key: ae.account_id
2. 1.170 2,305.469 ↓ 52.4 734 1

Nested Loop (cost=19,813.68..63,841.68 rows=14 width=4) (actual time=89.198..2,305.469 rows=734 loops=1)

3. 1.290 2,300.209 ↓ 74.4 818 1

Nested Loop (cost=19,813.39..63,837.28 rows=11 width=4) (actual time=89.138..2,300.209 rows=818 loops=1)

4. 0.878 2,296.465 ↓ 74.4 818 1

Hash Join (cost=19,813.10..63,833.78 rows=11 width=4) (actual time=89.126..2,296.465 rows=818 loops=1)

  • Hash Cond: (w.compliance_process_id = cp.compliance_process_id)
5. 1.566 2,295.528 ↓ 9.0 818 1

Nested Loop (cost=19,809.67..63,829.89 rows=91 width=8) (actual time=89.044..2,295.528 rows=818 loops=1)

6. 323.468 2,290.754 ↑ 1.7 802 1

Hash Join (cost=19,809.23..62,824.81 rows=1,343 width=12) (actual time=89.026..2,290.754 rows=802 loops=1)

  • Hash Cond: (pc.part_entity_id = pe.part_entity_id)
7. 851.159 1,899.568 ↓ 1.0 532,481 1

Merge Join (cost=5.90..41,088.94 rows=511,763 width=20) (actual time=0.019..1,899.568 rows=532,481 loops=1)

  • Merge Cond: (pel.part_entity_location_id = pc.part_entity_location_id)
8. 435.858 435.858 ↓ 1.0 601,462 1

Index Scan using "PK_part_entity_location" on part_entity_location pel (cost=0.42..15,539.48 rows=601,460 width=12) (actual time=0.008..435.858 rows=601,462 loops=1)

9. 612.551 612.551 ↑ 19.8 532,482 1

Index Scan using "IX_priority_cache_part_entity_location_id" on priority_cache pc (cost=0.43..364,413.70 rows=10,566,343 width=24) (actual time=0.009..612.551 rows=532,482 loops=1)

10. 25.754 67.718 ↓ 1.9 44,350 1

Hash (cost=19,514.09..19,514.09 rows=23,139 width=8) (actual time=67.718..67.718 rows=44,350 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2245kB
11. 41.964 41.964 ↓ 1.9 44,350 1

Index Scan using "IX_part_entity_entity_id_relationship_type_id" on part_entity pe (cost=0.43..19,514.09 rows=23,139 width=8) (actual time=0.036..41.964 rows=44,350 loops=1)

  • Index Cond: ((entity_id = 8686) AND (relationship_type_id = 1))
12. 3.208 3.208 ↑ 1.0 1 802

Index Scan using "IX_work_priority_cache_id" on work w (cost=0.43..0.74 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=802)

  • Index Cond: (priority_cache_id = pc.priority_cache_id)
  • Filter: (status_id = ANY ('{16,15,51,27,28,29,31,32,33,53,52,55,56,54}'::integer[]))
  • Rows Removed by Filter: 0
13. 0.025 0.059 ↑ 1.0 34 1

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

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

  • Index Cond: (functional_area_id = 2)
15. 2.454 2.454 ↑ 1.0 1 818

Index Scan using "PK_entity_location" on entity_location el (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=818)

  • Index Cond: (entity_location_id = pel.entity_location_id)
16. 4.090 4.090 ↑ 2.0 1 818

Index Scan using "IX_account_entity_entity_id" on account_entity ae (cost=0.29..0.38 rows=2 width=8) (actual time=0.003..0.005 rows=1 loops=818)

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