explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dp9x

Settings
# exclusive inclusive rows x rows loops node
1. 6,236.239 39,719.655 ↓ 1.0 5,166,229 1

Hash Join (cost=147,232.85..7,909,469.97 rows=4,988,350 width=2,993) (actual time=553.807..39,719.655 rows=5,166,229 loops=1)

  • Buffers: shared hit=55 read=261954, temp read=199893 written=199863
2. 2,312.306 33,483.053 ↓ 1.0 5,166,229 1

Hash Join (cost=147,156.11..7,853,274.28 rows=4,988,350 width=2,986) (actual time=553.421..33,483.053 rows=5,166,229 loops=1)

  • Buffers: shared hit=8 read=261954, temp read=199893 written=199863
3. 3,199.773 31,170.311 ↓ 1.0 5,166,229 1

Nested Loop (cost=147,079.36..7,834,491.21 rows=4,988,350 width=2,979) (actual time=552.981..31,170.311 rows=5,166,229 loops=1)

  • Buffers: shared hit=7 read=261908, temp read=199893 written=199863
4. 1,918.639 27,970.538 ↓ 1.0 5,166,229 1

Nested Loop (cost=147,079.09..7,772,128.54 rows=4,988,350 width=2,961) (actual time=552.958..27,970.538 rows=5,166,229 loops=1)

  • Buffers: shared hit=7 read=261905, temp read=199893 written=199863
5. 0.043 0.043 ↑ 1.0 1 1

Seq Scan on currencies currencies (cost=0..5.21 rows=1 width=12) (actual time=0.02..0.043 rows=1 loops=1)

  • Filter: (currencies.id = 161)
  • Buffers: shared hit=1 read=2
6. 2,632.852 26,051.856 ↓ 1.0 5,166,229 1

Hash Join (cost=147,079.09..7,722,239.83 rows=4,988,350 width=2,953) (actual time=552.936..26,051.856 rows=5,166,229 loops=1)

  • Buffers: shared hit=6 read=261903, temp read=199893 written=199863
7. 2,798.673 23,418.995 ↓ 1.0 5,166,229 1

Hash Join (cost=147,078.02..7,653,648.95 rows=4,988,350 width=2,921) (actual time=552.921..23,418.995 rows=5,166,229 loops=1)

  • Buffers: shared hit=6 read=261902, temp read=199893 written=199863
8. 1,955.879 20,620.303 ↓ 1.0 5,166,229 1

Nested Loop (cost=147,076.39..7,585,057.51 rows=4,988,350 width=2,849) (actual time=552.894..20,620.303 rows=5,166,229 loops=1)

  • Buffers: shared hit=6 read=261901, temp read=199893 written=199863
9. 0.014 0.014 ↑ 1.0 1 1

Index Scan using portfolios_pkey on portfolios p (cost=0.28..8.29 rows=1 width=26) (actual time=0.011..0.014 rows=1 loops=1)

  • Index Cond: (p.id = 502)
  • Buffers: shared hit=1 read=2
10. 12,354.110 18,664.410 ↓ 1.0 5,166,229 1

Hash Join (cost=147,076.11..7,535,165.71 rows=4,988,350 width=2,827) (actual time=552.881..18,664.41 rows=5,166,229 loops=1)

  • Buffers: shared hit=5 read=261899, temp read=199893 written=199863
11. 5,766.500 6,229.796 ↓ 1.0 5,166,229 1

Bitmap Heap Scan on imported_instrument_performances ip (cost=138,528.87..4,491,976.65 rows=4,988,350 width=2,407) (actual time=469.455..6,229.796 rows=5,166,229 loops=1)

  • Filter: ((ip.date_from >= '2008-11-11'::date) AND (ip.date_to <= '2019-03-31'::date) AND (ip.currency_id = 161))
  • Buffers: shared read=260325
12. 463.296 463.296 ↑ 1.4 5,166,229 1

Bitmap Index Scan on index_imported_instrument_performances_on_portfolio_id (cost=0..137,281.78 rows=7,179,895 width=0) (actual time=463.296..463.296 rows=5,166,229 loops=1)

  • Index Cond: (ip.portfolio_id = 502)
  • Buffers: shared read=14119
13. 42.930 80.504 ↓ 1.0 75,760 1

Hash (cost=3,382.26..3,382.26 rows=75,759 width=428) (actual time=80.504..80.504 rows=75,760 loops=1)

  • Buffers: shared hit=5 read=1574, temp written=1139
14. 26.659 37.574 ↓ 1.0 75,760 1

Hash Join (cost=6.98..3,382.26 rows=75,759 width=428) (actual time=0.046..37.574 rows=75,760 loops=1)

  • Buffers: shared hit=5 read=1574
15. 10.873 10.873 ↓ 1.0 75,760 1

Seq Scan on instruments i (cost=0..2,333.59 rows=75,759 width=420) (actual time=0.002..10.873 rows=75,760 loops=1)

  • Buffers: shared hit=2 read=1574
16. 0.017 0.042 ↑ 1.0 177 1

Hash (cost=4.77..4.77 rows=177 width=12) (actual time=0.042..0.042 rows=177 loops=1)

  • Buffers: shared hit=3
17. 0.025 0.025 ↑ 1.0 177 1

Seq Scan on currencies ic (cost=0..4.77 rows=177 width=12) (actual time=0.002..0.025 rows=177 loops=1)

  • Buffers: shared hit=3
18. 0.004 0.019 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=76) (actual time=0.019..0.019 rows=28 loops=1)

  • Buffers: shared read=1
19. 0.015 0.015 ↑ 1.0 28 1

Seq Scan on instrument_types it (cost=0..1.28 rows=28 width=76) (actual time=0.011..0.015 rows=28 loops=1)

  • Buffers: shared read=1
20. 0.003 0.009 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=36) (actual time=0.009..0.009 rows=3 loops=1)

  • Buffers: shared read=1
21. 0.006 0.006 ↑ 1.0 3 1

Seq Scan on strategy_types st (cost=0..1.03 rows=3 width=36) (actual time=0.006..0.006 rows=3 loops=1)

  • Buffers: shared read=1
22. 0.000 0.000 ↑ 1.0 1 5,166,229

Materialize (cost=0.28..8.3 rows=1 width=18) (actual time=0..0 rows=1 loops=5,166,229)

  • Buffers: shared read=3
23. 0.022 0.022 ↑ 1.0 1 1

Index Scan using index_lookups_on_type_name_and_value on lookups calculation_type_lookup (cost=0.28..8.29 rows=1 width=18) (actual time=0.02..0.022 rows=1 loops=1)

  • Index Cond: (((calculation_type_lookup.type_name)::text = 'InstrumentPerformanceType'::text) AND (calculation_type_lookup.value = '2'::numeric))
  • Buffers: shared read=3
24. 0.134 0.436 ↓ 1.0 1,323 1

Hash (cost=60.22..60.22 rows=1,322 width=11) (actual time=0.436..0.436 rows=1,323 loops=1)

  • Buffers: shared hit=1 read=46
25. 0.302 0.302 ↓ 1.0 1,323 1

Seq Scan on portfolios parent_portfolios (cost=0..60.22 rows=1,322 width=11) (actual time=0.005..0.302 rows=1,323 loops=1)

  • Buffers: shared hit=1 read=46
26. 0.154 0.363 ↓ 1.0 1,323 1

Hash (cost=60.22..60.22 rows=1,322 width=11) (actual time=0.363..0.363 rows=1,323 loops=1)

  • Buffers: shared hit=47
27. 0.209 0.209 ↓ 1.0 1,323 1

Seq Scan on portfolios origin_portfolios (cost=0..60.22 rows=1,322 width=11) (actual time=0.001..0.209 rows=1,323 loops=1)

  • Buffers: shared hit=47