explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O3P2

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 4.711 ↑ 1.0 1 1

CTE Scan on s (cost=2.50..325.16 rows=1 width=58) (actual time=4.707..4.711 rows=1 loops=1)

  • Buffers: shared hit=1189 read=1
2.          

CTE s

3. 0.054 0.054 ↑ 1.0 1 1

Index Only Scan using symbol_unique on symbols (cost=0.28..2.50 rows=1 width=4) (actual time=0.051..0.054 rows=1 loops=1)

  • Index Cond: (symbol = 'BTC'::text)
  • Heap Fetches: 1
  • Buffers: shared hit=3
4.          

SubPlan (forCTE Scan)

5. 0.007 3.631 ↑ 1.0 1 1

Limit (cost=2.55..161.32 rows=1 width=16) (actual time=3.626..3.631 rows=1 loops=1)

  • Buffers: shared hit=16 read=1
6. 0.003 3.624 ↑ 12,806.0 1 1

Custom Scan (ConstraintAwareAppend) (cost=2.55..2,033,282.40 rows=12,806 width=16) (actual time=3.623..3.624 rows=1 loops=1)

  • Hypertable: tickers
  • Chunks left after exclusion: 1
  • Buffers: shared hit=16 read=1
7. 0.002 3.621 ↑ 12,806.0 1 1

Merge Append (cost=2.55..2,033,282.40 rows=12,806 width=16) (actual time=3.621..3.621 rows=1 loops=1)

  • Sort Key: t_1."time
  • Buffers: shared hit=16 read=1
8. 3.619 3.619 ↑ 12,800.0 1 1

Index Scan Backward using _hyper_1_5_chunk_tickers_time_idx on _hyper_1_5_chunk t_1 (cost=0.58..2,033,056.65 rows=12,800 width=16) (actual time=3.619..3.619 rows=1 loops=1)

  • Index Cond: ("time" > (now() - '1 mon'::interval))
  • Filter: ((symbol)::text = (s.symbol)::text)
  • Rows Removed by Filter: 696
  • Buffers: shared hit=16 read=1
9. 0.010 1.011 ↑ 1.0 1 1

Limit (cost=2.55..161.32 rows=1 width=16) (actual time=1.009..1.011 rows=1 loops=1)

  • Buffers: shared hit=1170
10. 0.001 1.001 ↑ 12,806.0 1 1

Custom Scan (ConstraintAwareAppend) (cost=2.55..2,033,282.40 rows=12,806 width=16) (actual time=1.001..1.001 rows=1 loops=1)

  • Hypertable: tickers
  • Chunks left after exclusion: 1
  • Buffers: shared hit=1170
11. 0.001 1.000 ↑ 12,806.0 1 1

Merge Append (cost=2.55..2,033,282.40 rows=12,806 width=16) (actual time=1.000..1.000 rows=1 loops=1)

  • Sort Key: t_3."time" DESC
  • Buffers: shared hit=1170
12. 0.999 0.999 ↑ 12,800.0 1 1

Index Scan using _hyper_1_5_chunk_tickers_time_idx on _hyper_1_5_chunk t_3 (cost=0.58..2,033,056.65 rows=12,800 width=16) (actual time=0.999..0.999 rows=1 loops=1)

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