explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LSMA

Settings
# exclusive inclusive rows x rows loops node
1. 12.801 585,912.697 ↑ 1.1 1,912 1

CTE Scan on s (cost=31.28..652,294.90 rows=2,028 width=58) (actual time=0.613..585,912.697 rows=1,912 loops=1)

  • Buffers: shared hit=123221617 read=7977
2.          

CTE s

3. 2.293 2.293 ↑ 1.1 1,912 1

Seq Scan on symbols (cost=0.00..31.28 rows=2,028 width=4) (actual time=0.015..2.293 rows=1,912 loops=1)

  • Buffers: shared hit=11
4.          

SubPlan (forCTE Scan)

5. 5.736 563,600.240 ↑ 1.0 1 1,912

Limit (cost=2.55..160.80 rows=1 width=16) (actual time=294.769..294.770 rows=1 loops=1,912)

  • Buffers: shared hit=65350481 read=7954
6. 1.912 563,594.504 ↑ 12,899.0 1 1,912

Custom Scan (ConstraintAwareAppend) (cost=2.55..2,041,382.09 rows=12,899 width=16) (actual time=294.766..294.767 rows=1 loops=1,912)

  • Hypertable: tickers
  • Chunks left after exclusion: 1
  • Buffers: shared hit=65350481 read=7954
7. 1.912 563,592.592 ↑ 12,899.0 1 1,912

Merge Append (cost=2.55..2,041,382.09 rows=12,899 width=16) (actual time=294.766..294.766 rows=1 loops=1,912)

  • Sort Key: t_1."time
  • Buffers: shared hit=65350481 read=7954
8. 563,590.680 563,590.680 ↑ 12,893.0 1 1,912

Index Scan Backward using _hyper_1_5_chunk_tickers_time_idx on _hyper_1_5_chunk t_1 (cost=0.58..2,041,154.81 rows=12,893 width=16) (actual time=294.765..294.765 rows=1 loops=1,912)

  • Index Cond: ("time" > (now() - '1 mon'::interval))
  • Filter: ((symbol)::text = (s.symbol)::text)
  • Rows Removed by Filter: 1617483
  • Buffers: shared hit=65350481 read=7954
9. 7.648 22,299.656 ↑ 1.0 1 1,912

Limit (cost=2.55..160.80 rows=1 width=16) (actual time=11.662..11.663 rows=1 loops=1,912)

  • Buffers: shared hit=57871125 read=23
10. 0.000 22,292.008 ↑ 12,899.0 1 1,912

Custom Scan (ConstraintAwareAppend) (cost=2.55..2,041,382.09 rows=12,899 width=16) (actual time=11.659..11.659 rows=1 loops=1,912)

  • Hypertable: tickers
  • Chunks left after exclusion: 1
  • Buffers: shared hit=57871125 read=23
11. 1.912 22,292.008 ↑ 12,899.0 1 1,912

Merge Append (cost=2.55..2,041,382.09 rows=12,899 width=16) (actual time=11.659..11.659 rows=1 loops=1,912)

  • Sort Key: t_3."time" DESC
  • Buffers: shared hit=57871125 read=23
12. 22,290.096 22,290.096 ↑ 12,893.0 1 1,912

Index Scan using _hyper_1_5_chunk_tickers_time_idx on _hyper_1_5_chunk t_3 (cost=0.58..2,041,154.81 rows=12,893 width=16) (actual time=11.658..11.658 rows=1 loops=1,912)

  • Index Cond: ("time" > (now() - '1 mon'::interval))
  • Filter: ((symbol)::text = (s.symbol)::text)
  • Rows Removed by Filter: 24158
  • Buffers: shared hit=57871125 read=23