explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 499b

Settings
# exclusive inclusive rows x rows loops node
1. 497.682 17,620.494 ↓ 2.6 201,483 1

Hash Join (cost=2,920.43..1,289,821.78 rows=77,478 width=45) (actual time=45.493..17,620.494 rows=201,483 loops=1)

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

CTE timestamps

3. 0.206 0.206 ↑ 1.0 61 1

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

4. 767.774 17,102.980 ↓ 57.9 4,482,585 1

Hash Join (cost=6.68..1,286,704.62 rows=77,478 width=45) (actual time=0.113..17,102.980 rows=4,482,585 loops=1)

  • Hash Cond: (ft_security_data.m_data_field_id = m_data_field.id)
5. 8,764.744 16,335.127 ↓ 57.9 4,482,585 1

Nested Loop (cost=0.57..1,286,490.69 rows=77,478 width=28) (actual time=0.029..16,335.127 rows=4,482,585 loops=1)

  • Join Filter: (timestamps.ts <@ ft_security_data.from_to)
  • Rows Removed by Join Filter: 71,721,360
6. 0.344 0.344 ↑ 1.0 61 1

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

7. 7,268.791 7,570.039 ↑ 1.0 1,249,245 61

Materialize (cost=0.57..127,492.20 rows=1,270,134 width=41) (actual time=0.003..124.099 rows=1,249,245 loops=61)

8. 301.248 301.248 ↑ 1.0 1,249,245 1

Index Only Scan using ft_security_data_idx_id_covering on ft_security_data (cost=0.57..121,141.53 rows=1,270,134 width=41) (actual time=0.018..301.248 rows=1,249,245 loops=1)

  • Index Cond: (m_data_field_id = ANY ('{152,153,158}'::integer[]))
  • Heap Fetches: 1,249,245
9. 0.027 0.079 ↑ 1.0 194 1

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

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

  • Heap Fetches: 141
11. 0.186 19.832 ↑ 20.6 1,101 1

Hash (cost=2,628.92..2,628.92 rows=22,725 width=8) (actual time=19.832..19.832 rows=1,101 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 300kB
12. 11.111 19.646 ↑ 20.6 1,101 1

HashAggregate (cost=2,174.42..2,401.67 rows=22,725 width=8) (actual time=19.433..19.646 rows=1,101 loops=1)

  • Group Key: its_universe_component.m_security_id
13. 8.535 8.535 ↑ 1.0 95,577 1

Index Only Scan using its_universe_component_idx_universe_id_covering on its_universe_component (cost=0.44..1,923.82 rows=100,243 width=8) (actual time=0.011..8.535 rows=95,577 loops=1)

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