explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RQ1w

Settings
# exclusive inclusive rows x rows loops node
1. 29.995 7,974.509 ↑ 1.2 82,162 1

Hash Join (cost=343,306.45..352,317.02 rows=100,155 width=46) (actual time=4,191.538..7,974.509 rows=82,162 loops=1)

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

CTE timestamps

3. 0.137 0.137 ↑ 1.0 61 1

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

4. 20.291 3,754.154 ↑ 1.2 83,153 1

Hash Join (cost=6.66..8,754.29 rows=100,155 width=46) (actual time=1.147..3,754.154 rows=83,153 loops=1)

  • Hash Cond: (ts_book_value.m_data_field_id = m_data_field.id)
5. 13.843 3,733.782 ↑ 1.2 83,153 1

Nested Loop (cost=0.56..8,479.53 rows=100,155 width=29) (actual time=1.057..3,733.782 rows=83,153 loops=1)

6. 0.220 0.220 ↑ 1.0 61 1

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

7. 3,719.719 3,719.719 ↑ 1.2 1,363 61

Index Scan using ts_book_value_pkey on ts_book_value (cost=0.56..122.57 rows=1,642 width=29) (actual time=0.657..60.979 rows=1,363 loops=61)

  • Index Cond: ("timestamp" = timestamps.ts)
8. 0.029 0.081 ↑ 1.0 194 1

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

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

  • Heap Fetches: 141
10. 3.115 4,190.360 ↑ 1.0 22,410 1

Hash (cost=343,011.30..343,011.30 rows=23,018 width=8) (actual time=4,190.359..4,190.360 rows=22,410 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,132kB
11. 2,511.821 4,187.245 ↑ 1.0 22,410 1

HashAggregate (cost=342,550.94..342,781.12 rows=23,018 width=8) (actual time=4,184.036..4,187.245 rows=22,410 loops=1)

  • Group Key: its_universe_component.m_security_id
12. 1,675.424 1,675.424 ↑ 1.0 15,636,110 1

Index Only Scan using its_universe_component_idx_universe_id_covering on its_universe_component (cost=0.44..301,552.12 rows=16,399,528 width=8) (actual time=0.015..1,675.424 rows=15,636,110 loops=1)

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