explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GWgK

Settings
# exclusive inclusive rows x rows loops node
1. 169.156 13,418.946 ↓ 17.2 1,055,979 1

Hash Join (cost=338,003.39..1,283,174.55 rows=61,484 width=45) (actual time=3,533.929..13,418.946 rows=1,055,979 loops=1)

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

CTE timestamps

3. 0.212 0.212 ↑ 1.0 61 1

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

4. 6,575.656 13,249.712 ↓ 17.2 1,055,979 1

Nested Loop (cost=337,996.52..1,283,002.76 rows=61,484 width=28) (actual time=3,533.844..13,249.712 rows=1,055,979 loops=1)

  • Join Filter: (timestamps.ts <@ ft_shares_outstanding.from_to)
  • Rows Removed by Join Filter: 60,295,198
5. 0.351 0.351 ↑ 1.0 61 1

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

6. 2,824.587 6,673.705 ↑ 1.0 1,005,757 61

Materialize (cost=337,996.52..363,261.77 rows=1,007,934 width=41) (actual time=57.931..109.405 rows=1,005,757 loops=61)

7. 154.491 3,849.118 ↑ 1.0 1,005,757 1

Hash Join (cost=337,996.52..358,222.10 rows=1,007,934 width=41) (actual time=3,533.739..3,849.118 rows=1,005,757 loops=1)

  • Hash Cond: (ft_shares_outstanding.security_id = its_universe_component.m_security_id)
8. 160.940 160.940 ↑ 1.0 1,066,961 1

Index Only Scan using ft_shares_outstanding_idx_data_field on ft_shares_outstanding (cost=0.43..17,424.74 rows=1,066,961 width=41) (actual time=0.018..160.940 rows=1,066,961 loops=1)

  • Heap Fetches: 24,993
9. 2.728 3,533.687 ↑ 1.0 22,410 1

Hash (cost=337,708.37..337,708.37 rows=23,018 width=8) (actual time=3,533.687..3,533.687 rows=22,410 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,132kB
10. 2,139.051 3,530.959 ↑ 1.0 22,410 1

HashAggregate (cost=337,248.01..337,478.19 rows=23,018 width=8) (actual time=3,528.207..3,530.959 rows=22,410 loops=1)

  • Group Key: its_universe_component.m_security_id
11. 1,391.908 1,391.908 ↑ 1.0 15,636,110 1

Index Only Scan using its_universe_component_idx_universe_id_covering on its_universe_component (cost=0.44..296,733.08 rows=16,205,970 width=8) (actual time=0.011..1,391.908 rows=15,636,110 loops=1)

  • Index Cond: (m_universe_id = 179)
  • Heap Fetches: 0
12. 0.027 0.078 ↑ 1.0 194 1

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

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

  • Heap Fetches: 141