explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YqHw

Settings
# exclusive inclusive rows x rows loops node
1. 149.083 4,870.506 ↓ 1.1 944,536 1

Hash Join (cost=344,269.45..393,720.90 rows=864,959 width=43) (actual time=4,178.092..4,870.506 rows=944,536 loops=1)

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

CTE timestamps

3. 0.098 0.098 ↑ 1.0 61 1

Values Scan on "*VALUES*" (cost=0.00..0.76 rows=61 width=8) (actual time=0.002..0.098 rows=61 loops=1)

4. 200.881 4,721.340 ↓ 1.1 944,536 1

Hash Join (cost=344,262.59..391,393.91 rows=864,959 width=26) (actual time=4,178.003..4,721.340 rows=944,536 loops=1)

  • Hash Cond: (esg.m_company_id = m_security.company_id)
5. 83.090 343.544 ↓ 1.1 934,720 1

Nested Loop (cost=0.57..27,370.40 rows=888,953 width=26) (actual time=1.052..343.544 rows=934,720 loops=1)

6. 0.167 0.167 ↑ 1.0 61 1

CTE Scan on timestamps (cost=0.00..1.22 rows=61 width=8) (actual time=0.003..0.167 rows=61 loops=1)

7. 260.287 260.287 ↓ 1.1 15,323 61

Index Only Scan using ar_ts_esg_rating_idx_date_data_field_covering on ar_ts_esg_rating esg (cost=0.57..302.95 rows=14,573 width=26) (actual time=0.965..4.267 rows=15,323 loops=61)

  • Index Cond: ((date = timestamps.ts) AND (m_data_field_id = ANY ('{64,65,66,67}'::integer[])))
  • Heap Fetches: 0
8. 3.181 4,176.915 ↑ 1.0 22,402 1

Hash (cost=343,974.29..343,974.29 rows=23,018 width=16) (actual time=4,176.914..4,176.915 rows=22,402 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,307kB
9. 7.043 4,173.734 ↑ 1.0 22,402 1

Hash Join (cost=343,299.02..343,974.29 rows=23,018 width=16) (actual time=4,165.025..4,173.734 rows=22,402 loops=1)

  • Hash Cond: (m_security.id = its_universe_component.m_security_id)
10. 1.703 1.703 ↑ 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.703 rows=24,495 loops=1)

11. 3.015 4,164.988 ↑ 1.0 22,410 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,132kB
12. 2,515.020 4,161.973 ↑ 1.0 22,410 1

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

  • Group Key: its_universe_component.m_security_id
13. 1,646.953 1,646.953 ↑ 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,646.953 rows=15,636,110 loops=1)

  • Index Cond: (m_universe_id = 179)
  • Heap Fetches: 0
14. 0.030 0.083 ↑ 1.0 194 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
15. 0.053 0.053 ↑ 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.012..0.053 rows=194 loops=1)

  • Heap Fetches: 141