explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Eiw0

Settings
# exclusive inclusive rows x rows loops node
1. 180.564 1,229.230 ↓ 3.0 287,445 1

Sort (cost=22,494.24..22,733.83 rows=95,838 width=126) (actual time=1,189.432..1,229.230 rows=287,445 loops=1)

  • Sort Key: d.detected_timestamp
  • Sort Method: external merge Disk: 29232kB
2.          

CTE s

3. 0.008 0.043 ↓ 5.0 5 1

Nested Loop (cost=1.09..3.22 rows=1 width=290) (actual time=0.028..0.043 rows=5 loops=1)

4. 0.005 0.025 ↓ 5.0 5 1

Hash Join (cost=1.09..2.19 rows=1 width=294) (actual time=0.023..0.025 rows=5 loops=1)

  • Hash Cond: (s_1.location_id = l.location_id)
5. 0.008 0.008 ↓ 2.1 15 1

Seq Scan on sensors s_1 (cost=0.00..1.07 rows=7 width=290) (actual time=0.007..0.008 rows=15 loops=1)

6. 0.006 0.012 ↓ 2.0 2 1

Hash (cost=1.08..1.08 rows=1 width=8) (actual time=0.012..0.012 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.006 0.006 ↓ 2.0 2 1

Seq Scan on locations l (cost=0.00..1.08 rows=1 width=8) (actual time=0.005..0.006 rows=2 loops=1)

  • Filter: (route_id = 5)
  • Rows Removed by Filter: 13
8. 0.010 0.010 ↑ 1.0 1 5

Seq Scan on routes r (cost=0.00..1.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=5)

  • Filter: (route_id = 5)
  • Rows Removed by Filter: 5
9.          

CTE tbl

10. 0.027 0.065 ↓ 1.6 144 1

Nested Loop (cost=9.61..258.38 rows=91 width=60) (actual time=0.036..0.065 rows=144 loops=1)

11. 0.002 0.003 ↓ 5.0 5 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.002..0.003 rows=5 loops=1)

  • Group Key: s_2.sensor_id
12. 0.001 0.001 ↓ 5.0 5 1

CTE Scan on s s_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=5 loops=1)

13. 0.015 0.035 ↑ 3.1 29 5

Bitmap Heap Scan on tirtl_beam_levels tbl_1 (cost=9.59..257.44 rows=91 width=60) (actual time=0.006..0.007 rows=29 loops=5)

  • Recheck Cond: ((sensor_id = s_2.sensor_id) AND (effective_start < '2019-01-02 00:00:00+10'::timestamp with time zone) AND (effective_end > '2019-01-01 00:00:00+10'::timestamp with time zone))
  • Heap Blocks: exact=2
14. 0.020 0.020 ↑ 3.1 29 5

Bitmap Index Scan on ix_tbl_sensor_effective_dates (cost=0.00..9.56 rows=91 width=0) (actual time=0.004..0.004 rows=29 loops=5)

  • Index Cond: ((sensor_id = s_2.sensor_id) AND (effective_start < '2019-01-02 00:00:00+10'::timestamp with time zone) AND (effective_end > '2019-01-01 00:00:00+10'::timestamp with time zone))
15. 969.637 1,048.666 ↓ 3.0 287,445 1

Hash Left Join (cost=3.40..8,076.35 rows=95,838 width=126) (actual time=0.190..1,048.666 rows=287,445 loops=1)

  • Hash Cond: (d.sensor_id = tbl.sensor_id)
  • Join Filter: ((d.detected_timestamp >= tbl.effective_start) AND (d.detected_timestamp <= tbl.effective_end))
  • Rows Removed by Join Filter: 13694538
16. 31.241 78.900 ↓ 3.0 287,445 1

Nested Loop (cost=0.45..6,036.84 rows=95,838 width=94) (actual time=0.057..78.900 rows=287,445 loops=1)

17. 0.008 0.054 ↓ 5.0 5 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.049..0.054 rows=5 loops=1)

  • Group Key: s.sensor_id
18. 0.046 0.046 ↓ 5.0 5 1

CTE Scan on s (cost=0.00..0.02 rows=1 width=4) (actual time=0.030..0.046 rows=5 loops=1)

19. 47.605 47.605 ↑ 1.7 57,489 5

Index Scan using "IXFK_detections_sensors" on detections d (cost=0.42..5,078.42 rows=95,838 width=94) (actual time=0.006..9.521 rows=57,489 loops=5)

  • Index Cond: (sensor_id = s.sensor_id)
  • Filter: ((detected_timestamp >= '2019-01-01 00:00:00+10'::timestamp with time zone) AND (detected_timestamp <= '2019-01-02 00:00:00+10'::timestamp with time zone))
  • Rows Removed by Filter: 2511
20. 0.019 0.129 ↓ 1.6 144 1

Hash (cost=1.82..1.82 rows=91 width=36) (actual time=0.129..0.129 rows=144 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
21. 0.110 0.110 ↓ 1.6 144 1

CTE Scan on tbl (cost=0.00..1.82 rows=91 width=36) (actual time=0.037..0.110 rows=144 loops=1)