explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aFCg

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

Hash Join (cost=338,003.39..1,282,100.91 rows=61,445 width=45) (actual time=3,644.948..13,465.529 rows=1,055,979 loops=1)

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

CTE timestamps

3. 0.203 0.203 ↑ 1.0 61 1

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

4. 6,530.215 13,297.349 ↓ 17.2 1,055,979 1

Nested Loop (cost=337,996.52..1,281,929.22 rows=61,445 width=28) (actual time=3,644.846..13,297.349 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.343 0.343 ↑ 1.0 61 1

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

6. 2,800.685 6,766.791 ↑ 1.0 1,005,757 61

Materialize (cost=337,996.52..362,782.27 rows=1,007,283 width=41) (actual time=59.750..110.931 rows=1,005,757 loops=61)

7. 157.337 3,966.106 ↑ 1.0 1,005,757 1

Hash Join (cost=337,996.52..357,745.85 rows=1,007,283 width=41) (actual time=3,644.736..3,966.106 rows=1,005,757 loops=1)

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

Index Only Scan using ft_shares_outstanding_idx_data_field on ft_shares_outstanding (cost=0.43..16,950.31 rows=1,066,272 width=41) (actual time=0.009..164.076 rows=1,066,961 loops=1)

  • Heap Fetches: 24,993
9. 2.660 3,644.693 ↑ 1.0 22,410 1

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

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

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

  • Group Key: its_universe_component.m_security_id
11. 1,430.095 1,430.095 ↑ 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.013..1,430.095 rows=15,636,110 loops=1)

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

Hash (cost=3.68..3.68 rows=194 width=25) (actual time=0.095..0.095 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