explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MU3J

Settings
# exclusive inclusive rows x rows loops node
1. 51.577 34,235.513 ↓ 21.3 127,832 1

Hash Join (cost=8,961.13..40,429.75 rows=5,992 width=51) (actual time=32.206..34,235.513 rows=127,832 loops=1)

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

CTE timestamps

3. 0.018 0.018 ↑ 1.0 61 1

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

4.          

CTE unfiltered_revision_days

5. 1.490 22.674 ↑ 8.1 4,461 1

WindowAgg (cost=6,495.34..7,217.58 rows=36,112 width=24) (actual time=20.985..22.674 rows=4,461 loops=1)

6. 1.489 21.184 ↑ 8.1 4,461 1

Sort (cost=6,495.34..6,585.62 rows=36,112 width=16) (actual time=20.980..21.184 rows=4,461 loops=1)

  • Sort Key: timestamps.ts, its_universe_component_1."timestamp" DESC
  • Sort Method: quicksort Memory: 402kB
7. 0.814 19.695 ↑ 8.1 4,461 1

Nested Loop (cost=2,095.41..3,761.63 rows=36,112 width=16) (actual time=18.527..19.695 rows=4,461 loops=1)

  • Join Filter: (its_universe_component_1."timestamp" <= timestamps.ts)
  • Rows Removed by Join Filter: 5,299
8. 0.032 0.032 ↑ 1.0 61 1

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

9. 0.439 18.849 ↑ 11.1 160 61

Materialize (cost=2,095.41..2,139.81 rows=1,776 width=8) (actual time=0.301..0.309 rows=160 loops=61)

10. 9.881 18.410 ↑ 11.1 160 1

HashAggregate (cost=2,095.41..2,113.17 rows=1,776 width=8) (actual time=18.386..18.410 rows=160 loops=1)

  • Group Key: its_universe_component_1."timestamp
11. 8.529 8.529 ↓ 1.0 95,577 1

Index Only Scan using its_universe_component_idx_universe_covering_2 on its_universe_component its_universe_component_1 (cost=0.56..1,856.53 rows=95,551 width=8) (actual time=0.008..8.529 rows=95,577 loops=1)

  • Index Cond: (m_universe_id = 106)
  • Heap Fetches: 0
12.          

CTE revision_days

13. 23.737 23.737 ↑ 3.2 56 1

CTE Scan on unfiltered_revision_days (cost=0.00..812.52 rows=181 width=16) (actual time=20.987..23.737 rows=56 loops=1)

  • Filter: (distance = 1)
  • Rows Removed by Filter: 4,405
14. 84.513 34,183.830 ↓ 21.3 127,832 1

Nested Loop (cost=924.16..32,376.71 rows=5,992 width=34) (actual time=32.088..34,183.830 rows=127,832 loops=1)

15. 19.045 78.633 ↓ 3.4 33,452 1

Hash Join (cost=923.58..3,261.96 rows=9,738 width=32) (actual time=30.502..78.633 rows=33,452 loops=1)

  • Hash Cond: (its_universe_component.m_security_id = m_security.id)
16. 14.832 53.047 ↓ 3.4 33,452 1

Hash Join (cost=6.45..2,319.26 rows=9,738 width=24) (actual time=23.796..53.047 rows=33,452 loops=1)

  • Hash Cond: (its_universe_component."timestamp" = revision_days.revision_date)
17. 14.449 14.449 ↓ 1.0 95,577 1

Index Only Scan using its_universe_component_idx_universe_covering_2 on its_universe_component (cost=0.56..1,857.68 rows=95,551 width=16) (actual time=0.023..14.449 rows=95,577 loops=1)

  • Index Cond: (m_universe_id = 106)
  • Heap Fetches: 0
18. 0.011 23.766 ↑ 3.2 56 1

Hash (cost=3.62..3.62 rows=181 width=16) (actual time=23.766..23.766 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 23.755 23.755 ↑ 3.2 56 1

CTE Scan on revision_days (cost=0.00..3.62 rows=181 width=16) (actual time=20.988..23.755 rows=56 loops=1)

20. 3.230 6.541 ↑ 1.0 24,495 1

Hash (cost=610.95..610.95 rows=24,495 width=16) (actual time=6.541..6.541 rows=24,495 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,405kB
21. 3.311 3.311 ↑ 1.0 24,495 1

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

22. 34,020.684 34,020.684 ↓ 2.0 4 33,452

Index Scan using ar_ts_esg_rating_pkey on ar_ts_esg_rating esg (cost=0.57..2.97 rows=2 width=26) (actual time=0.281..1.017 rows=4 loops=33,452)

  • Index Cond: ((date = revision_days."timestamp") AND (m_data_field_id = ANY ('{63,64,65,66}'::integer[])) AND (m_company_id = m_security.company_id))
23. 0.031 0.106 ↑ 1.0 194 1

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

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

  • Heap Fetches: 141