explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8SIW

Settings
# exclusive inclusive rows x rows loops node
1. 5.743 32,286.518 ↑ 4.2 19,864 1

Hash Join (cost=3,930.24..17,181.92 rows=82,998 width=43) (actual time=105.943..32,286.518 rows=19,864 loops=1)

  • Hash Cond: (esg.m_data_field_id = m_data_field.id)
2.          

CTE timestamps

3. 0.023 0.023 ↑ 1.0 6 1

Values Scan on "*VALUES*" (cost=0.00..0.08 rows=6 width=8) (actual time=0.001..0.023 rows=6 loops=1)

4. 47.642 32,280.645 ↑ 4.2 19,864 1

Hash Join (cost=3,924.06..16,953.11 rows=82,998 width=26) (actual time=105.807..32,280.645 rows=19,864 loops=1)

  • Hash Cond: (esg.m_company_id = m_security.company_id)
5. 40.918 32,205.021 ↓ 1.9 168,308 1

Nested Loop (cost=0.57..11,228.48 rows=86,326 width=26) (actual time=0.398..32,205.021 rows=168,308 loops=1)

6. 0.035 0.035 ↑ 1.0 6 1

CTE Scan on timestamps (cost=0.00..0.12 rows=6 width=8) (actual time=0.002..0.035 rows=6 loops=1)

7. 32,164.068 32,164.068 ↓ 1.9 28,051 6

Index Scan using ar_ts_esg_rating_pkey on ar_ts_esg_rating esg (cost=0.57..1,727.51 rows=14,388 width=26) (actual time=0.332..5,360.678 rows=28,051 loops=6)

  • Index Cond: ((date = timestamps.ts) AND (m_data_field_id = ANY ('{64,65,66,67}'::integer[])))
8. 0.174 27.982 ↑ 20.7 1,101 1

Hash (cost=3,639.23..3,639.23 rows=22,740 width=16) (actual time=27.982..27.982 rows=1,101 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 308kB
9. 2.923 27.808 ↑ 20.7 1,101 1

Hash Join (cost=2,963.97..3,639.23 rows=22,740 width=16) (actual time=23.372..27.808 rows=1,101 loops=1)

  • Hash Cond: (m_security.id = its_universe_component.m_security_id)
10. 1.550 1.550 ↑ 1.0 24,495 1

Seq Scan on m_security (cost=0.00..610.95 rows=24,495 width=16) (actual time=0.005..1.550 rows=24,495 loops=1)

11. 0.146 23.335 ↑ 20.7 1,101 1

Hash (cost=2,679.72..2,679.72 rows=22,740 width=8) (actual time=23.335..23.335 rows=1,101 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 300kB
12. 13.232 23.189 ↑ 20.7 1,101 1

HashAggregate (cost=2,224.92..2,452.32 rows=22,740 width=8) (actual time=22.956..23.189 rows=1,101 loops=1)

  • Group Key: its_universe_component.m_security_id
13. 9.957 9.957 ↑ 1.1 95,577 1

Index Only Scan using its_universe_component_idx_universe_id_covering on its_universe_component (cost=0.44..1,971.32 rows=101,440 width=8) (actual time=0.015..9.957 rows=95,577 loops=1)

  • Index Cond: (m_universe_id = 106)
  • Heap Fetches: 0
14. 0.031 0.130 ↑ 1.0 194 1

Hash (cost=3.68..3.68 rows=194 width=25) (actual time=0.130..0.130 rows=194 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
15. 0.099 0.099 ↑ 1.0 194 1

Index Only Scan using m_data_field_idx_id_covering on m_data_field (cost=0.27..3.68 rows=194 width=25) (actual time=0.013..0.099 rows=194 loops=1)

  • Heap Fetches: 141