explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rp2l

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 7,479.726 ↓ 21,815.1 305,411 1

Nested Loop (cost=341,414.80..580,270.05 rows=14 width=45) (actual time=4,158.717..7,479.726 rows=305,411 loops=1)

2.          

CTE timestamps

3. 0.041 0.041 ↑ 1.0 14 1

Values Scan on "*VALUES*" (cost=0.00..0.18 rows=14 width=8) (actual time=0.001..0.041 rows=14 loops=1)

4. 296.584 7,219.484 ↓ 21,815.1 305,411 1

Nested Loop Semi Join (cost=341,414.48..580,267.60 rows=14 width=28) (actual time=4,158.705..7,219.484 rows=305,411 loops=1)

  • Join Filter: (a.from_to @> timestamps.ts)
  • Rows Removed by Join Filter: 1,129,962
5. 1,612.858 6,922.900 ↓ 21.6 305,411 1

Nested Loop (cost=341,414.48..575,826.31 rows=14,111 width=49) (actual time=4,158.700..6,922.900 rows=305,411 loops=1)

  • Join Filter: (a.from_to @> c.ts)
  • Rows Removed by Join Filter: 13,775,187
6. 0.066 0.066 ↑ 1.0 14 1

CTE Scan on timestamps c (cost=0.00..0.28 rows=14 width=8) (actual time=0.002..0.066 rows=14 loops=1)

7. 795.296 5,309.976 ↑ 1.0 1,005,757 14

Materialize (cost=341,414.48..366,679.73 rows=1,007,934 width=41) (actual time=297.048..379.284 rows=1,005,757 loops=14)

8. 173.624 4,514.680 ↑ 1.0 1,005,757 1

Hash Join (cost=341,414.48..361,640.06 rows=1,007,934 width=41) (actual time=4,158.672..4,514.680 rows=1,005,757 loops=1)

  • Hash Cond: (a.security_id = its_universe_component.m_security_id)
9. 182.428 182.428 ↑ 1.0 1,066,961 1

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

  • Heap Fetches: 24,993
10. 2.791 4,158.628 ↑ 1.0 22,410 1

Hash (cost=341,126.33..341,126.33 rows=23,018 width=8) (actual time=4,158.627..4,158.628 rows=22,410 loops=1)

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

HashAggregate (cost=340,665.97..340,896.15 rows=23,018 width=8) (actual time=4,152.941..4,155.837 rows=22,410 loops=1)

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

Index Only Scan using its_universe_component_idx_universe_id_covering on its_universe_component (cost=0.44..299,839.17 rows=16,330,720 width=8) (actual time=0.013..1,676.327 rows=15,636,110 loops=1)

  • Index Cond: (m_universe_id = 179)
  • Heap Fetches: 0
13. 0.000 0.000 ↑ 2.8 5 305,411

CTE Scan on timestamps (cost=0.00..0.28 rows=14 width=8) (actual time=0.000..0.000 rows=5 loops=305,411)

14. 305.411 305.411 ↑ 1.0 1 305,411

Index Scan using m_data_field_pkey on m_data_field b (cost=0.14..0.16 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=305,411)

  • Index Cond: (id = a.m_data_field_id)