explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YAB3

Settings
# exclusive inclusive rows x rows loops node
1. 9.731 7,837.336 ↓ 18.2 22,684 1

Hash Join (cost=45,762.60..95,953.35 rows=1,244 width=51) (actual time=293.843..7,837.336 rows=22,684 loops=1)

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

CTE timestamps

3. 0.029 0.029 ↑ 1.0 61 1

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

4.          

CTE unfiltered_revision_days

5. 0.561 279.493 ↑ 2.1 1,751 1

WindowAgg (cost=44,599.58..44,672.38 rows=3,640 width=24) (actual time=278.860..279.493 rows=1,751 loops=1)

6. 0.641 278.932 ↑ 2.1 1,751 1

Sort (cost=44,599.58..44,608.68 rows=3,640 width=16) (actual time=278.852..278.932 rows=1,751 loops=1)

  • Sort Key: timestamps.ts, its_etf_component_1."timestamp" DESC
  • Sort Method: quicksort Memory: 131kB
7. 0.694 278.291 ↑ 2.1 1,751 1

Nested Loop (cost=44,215.25..44,384.28 rows=3,640 width=16) (actual time=277.676..278.291 rows=1,751 loops=1)

  • Join Filter: (its_etf_component_1."timestamp" <= timestamps.ts)
  • Rows Removed by Join Filter: 4,532
8. 0.047 0.047 ↑ 1.0 61 1

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

9. 0.411 277.550 ↑ 1.7 103 61

Materialize (cost=44,215.25..44,219.73 rows=179 width=8) (actual time=4.543..4.550 rows=103 loops=61)

10. 1.909 277.139 ↑ 1.7 103 1

HashAggregate (cost=44,215.25..44,217.04 rows=179 width=8) (actual time=277.114..277.139 rows=103 loops=1)

  • Group Key: its_etf_component_1."timestamp
11. 3.689 275.230 ↑ 1.2 17,380 1

Gather (cost=1,000.00..44,165.06 rows=20,078 width=8) (actual time=41.281..275.230 rows=17,380 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 271.541 271.541 ↑ 1.4 5,793 3 / 3

Parallel Seq Scan on its_etf_component its_etf_component_1 (cost=0.00..41,157.26 rows=8,366 width=8) (actual time=38.707..271.541 rows=5,793 loops=3)

  • Filter: (m_etf_id = 281)
  • Rows Removed by Filter: 936,815
13.          

CTE revision_days

14. 279.874 279.874 ↓ 1.9 34 1

CTE Scan on unfiltered_revision_days (cost=0.00..81.90 rows=18 width=16) (actual time=278.862..279.874 rows=34 loops=1)

  • Filter: (distance = 1)
  • Rows Removed by Filter: 1,717
15. 11.053 7,827.040 ↓ 18.2 22,684 1

Nested Loop (cost=1,001.45..51,188.87 rows=1,244 width=34) (actual time=293.241..7,827.040 rows=22,684 loops=1)

16. 2.809 328.185 ↓ 2.8 5,751 1

Nested Loop (cost=1,000.87..45,152.45 rows=2,019 width=32) (actual time=290.705..328.185 rows=5,751 loops=1)

17. 2.833 296.621 ↓ 2.8 5,751 1

Hash Join (cost=1,000.59..44,261.12 rows=2,019 width=24) (actual time=290.384..296.621 rows=5,751 loops=1)

  • Hash Cond: (its_etf_component."timestamp" = revision_days.revision_date)
18. 0.000 13.887 ↑ 1.2 17,380 1

Gather (cost=1,000.00..44,165.06 rows=20,078 width=16) (actual time=10.465..13.887 rows=17,380 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 55.570 55.570 ↑ 1.4 5,793 3 / 3

Parallel Seq Scan on its_etf_component (cost=0.00..41,157.26 rows=8,366 width=16) (actual time=8.596..55.570 rows=5,793 loops=3)

  • Filter: (m_etf_id = 281)
  • Rows Removed by Filter: 936,815
20. 0.012 279.901 ↓ 1.9 34 1

Hash (cost=0.36..0.36 rows=18 width=16) (actual time=279.901..279.901 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
21. 279.889 279.889 ↓ 1.9 34 1

CTE Scan on revision_days (cost=0.00..0.36 rows=18 width=16) (actual time=278.863..279.889 rows=34 loops=1)

22. 28.755 28.755 ↑ 1.0 1 5,751

Index Scan using m_security_id_idx_covering on m_security (cost=0.29..0.44 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=5,751)

  • Index Cond: (id = its_etf_component.m_security_id)
23. 7,487.802 7,487.802 ↓ 2.0 4 5,751

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.328..1.302 rows=4 loops=5,751)

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

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

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

  • Heap Fetches: 141