explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wcgw

Settings
# exclusive inclusive rows x rows loops node
1. 368.486 1,885.498 ↓ 0.0 0 1

Gather (cost=403,112.63..407,616.58 rows=147 width=23) (actual time=1,885.408..1,885.498 rows=0 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
2. 0.002 1,517.012 ↓ 0.0 0 4

Nested Loop Semi Join (cost=402,112.63..406,601.88 rows=47 width=23) (actual time=1,517.012..1,517.012 rows=0 loops=4)

  • Join Filter: ((i.period && "*SELECT* 1".period) AND (s.permid = i.permid))
3. 10.799 1,517.010 ↓ 0.0 0 4

Merge Join (cost=402,112.34..402,506.27 rows=9,455 width=62) (actual time=1,517.010..1,517.010 rows=0 loops=4)

  • Merge Cond: (s.permid = "*SELECT* 1".permid)
4. 0.562 46.640 ↑ 431.0 1 4

Sort (cost=13,609.98..13,611.06 rows=431 width=35) (actual time=46.640..46.640 rows=1 loops=4)

  • Sort Key: s.permid
  • Sort Method: quicksort Memory: 141kB
5. 0.643 46.078 ↓ 3.1 1,349 4

Hash Join (cost=80.38..13,591.12 rows=431 width=35) (actual time=1.728..46.078 rows=1,349 loops=4)

  • Hash Cond: ((i_1.asset_class = ac.permid) AND (s.category = ac.category))
6. 12.556 45.409 ↓ 1.2 1,349 4

Hash Join (cost=78.91..13,583.57 rows=1,104 width=50) (actual time=1.581..45.409 rows=1,349 loops=4)

  • Hash Cond: (i_1.permid = s.permid)
7. 31.492 31.492 ↑ 1.8 96,094 4

Parallel Seq Scan on instruments i_1 (cost=0.00..12,834.27 rows=175,827 width=31) (actual time=0.007..31.492 rows=96,094 loops=4)

8. 0.758 1.361 ↓ 1.0 2,708 4

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

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

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

10. 0.011 0.026 ↑ 1.0 19 4

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 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)

12. 47.922 1,459.571 ↓ 1.9 112,840 4

Sort (cost=388,502.36..388,650.94 rows=59,431 width=27) (actual time=1,448.591..1,459.571 rows=112,840 loops=4)

  • Sort Key: "*SELECT* 1".permid
  • Sort Method: quicksort Memory: 11888kB
13. 7.910 1,411.649 ↓ 1.9 112,840 4

Append (cost=0.43..383,789.80 rows=59,431 width=27) (actual time=0.067..1,411.649 rows=112,840 loops=4)

14. 4.657 246.076 ↓ 1.7 48,277 4

Subquery Scan on *SELECT* 1 (cost=0.43..163,372.95 rows=28,563 width=27) (actual time=0.067..246.076 rows=48,277 loops=4)

15. 239.237 241.419 ↓ 1.7 48,277 4

Nested Loop (cost=0.43..163,087.32 rows=28,563 width=27) (actual time=0.066..241.419 rows=48,277 loops=4)

16. 2.171 2.171 ↑ 1.1 21,216 4

Seq Scan on company_leis (cost=0.00..403.56 rows=23,156 width=25) (actual time=0.019..2.171 rows=21,216 loops=4)

17. 0.011 0.011 ↓ 2.0 2 84,864

Index Scan using organizations_lei_idx on organizations (cost=0.43..7.02 rows=1 width=43) (actual time=0.008..0.011 rows=2 loops=84,864)

  • Index Cond: (lei = company_leis.lei)
18. 5.516 1,157.663 ↓ 2.1 64,563 4

Subquery Scan on *SELECT* 2 (cost=0.41..220,416.85 rows=30,868 width=27) (actual time=0.143..1,157.663 rows=64,563 loops=4)

19. 1,149.470 1,152.147 ↓ 2.1 64,563 4

Nested Loop (cost=0.41..220,108.17 rows=30,868 width=27) (actual time=0.143..1,152.147 rows=64,563 loops=4)

20. 2.638 2.638 ↑ 1.0 29,159 4

Seq Scan on _fallback_companies (cost=0.00..454.28 rows=29,428 width=12) (actual time=0.022..2.638 rows=29,159 loops=4)

21. 0.039 0.039 ↓ 2.0 2 116,636

Index Scan using organizations_permid_period_excl on organizations organizations_1 (cost=0.41..7.45 rows=1 width=23) (actual time=0.029..0.039 rows=2 loops=116,636)

  • Index Cond: (permid = _fallback_companies.permid)
  • Filter: (lei IS NULL)
  • Rows Removed by Filter: 0
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using instruments_permid_period_excl on instruments i (cost=0.29..0.42 rows=1 width=23) (never executed)

  • Index Cond: ((permid = i_1.permid) AND (period = i_1.period))
  • Heap Fetches: 0
Planning time : 3.912 ms
Execution time : 1,885.672 ms