explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NH05

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 1,007.307 ↑ 3.5 8 1

Hash Join (cost=1,023,147.62..1,023,149.63 rows=28 width=68) (actual time=1,007.299..1,007.307 rows=8 loops=1)

  • Hash Cond: (susm.signal_id = dct.id)
  • Buffers: shared hit=55,003
2.          

CTE strategy_sig_dict

3. 0.000 0.249 ↑ 3.5 8 1

HashAggregate (cost=18.32..18.59 rows=28 width=12) (actual time=0.247..0.249 rows=8 loops=1)

  • Group Key: ssim.strategy_signal_info_msg_id, ssim.name
  • Buffers: shared hit=8
4.          

Initplan (for HashAggregate)

5. 0.009 0.048 ↑ 1.0 1 1

Aggregate (cost=3.59..3.60 rows=1 width=4) (actual time=0.048..0.048 rows=1 loops=1)

  • Buffers: shared hit=2
6. 0.039 0.039 ↑ 1.0 6 1

Seq Scan on strategy_info_msg sim (cost=0.00..3.58 rows=6 width=4) (actual time=0.014..0.039 rows=6 loops=1)

  • Filter: (name = '6E_L_150_067_2_0'::text)
  • Rows Removed by Filter: 120
  • Buffers: shared hit=2
7. 0.212 0.212 ↓ 1.5 48 1

Seq Scan on strategy_signal_info_msg ssim (cost=0.00..14.55 rows=33 width=12) (actual time=0.073..0.212 rows=48 loops=1)

  • Filter: (id_strategy_info_msg = $0)
  • Rows Removed by Filter: 636
  • Buffers: shared hit=8
8. 0.000 1,007.032 ↑ 10.4 7 1

HashAggregate (cost=1,023,128.12..1,023,128.85 rows=73 width=36) (actual time=1,007.028..1,007.032 rows=7 loops=1)

  • Group Key: susm.signal_id
  • Buffers: shared hit=54,995
9.          

Initplan (for HashAggregate)

10. 0.007 944.078 ↑ 1.0 1 1

Result (cost=37.24..37.25 rows=1 width=4) (actual time=944.077..944.078 rows=1 loops=1)

  • Buffers: shared hit=54,017
11.          

Initplan (for Result)

12. 0.003 944.071 ↑ 1.0 1 1

Limit (cost=0.43..37.24 rows=1 width=4) (actual time=944.070..944.071 rows=1 loops=1)

  • Buffers: shared hit=54,017
13. 944.068 944.068 ↑ 5,354.0 1 1

Index Only Scan Backward using i2 on tick_msg tm (cost=0.43..197,050.94 rows=5,354 width=4) (actual time=944.068..944.068 rows=1 loops=1)

  • Index Cond: ((id_journal_record_msg IS NOT NULL) AND (id_day = 1) AND (inst_id = 4,900,054) AND (system_time <= 1,593,036,820.8996112))
  • Heap Fetches: 1
  • Buffers: shared hit=54,017
14. 20.955 1,000.504 ↑ 1,097.4 7,452 1

Hash Semi Join (cost=1.48..961,756.44 rows=8,177,923 width=19) (actual time=944.219..1,000.504 rows=7,452 loops=1)

  • Hash Cond: (susm.signal_id = strategy_sig_dict.id)
  • Buffers: shared hit=54,995
15. 979.543 979.543 ↑ 236.9 89,998 1

Index Scan using system_unique_signals_msg_pkey on system_unique_signals_msg susm (cost=0.56..814,808.48 rows=21,321,013 width=19) (actual time=944.125..979.543 rows=89,998 loops=1)

  • Index Cond: ((id_day = 1) AND (id_journal_record_msg <= $3))
  • Buffers: shared hit=54,995
16. 0.003 0.006 ↑ 3.5 8 1

Hash (cost=0.56..0.56 rows=28 width=4) (actual time=0.005..0.006 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.003 0.003 ↑ 3.5 8 1

CTE Scan on strategy_sig_dict (cost=0.00..0.56 rows=28 width=4) (actual time=0.001..0.003 rows=8 loops=1)

18. 0.005 0.261 ↑ 3.5 8 1

Hash (cost=0.56..0.56 rows=28 width=36) (actual time=0.261..0.261 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=8
19. 0.256 0.256 ↑ 3.5 8 1

CTE Scan on strategy_sig_dict dct (cost=0.00..0.56 rows=28 width=36) (actual time=0.250..0.256 rows=8 loops=1)

  • Buffers: shared hit=8
Planning time : 1.086 ms
Execution time : 1,007.488 ms