explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UfYK

Settings
# exclusive inclusive rows x rows loops node
1. 75.445 2,305.688 ↓ 2.6 30,828 1

Hash Join (cost=2,896.11..226,846.16 rows=11,894 width=45) (actual time=46.529..2,305.688 rows=30,828 loops=1)

  • Hash Cond: (b.security_id = its_universe_component.m_security_id)
2.          

CTE timestamps

3. 0.069 0.069 ↑ 1.0 28 1

Values Scan on "*VALUES*" (cost=0.00..0.35 rows=28 width=8) (actual time=0.001..0.069 rows=28 loops=1)

4. 74.055 2,211.152 ↓ 57.7 685,860 1

Nested Loop (cost=0.72..223,919.53 rows=11,894 width=45) (actual time=0.042..2,211.152 rows=685,860 loops=1)

5. 0.016 0.016 ↑ 1.0 1 1

Index Scan using m_data_field_pkey on m_data_field c (cost=0.14..0.36 rows=1 width=25) (actual time=0.013..0.016 rows=1 loops=1)

  • Index Cond: (id = 153)
6. 1,348.758 2,137.081 ↓ 57.7 685,860 1

Nested Loop (cost=0.57..223,800.23 rows=11,894 width=28) (actual time=0.027..2,137.081 rows=685,860 loops=1)

  • Join Filter: (timestamps.ts <@ b.from_to)
  • Rows Removed by Join Filter: 10,973,760
7. 0.123 0.123 ↑ 1.0 28 1

CTE Scan on timestamps (cost=0.00..0.56 rows=28 width=8) (actual time=0.002..0.123 rows=28 loops=1)

8. 689.827 788.200 ↑ 1.0 416,415 28

Materialize (cost=0.57..46,445.67 rows=424,800 width=41) (actual time=0.001..28.150 rows=416,415 loops=28)

9. 98.373 98.373 ↑ 1.0 416,415 1

Index Only Scan using ft_security_data_idx_id_covering on ft_security_data b (cost=0.57..44,321.67 rows=424,800 width=41) (actual time=0.013..98.373 rows=416,415 loops=1)

  • Index Cond: (m_data_field_id = 153)
  • Heap Fetches: 416,415
10. 0.188 19.091 ↑ 20.6 1,101 1

Hash (cost=2,611.04..2,611.04 rows=22,720 width=8) (actual time=19.091..19.091 rows=1,101 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 300kB
11. 10.682 18.903 ↑ 20.6 1,101 1

HashAggregate (cost=2,156.64..2,383.84 rows=22,720 width=8) (actual time=18.687..18.903 rows=1,101 loops=1)

  • Group Key: its_universe_component.m_security_id
12. 8.221 8.221 ↑ 1.0 95,577 1

Index Only Scan using its_universe_component_idx_universe_id_covering on its_universe_component (cost=0.44..1,907.12 rows=99,810 width=8) (actual time=0.012..8.221 rows=95,577 loops=1)

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