explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kJYU

Settings
# exclusive inclusive rows x rows loops node
1. 5.219 4,381.740 ↑ 1.1 22,402 1

Unique (cost=360,605.78..360,807.09 rows=24,495 width=77) (actual time=4,373.603..4,381.740 rows=22,402 loops=1)

2. 19.237 4,376.521 ↑ 1.2 34,951 1

Sort (cost=360,605.78..360,706.43 rows=40,262 width=77) (actual time=4,373.601..4,376.521 rows=34,951 loops=1)

  • Sort Key: a.id, g.valid_since DESC
  • Sort Method: quicksort Memory: 6,452kB
3. 10.887 4,357.284 ↑ 1.2 34,951 1

Hash Join (cost=347,761.24..357,526.31 rows=40,262 width=77) (actual time=4,270.773..4,357.284 rows=34,951 loops=1)

  • Hash Cond: (a.id = its_universe_component.m_security_id)
4. 4.782 102.754 ↑ 1.1 26,505 1

Hash Left Join (cost=1,428.61..10,644.78 rows=28,695 width=58) (actual time=27.072..102.754 rows=26,505 loops=1)

  • Hash Cond: (e.classification_id = f.id)
5. 4.465 97.774 ↑ 1.0 24,495 1

Hash Join (cost=1,412.36..10,215.26 rows=25,263 width=44) (actual time=26.868..97.774 rows=24,495 loops=1)

  • Hash Cond: (b.incorporation_country_id = c.id)
6. 7.479 93.244 ↑ 1.0 24,495 1

Hash Join (cost=1,406.89..10,141.84 rows=25,263 width=38) (actual time=26.798..93.244 rows=24,495 loops=1)

  • Hash Cond: (a.company_id = b.id)
7. 43.577 73.877 ↑ 1.0 24,495 1

Hash Right Join (cost=721.22..9,389.84 rows=25,263 width=42) (actual time=14.879..73.877 rows=24,495 loops=1)

  • Hash Cond: (e.company_id = a.company_id)
  • Filter: ((e.classification_type_id = 1) OR ((e.classification_type_id = 1) IS NULL))
  • Rows Removed by Filter: 171,465
8. 15.465 15.465 ↑ 1.0 189,520 1

Seq Scan on i_company_classification e (cost=0.00..3,475.80 rows=189,580 width=20) (actual time=0.009..15.465 rows=189,520 loops=1)

9. 4.147 14.835 ↑ 1.0 24,495 1

Hash (cost=415.03..415.03 rows=24,495 width=34) (actual time=14.835..14.835 rows=24,495 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,921kB
10. 10.688 10.688 ↑ 1.0 24,495 1

Index Scan using m_security_pkey on m_security a (cost=0.29..415.03 rows=24,495 width=34) (actual time=0.010..10.688 rows=24,495 loops=1)

11. 2.902 11.888 ↑ 1.0 23,690 1

Hash (cost=389.54..389.54 rows=23,690 width=12) (actual time=11.888..11.888 rows=23,690 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,274kB
12. 8.986 8.986 ↑ 1.0 23,690 1

Index Scan using m_company_pkey on m_company b (cost=0.29..389.54 rows=23,690 width=12) (actual time=0.004..8.986 rows=23,690 loops=1)

13. 0.025 0.065 ↓ 1.0 173 1

Hash (cost=3.32..3.32 rows=172 width=14) (actual time=0.065..0.065 rows=173 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
14. 0.040 0.040 ↓ 1.0 173 1

Index Scan using m_country_pkey on m_country c (cost=0.14..3.32 rows=172 width=14) (actual time=0.009..0.040 rows=173 loops=1)

15. 0.067 0.198 ↑ 1.1 487 1

Hash (cost=9.79..9.79 rows=517 width=26) (actual time=0.197..0.198 rows=487 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
16. 0.131 0.131 ↑ 1.1 487 1

Index Scan using m_classification_pkey on m_classification f (cost=0.28..9.79 rows=517 width=26) (actual time=0.009..0.131 rows=487 loops=1)

17. 5.278 4,243.643 ↑ 1.1 32,382 1

Hash (cost=345,900.02..345,900.02 rows=34,609 width=35) (actual time=4,243.643..4,243.643 rows=32,382 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,789kB
18. 8.233 4,238.365 ↑ 1.1 32,382 1

Hash Join (cost=343,299.02..345,900.02 rows=34,609 width=35) (actual time=4,220.984..4,238.365 rows=32,382 loops=1)

  • Hash Cond: (g.security_id = its_universe_component.m_security_id)
19. 9.186 9.186 ↑ 1.0 36,190 1

Seq Scan on i_security_reference g (cost=0.00..2,504.96 rows=36,574 width=27) (actual time=0.004..9.186 rows=36,190 loops=1)

  • Filter: (identifier_id = 1)
  • Rows Removed by Filter: 73,487
20. 2.714 4,220.946 ↑ 1.0 22,410 1

Hash (cost=343,011.30..343,011.30 rows=23,018 width=8) (actual time=4,220.946..4,220.946 rows=22,410 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,132kB
21. 2,542.164 4,218.232 ↑ 1.0 22,410 1

HashAggregate (cost=342,550.94..342,781.12 rows=23,018 width=8) (actual time=4,215.302..4,218.232 rows=22,410 loops=1)

  • Group Key: its_universe_component.m_security_id
22. 1,676.068 1,676.068 ↑ 1.0 15,636,110 1

Index Only Scan using its_universe_component_idx_universe_id_covering on its_universe_component (cost=0.44..301,552.12 rows=16,399,528 width=8) (actual time=0.017..1,676.068 rows=15,636,110 loops=1)

  • Index Cond: (m_universe_id = 179)
  • Heap Fetches: 0