explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jG1R

Settings
# exclusive inclusive rows x rows loops node
1. 2.362 39,427.057 ↓ 4.9 10,000 1

Limit (cost=222,820.65..222,825.72 rows=2,028 width=1,225) (actual time=39,420.061..39,427.057 rows=10,000 loops=1)

  • Buffers: shared hit=52100 read=63363
2. 80.923 39,424.695 ↓ 4.9 10,000 1

Sort (cost=222,820.65..222,825.72 rows=2,028 width=1,225) (actual time=39,420.060..39,424.695 rows=10,000 loops=1)

  • Sort Key: d.partition_id
  • Sort Method: top-N heapsort Memory: 16684kB
  • Buffers: shared hit=52100 read=63363
3. 61.973 39,343.772 ↓ 20.7 41,923 1

Hash Join (cost=741.20..222,709.26 rows=2,028 width=1,225) (actual time=28,943.921..39,343.772 rows=41,923 loops=1)

  • Hash Cond: (d.topic_id = t.id)
  • Buffers: shared hit=52100 read=63363
4. 426.664 39,276.200 ↓ 20.7 41,923 1

Hash Join (cost=26.51..221,966.68 rows=2,028 width=126) (actual time=28,938.274..39,276.200 rows=41,923 loops=1)

  • Hash Cond: (d.partition_id = p.id)
  • Buffers: shared hit=51452 read=63363
5. 364.976 38,849.457 ↑ 1.0 3,117,138 1

Append (cost=0.00..210,038.81 rows=3,168,288 width=73) (actual time=0.032..38,849.457 rows=3,117,138 loops=1)

  • Buffers: shared hit=51442 read=63363
6. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on data d (cost=0.00..0.00 rows=1 width=73) (actual time=0.003..0.003 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))
7. 38,484.478 38,484.478 ↑ 1.0 3,117,138 1

Index Scan using start_time_index_2019_05 on data_2019_05 d_1 (cost=0.57..210,038.81 rows=3,168,287 width=73) (actual time=0.028..38,484.478 rows=3,117,138 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))
  • Buffers: shared hit=51442 read=63363
8. 0.005 0.079 ↓ 1.2 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=10
9. 0.023 0.074 ↓ 1.2 6 1

Bitmap Heap Scan on partition p (cost=8.63..26.45 rows=5 width=61) (actual time=0.057..0.074 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=10
10. 0.001 0.051 ↓ 0.0 0 1

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

  • Buffers: shared hit=4
11. 0.025 0.025 ↑ 1.0 3 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 3402kB
  • Buffers: shared hit=648
14. 2.968 2.968 ↓ 1.0 2,986 1

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

  • Buffers: shared hit=648