explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 78hJ

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

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

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

CTE timestamps

3. 0.173 0.173 ↑ 1.0 61 1

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

4. 6,725.454 13,614.863 ↓ 17.2 1,055,979 1

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

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

6. 2,840.375 6,889.096 ↑ 1.0 1,005,757 61

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

7. 157.345 4,048.721 ↑ 1.0 1,005,757 1

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

  • Hash Cond: (ft_shares_outstanding.security_id = its_universe_component.m_security_id)
8. 163.889 163.889 ↓ 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..163.889 rows=1,066,961 loops=1)

  • Heap Fetches: 24,993
9. 2.767 3,727.487 ↑ 1.0 22,410 1

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

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

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

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

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

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

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

  • Heap Fetches: 141