explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7whr

Settings
# exclusive inclusive rows x rows loops node
1. 1.285 17,240.550 ↑ 1.3 5,735 1

explain analyze select tag_id, time,value from "History".history_data_view where tag_id=16176 and time between '2020-05-19 14:00:00' and '2020-05-20 14:00:00'Sort (cost=1,505,308.26..1,505,327.11 rows=7,540 width=16) (actual time=17,240.057..17,240.550 rows=5,735 loops=1)

  • Sort Key: history_times."time
  • Sort Method: quicksort Memory: 641kB
2. 395.688 17,239.265 ↑ 1.3 5,735 1

Hash Join (cost=119,306.58..1,504,822.67 rows=7,540 width=16) (actual time=17,228.973..17,239.265 rows=5,735 loops=1)

  • Hash Cond: (history_data.time_id = history_times.id)
3. 16,547.531 16,798.799 ↓ 1.1 2,689,124 1

Bitmap Heap Scan on history_data (cost=73,897.24..1,449,782.12 rows=2,548,216 width=12) (actual time=254.803..16,798.799 rows=2,689,124 loops=1)

  • Recheck Cond: (tag_id = 16176)
  • Rows Removed by Index Recheck: 219798017
  • Heap Blocks: exact=30087 lossy=1211413
4. 251.268 251.268 ↓ 1.1 2,689,124 1

Bitmap Index Scan on history_data_pkey (cost=0.00..73,260.19 rows=2,548,216 width=0) (actual time=251.268..251.268 rows=2,689,124 loops=1)

  • Index Cond: (tag_id = 16176)
5. 14.854 44.778 ↓ 1.4 38,863 1

Hash (cost=45,065.51..45,065.51 rows=27,506 width=12) (actual time=44.778..44.778 rows=38,863 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2182kB
6. 14.628 29.924 ↓ 1.4 38,863 1

Bitmap Heap Scan on history_times (cost=742.37..45,065.51 rows=27,506 width=12) (actual time=15.477..29.924 rows=38,863 loops=1)

  • Recheck Cond: (("time" >= '2020-05-19 14:00:00'::timestamp without time zone) AND ("time" <= '2020-05-20 14:00:00'::timestamp without time zone))
  • Heap Blocks: exact=211
7. 15.296 15.296 ↓ 1.4 38,863 1

Bitmap Index Scan on history_times_time_idx (cost=0.00..735.49 rows=27,506 width=0) (actual time=15.296..15.296 rows=38,863 loops=1)

  • Index Cond: (("time" >= '2020-05-19 14:00:00'::timestamp without time zone) AND ("time" <= '2020-05-20 14:00:00'::timestamp without time zone))