explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5pCy

Settings
# exclusive inclusive rows x rows loops node
1. 114.340 22,364.811 ↑ 1.4 718,884 1

Hash Join (cost=3,083,754.49..3,239,319.66 rows=980,753 width=51) (actual time=18,969.217..22,364.811 rows=718,884 loops=1)

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

CTE timestamps

3. 0.026 0.026 ↑ 1.0 61 1

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

4.          

CTE unfiltered_revision_days

5. 10.164 3,165.755 ↑ 1.2 30,226 1

WindowAgg (cost=332,767.75..333,489.99 rows=36,112 width=24) (actual time=3,153.517..3,165.755 rows=30,226 loops=1)

6. 12.787 3,155.591 ↑ 1.2 30,226 1

Sort (cost=332,767.75..332,858.03 rows=36,112 width=16) (actual time=3,153.507..3,155.591 rows=30,226 loops=1)

  • Sort Key: timestamps.ts, its_universe_component_1."timestamp" DESC
  • Sort Method: quicksort Memory: 2,185kB
7. 6.874 3,142.804 ↑ 1.2 30,226 1

Nested Loop (cost=328,367.82..330,034.04 rows=36,112 width=16) (actual time=3,134.798..3,142.804 rows=30,226 loops=1)

  • Join Filter: (its_universe_component_1."timestamp" <= timestamps.ts)
  • Rows Removed by Join Filter: 57,980
8. 0.042 0.042 ↑ 1.0 61 1

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

9. 3.925 3,135.888 ↑ 1.2 1,446 61

Materialize (cost=328,367.82..328,412.22 rows=1,776 width=8) (actual time=51.341..51.408 rows=1,446 loops=61)

10. 1,733.700 3,131.963 ↑ 1.2 1,446 1

HashAggregate (cost=328,367.82..328,385.58 rows=1,776 width=8) (actual time=3,131.779..3,131.963 rows=1,446 loops=1)

  • Group Key: its_universe_component_1."timestamp
11. 1,398.263 1,398.263 ↑ 1.0 15,636,110 1

Index Only Scan using its_universe_component_idx_universe_covering_2 on its_universe_component its_universe_component_1 (cost=0.56..289,269.25 rows=15,639,427 width=8) (actual time=0.024..1,398.263 rows=15,636,110 loops=1)

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

CTE revision_days

13. 3,171.840 3,171.840 ↑ 4.2 43 1

CTE Scan on unfiltered_revision_days (cost=0.00..812.52 rows=181 width=16) (actual time=3,153.520..3,171.840 rows=43 loops=1)

  • Filter: (distance = 1)
  • Rows Removed by Filter: 30,183
14. 1,315.497 22,250.395 ↑ 1.4 718,884 1

Merge Join (cost=2,749,445.11..2,902,379.57 rows=980,753 width=34) (actual time=18,969.133..22,250.395 rows=718,884 loops=1)

  • Merge Cond: ((m_security.company_id = esg.m_company_id) AND (its_universe_component."timestamp" = revision_days.revision_date))
15. 11,797.698 16,787.406 ↑ 1.0 15,632,859 1

Sort (cost=2,354,933.64..2,394,032.20 rows=15,639,427 width=24) (actual time=15,050.556..16,787.406 rows=15,632,859 loops=1)

  • Sort Key: m_security.company_id, its_universe_component."timestamp
  • Sort Method: external merge Disk: 520,184kB
16. 3,471.562 4,989.708 ↑ 1.0 15,632,898 1

Hash Join (cost=917.70..344,086.09 rows=15,639,427 width=24) (actual time=6.216..4,989.708 rows=15,632,898 loops=1)

  • Hash Cond: (its_universe_component.m_security_id = m_security.id)
17. 1,511.981 1,511.981 ↑ 1.0 15,636,110 1

Index Only Scan using its_universe_component_idx_universe_covering_2 on its_universe_component (cost=0.56..302,108.36 rows=15,639,427 width=16) (actual time=0.020..1,511.981 rows=15,636,110 loops=1)

  • Index Cond: (m_universe_id = 179)
  • Heap Fetches: 0
18. 2.896 6.165 ↑ 1.0 24,495 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,405kB
19. 3.269 3.269 ↑ 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.269 rows=24,495 loops=1)

20. 115.284 4,147.492 ↑ 3.3 809,440 1

Materialize (cost=394,007.21..407,180.74 rows=2,634,707 width=34) (actual time=3,918.521..4,147.492 rows=809,440 loops=1)

21. 557.408 4,032.208 ↑ 3.3 797,132 1

Sort (cost=394,007.21..400,593.98 rows=2,634,707 width=34) (actual time=3,918.516..4,032.208 rows=797,132 loops=1)

  • Sort Key: esg.m_company_id, revision_days.revision_date
  • Sort Method: quicksort Memory: 85,353kB
22. 73.133 3,474.800 ↑ 3.3 797,132 1

Nested Loop (cost=0.57..81,120.90 rows=2,634,707 width=34) (actual time=3,156.075..3,474.800 rows=797,132 loops=1)

23. 3,171.875 3,171.875 ↑ 4.2 43 1

CTE Scan on revision_days (cost=0.00..3.62 rows=181 width=16) (actual time=3,153.521..3,171.875 rows=43 loops=1)

24. 229.792 229.792 ↓ 1.3 18,538 43

Index Only Scan using ar_ts_esg_rating_idx_date_data_field_covering on ar_ts_esg_rating esg (cost=0.57..302.60 rows=14,556 width=26) (actual time=1.301..5.344 rows=18,538 loops=43)

  • Index Cond: ((date = revision_days."timestamp") AND (m_data_field_id = ANY ('{63,64,65,66}'::integer[])))
  • Heap Fetches: 0
25. 0.028 0.076 ↑ 1.0 194 1

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

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

  • Heap Fetches: 141