explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XPJP

Settings
# exclusive inclusive rows x rows loops node
1. 3.983 5,671.855 ↓ 1.0 1,987 1

Nested Loop (cost=136.39..83,378.21 rows=1,931 width=106) (actual time=4.880..5,671.855 rows=1,987 loops=1)

2.          

CTE syv

3. 3.303 5.084 ↓ 1.1 2,029 1

Hash Right Join (cost=54.45..94.72 rows=1,931 width=28) (actual time=0.683..5.084 rows=2,029 loops=1)

  • Hash Cond: ((yv.symbol)::text = (s.symbol)::text)
4. 1.128 1.128 ↑ 1.0 2,001 1

Seq Scan on yearly_view yv (cost=0.00..35.01 rows=2,001 width=28) (actual time=0.012..1.128 rows=2,001 loops=1)

5. 0.368 0.653 ↓ 1.1 2,029 1

Hash (cost=30.31..30.31 rows=1,931 width=4) (actual time=0.653..0.653 rows=2,029 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
6. 0.285 0.285 ↓ 1.1 2,029 1

Seq Scan on symbols s (cost=0.00..30.31 rows=1,931 width=4) (actual time=0.012..0.285 rows=2,029 loops=1)

7. 3.548 66.519 ↓ 1.0 1,987 1

Nested Loop (cost=0.00..2,749.79 rows=1,931 width=106) (actual time=0.737..66.519 rows=1,987 loops=1)

8. 8.188 8.188 ↓ 1.1 2,029 1

CTE Scan on syv (cost=0.00..38.62 rows=1,931 width=66) (actual time=0.685..8.188 rows=2,029 loops=1)

9. 6.087 54.783 ↑ 1.0 1 2,029

Limit (cost=0.00..1.38 rows=1 width=138) (actual time=0.026..0.027 rows=1 loops=2,029)

10. 0.000 48.696 ↑ 30.0 1 2,029

Custom Scan (ConstraintAwareAppend) (cost=0.00..41.52 rows=30 width=138) (actual time=0.024..0.024 rows=1 loops=2,029)

  • Hypertable: tickers
  • Chunks left after exclusion: 1
11. 0.000 48.696 ↑ 30.0 1 2,029

Append (cost=0.00..41.52 rows=30 width=40) (actual time=0.024..0.024 rows=1 loops=2,029)

12. 48.696 48.696 ↑ 11.0 1 2,029

Index Scan using _hyper_1_5_chunk_symbol_time_idx on _hyper_1_5_chunk t_1 (cost=0.58..14.00 rows=11 width=40) (actual time=0.024..0.024 rows=1 loops=2,029)

  • Index Cond: (((symbol)::text = (syv.symbol)::text) AND ("time" > (now() - '1 day'::interval)))
13. 361.634 5,601.353 ↑ 1.0 1 1,987

Aggregate (cost=41.67..41.68 rows=1 width=16) (actual time=2.819..2.819 rows=1 loops=1,987)

14. 246.388 5,239.719 ↓ 50.0 1,499 1,987

Custom Scan (ConstraintAwareAppend) (cost=0.00..41.52 rows=30 width=8) (actual time=0.012..2.637 rows=1,499 loops=1,987)

  • Hypertable: tickers
  • Chunks left after exclusion: 1
15. 242.414 4,993.331 ↓ 50.0 1,499 1,987

Append (cost=0.00..41.52 rows=30 width=8) (actual time=0.012..2.513 rows=1,499 loops=1,987)

16. 4,750.917 4,750.917 ↓ 136.3 1,499 1,987

Index Scan using _hyper_1_5_chunk_symbol_time_idx on _hyper_1_5_chunk t_3 (cost=0.58..14.00 rows=11 width=8) (actual time=0.012..2.391 rows=1,499 loops=1,987)

  • Index Cond: (((symbol)::text = (syv.symbol)::text) AND ("time" > (now() - '1 day'::interval)))