explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HAzn

Settings
# exclusive inclusive rows x rows loops node
1. 0.108 6,868.722 ↓ 10.0 10 1

Limit (cost=1,081.10..398,924.59 rows=1 width=40) (actual time=6,834.084..6,868.722 rows=10 loops=1)

2. 5,481.835 6,868.614 ↓ 10.0 10 1

Nested Loop (cost=1,081.10..398,924.59 rows=1 width=40) (actual time=6,834.083..6,868.614 rows=10 loops=1)

  • Join Filter: ((i.period && "*SELECT* 1".period) AND (i.issued_by = "*SELECT* 1".permid))
  • Rows Removed by Join Filter: 35927050
3. 0.535 35.002 ↑ 8.9 6,659 1

Append (cost=0.43..383,789.80 rows=59,431 width=27) (actual time=0.073..35.002 rows=6,659 loops=1)

4. 0.773 34.467 ↑ 4.3 6,659 1

Subquery Scan on *SELECT* 1 (cost=0.43..163,372.95 rows=28,563 width=27) (actual time=0.073..34.467 rows=6,659 loops=1)

5. 2.812 33.694 ↑ 4.3 6,659 1

Nested Loop (cost=0.43..163,087.32 rows=28,563 width=27) (actual time=0.072..33.694 rows=6,659 loops=1)

6. 0.362 0.362 ↑ 7.6 3,052 1

Seq Scan on company_leis (cost=0.00..403.56 rows=23,156 width=25) (actual time=0.013..0.362 rows=3,052 loops=1)

7. 30.520 30.520 ↓ 2.0 2 3,052

Index Scan using organizations_lei_idx on organizations (cost=0.43..7.02 rows=1 width=43) (actual time=0.008..0.010 rows=2 loops=3,052)

  • Index Cond: (lei = company_leis.lei)
8. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=0.41..220,416.85 rows=30,868 width=27) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.41..220,108.17 rows=30,868 width=27) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Seq Scan on _fallback_companies (cost=0.00..454.28 rows=29,428 width=12) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Index Scan using organizations_permid_period_excl on organizations organizations_1 (cost=0.41..7.45 rows=1 width=23) (never executed)

  • Index Cond: (permid = _fallback_companies.permid)
  • Filter: (lei IS NULL)
12. 1,244.448 1,351.777 ↓ 5,395.0 5,395 6,659

Materialize (cost=1,080.66..14,094.73 rows=1 width=27) (actual time=0.001..0.203 rows=5,395 loops=6,659)

13. 5.162 107.329 ↓ 5,396.0 5,396 1

Gather (cost=1,080.66..14,094.72 rows=1 width=27) (actual time=4.113..107.329 rows=5,396 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
14. 51.831 102.167 ↓ 1,349.0 1,349 4

Nested Loop (cost=80.66..13,094.62 rows=1 width=27) (actual time=3.875..102.167 rows=1,349 loops=4)

  • Join Filter: (s.permid = i.permid)
15. 0.775 50.298 ↓ 3.0 1,349 4

Hash Join (cost=80.38..12,878.60 rows=449 width=35) (actual time=2.521..50.298 rows=1,349 loops=4)

  • Hash Cond: ((i_1.asset_class = ac.permid) AND (s.category = ac.category))
16. 15.191 49.495 ↓ 1.2 1,349 4

Hash Join (cost=78.91..12,870.96 rows=1,117 width=50) (actual time=2.388..49.495 rows=1,349 loops=4)

  • Hash Cond: (i_1.permid = s.permid)
17. 32.629 32.629 ↑ 1.3 96,094 4

Parallel Seq Scan on instruments i_1 (cost=0.00..12,315.92 rows=123,992 width=31) (actual time=0.007..32.629 rows=96,094 loops=4)

18. 0.948 1.675 ↓ 1.0 2,708 4

Hash (cost=45.07..45.07 rows=2,707 width=19) (actual time=1.675..1.675 rows=2,708 loops=4)

  • Buckets: 4096 Batches: 1 Memory Usage: 178kB
19. 0.727 0.727 ↓ 1.0 2,708 4

Seq Scan on _securities s (cost=0.00..45.07 rows=2,707 width=19) (actual time=0.014..0.727 rows=2,708 loops=4)

20. 0.013 0.028 ↑ 1.0 19 4

Hash (cost=1.19..1.19 rows=19 width=15) (actual time=0.028..0.028 rows=19 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.015 0.015 ↑ 1.0 19 4

Seq Scan on asset_class_instrument_categories ac (cost=0.00..1.19 rows=19 width=15) (actual time=0.012..0.015 rows=19 loops=4)

22. 0.038 0.038 ↑ 1.0 1 5,396

Index Scan using instruments_permid_period_excl on instruments i (cost=0.28..0.47 rows=1 width=31) (actual time=0.038..0.038 rows=1 loops=5,396)

  • Index Cond: ((permid = i_1.permid) AND (period = i_1.period))