explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ajBt

Settings
# exclusive inclusive rows x rows loops node
1. 1.255 133,783.621 ↓ 1,000.0 2,000 1

Nested Loop (cost=1,002.03..4,058,219.46 rows=2 width=53) (actual time=131.885..133,783.621 rows=2,000 loops=1)

2.          

CTE timestamps

3. 0.002 0.002 ↑ 1.0 2 1

Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=8) (actual time=0.001..0.002 rows=2 loops=1)

4.          

CTE unfiltered_revision_days

5. 0.043 19.106 ↓ 113.0 113 1

WindowAgg (cost=0.65..0.67 rows=1 width=24) (actual time=19.064..19.106 rows=113 loops=1)

6. 0.037 19.063 ↓ 113.0 113 1

Sort (cost=0.65..0.65 rows=1 width=16) (actual time=19.058..19.063 rows=113 loops=1)

  • Sort Key: timestamps.ts, its_universe_component_07_1."timestamp" DESC
  • Sort Method: quicksort Memory: 30kB
7. 0.035 19.026 ↓ 113.0 113 1

Nested Loop (cost=0.55..0.64 rows=1 width=16) (actual time=18.989..19.026 rows=113 loops=1)

  • Join Filter: (its_universe_component_07_1."timestamp" <= timestamps.ts)
  • Rows Removed by Join Filter: 3
8. 6.186 18.991 ↓ 58.0 58 1

HashAggregate (cost=0.55..0.56 rows=1 width=8) (actual time=18.984..18.991 rows=58 loops=1)

  • Group Key: its_universe_component_07_1."timestamp
9. 3.786 12.805 ↓ 58,002.0 58,002 1

Append (cost=0.43..0.55 rows=1 width=8) (actual time=0.014..12.805 rows=58,002 loops=1)

10. 9.019 9.019 ↓ 58,002.0 58,002 1

Index Only Scan using its_universe_component_07_m_universe_id_timestamp_m_securit_idx on its_universe_component_07 its_universe_component_07_1 (cost=0.43..0.55 rows=1 width=8) (actual time=0.014..9.019 rows=58,002 loops=1)

  • Index Cond: (m_universe_id = 2,161)
  • Heap Fetches: 58,002
11. 0.000 0.000 ↑ 1.0 2 58

CTE Scan on timestamps (cost=0.00..0.04 rows=2 width=8) (actual time=0.000..0.000 rows=2 loops=58)

12.          

CTE revision_days

13. 19.131 19.131 ↓ 2.0 2 1

CTE Scan on unfiltered_revision_days (cost=0.00..0.02 rows=1 width=16) (actual time=19.066..19.131 rows=2 loops=1)

  • Filter: (distance = 1)
  • Rows Removed by Filter: 111
14. 0.009 0.009 ↑ 1.0 1 1

Index Scan using m_data_field_pkey on m_data_field (cost=0.15..0.36 rows=1 width=25) (actual time=0.007..0.009 rows=1 loops=1)

  • Index Cond: (id = 182)
15. 7,674.437 133,782.357 ↓ 1,000.0 2,000 1

Hash Join (cost=1,001.17..4,058,218.36 rows=2 width=36) (actual time=131.875..133,782.357 rows=2,000 loops=1)

  • Hash Cond: (its_universe_component_07."timestamp" = revision_days.revision_date)
  • Join Filter: (ft_security_data.from_to @> revision_days."timestamp")
  • Rows Removed by Join Filter: 3,009,615
16. 5,239.893 126,088.784 ↓ 61,751.7 98,061,710 1

Gather (cost=1,001.14..4,058,212.36 rows=1,588 width=49) (actual time=20.669..126,088.784 rows=98,061,710 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 5,597.179 120,848.891 ↓ 49,376.5 32,687,237 3 / 3

Parallel Hash Join (cost=1.14..4,057,053.56 rows=662 width=49) (actual time=21.706..120,848.891 rows=32,687,237 loops=3)

  • Hash Cond: (ft_security_data.security_id = its_universe_component_07.m_security_id)
18. 115,241.137 115,241.137 ↑ 1.2 12,582,874 3 / 3

Parallel Index Only Scan using ft_security_data_idx_id_covering on ft_security_data (cost=0.58..3,998,136.96 rows=15,709,179 width=41) (actual time=1.645..115,241.137 rows=12,582,874 loops=3)

  • Index Cond: (m_data_field_id = 182)
  • Heap Fetches: 37,748,621
19. 5.521 10.575 ↓ 19,334.0 19,334 3 / 3

Parallel Hash (cost=0.55..0.55 rows=1 width=16) (actual time=10.574..10.575 rows=19,334 loops=3)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,768kB
20. 1.272 5.054 ↓ 19,334.0 19,334 3 / 3

Parallel Append (cost=0.43..0.55 rows=1 width=16) (actual time=0.013..5.054 rows=19,334 loops=3)

21. 3.782 3.782 ↓ 19,334.0 19,334 3 / 3

Parallel Index Only Scan using its_universe_component_07_m_universe_id_timestamp_m_securit_idx on its_universe_component_07 (cost=0.43..0.54 rows=1 width=16) (actual time=0.013..3.782 rows=19,334 loops=3)

  • Index Cond: (m_universe_id = 2,161)
  • Heap Fetches: 58,002
22. 0.003 19.136 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=19.136..19.136 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 19.133 19.133 ↓ 2.0 2 1

CTE Scan on revision_days (cost=0.00..0.02 rows=1 width=16) (actual time=19.067..19.133 rows=2 loops=1)