explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3RID

Settings
# exclusive inclusive rows x rows loops node
1. 0.197 57,513.664 ↓ 3.4 1,989 1

Limit (cost=222,666.31..222,667.75 rows=577 width=1,225) (actual time=57,512.731..57,513.664 rows=1,989 loops=1)

  • Buffers: shared hit=29026 read=86440
2. 4.142 57,513.467 ↓ 3.4 1,989 1

Sort (cost=222,666.31..222,667.75 rows=577 width=1,225) (actual time=57,512.729..57,513.467 rows=1,989 loops=1)

  • Sort Key: d.partition_id
  • Sort Method: quicksort Memory: 3257kB
  • Buffers: shared hit=29026 read=86440
3. 1.814 57,509.325 ↓ 3.4 1,989 1

Hash Join (cost=741.20..222,639.84 rows=577 width=1,225) (actual time=44,539.763..57,509.325 rows=1,989 loops=1)

  • Hash Cond: (d.topic_id = t.id)
  • Buffers: shared hit=29023 read=86440
4. 4.108 57,464.063 ↓ 3.4 1,989 1

Hash Join (cost=26.51..221,917.22 rows=577 width=126) (actual time=44,496.267..57,464.063 rows=1,989 loops=1)

  • Hash Cond: (d.partition_id = p.id)
  • Buffers: shared hit=28749 read=86066
5. 3.102 57,448.433 ↑ 31.3 28,826 1

Append (cost=0.00..218,504.73 rows=901,390 width=73) (actual time=5,300.332..57,448.433 rows=28,826 loops=1)

  • Buffers: shared hit=28746 read=86059
6. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on data d (cost=0.00..0.00 rows=1 width=73) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((start_time <= '2019-05-09 22:27:20'::timestamp without time zone) AND (start_time >= '2019-05-09 20:27:50'::timestamp without time zone) AND (end_time >= '2019-05-09 22:26:50'::timestamp without time zone))
7. 57,445.326 57,445.326 ↑ 31.3 28,826 1

Index Scan using start_time_index_2019_05 on data_2019_05 d_1 (cost=0.57..218,504.73 rows=901,389 width=73) (actual time=5,300.326..57,445.326 rows=28,826 loops=1)

  • Index Cond: ((start_time <= '2019-05-09 22:27:20'::timestamp without time zone) AND (start_time >= '2019-05-09 20:27:50'::timestamp without time zone))
  • Filter: (end_time >= '2019-05-09 22:26:50'::timestamp without time zone)
  • Rows Removed by Filter: 3088312
  • Buffers: shared hit=28746 read=86059
8. 0.011 11.522 ↓ 1.2 6 1

Hash (cost=26.45..26.45 rows=5 width=61) (actual time=11.522..11.522 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3 read=7
9. 8.133 11.511 ↓ 1.2 6 1

Bitmap Heap Scan on partition p (cost=8.63..26.45 rows=5 width=61) (actual time=4.056..11.511 rows=6 loops=1)

  • Recheck Cond: ((((vin)::text = '5G21A6S09K40045EX'::text) AND ((bag_date)::text = '20190508'::text)) OR (((vin)::text = '5G21A6S09K40045EX'::text) AND ((bag_date)::text = '20190509'::text)))
  • Heap Blocks: exact=6
  • Buffers: shared hit=3 read=7
10. 0.002 3.378 ↓ 0.0 0 1

BitmapOr (cost=8.63..8.63 rows=5 width=0) (actual time=3.378..3.378 rows=0 loops=1)

  • Buffers: shared hit=2 read=2
11. 3.366 3.366 ↑ 1.0 3 1

Bitmap Index Scan on main_index (cost=0.00..4.31 rows=3 width=0) (actual time=3.366..3.366 rows=3 loops=1)

  • Index Cond: (((vin)::text = '5G21A6S09K40045EX'::text) AND ((bag_date)::text = '20190508'::text))
  • Buffers: shared read=2
12. 0.010 0.010 ↑ 1.0 3 1

Bitmap Index Scan on main_index (cost=0.00..4.31 rows=3 width=0) (actual time=0.010..0.010 rows=3 loops=1)

  • Index Cond: (((vin)::text = '5G21A6S09K40045EX'::text) AND ((bag_date)::text = '20190509'::text))
  • Buffers: shared hit=2
13. 4.681 43.448 ↓ 1.0 2,986 1

Hash (cost=677.64..677.64 rows=2,964 width=1,115) (actual time=43.448..43.448 rows=2,986 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 3402kB
  • Buffers: shared hit=274 read=374
14. 38.767 38.767 ↓ 1.0 2,986 1

Seq Scan on topic t (cost=0.00..677.64 rows=2,964 width=1,115) (actual time=0.835..38.767 rows=2,986 loops=1)

  • Buffers: shared hit=274 read=374