explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bdSJ

Settings
# exclusive inclusive rows x rows loops node
1. 175.862 1,366.126 ↑ 2.2 287,445 1

Sort (cost=259,209.13..260,811.91 rows=641,110 width=126) (actual time=1,328.096..1,366.126 rows=287,445 loops=1)

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

CTE s

3. 0.008 0.042 ↓ 5.0 5 1

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

4. 0.007 0.024 ↓ 5.0 5 1

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

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

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

6. 0.002 0.008 ↓ 2.0 2 1

Hash (cost=1.08..1.08 rows=1 width=8) (actual time=0.008..0.008 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. 1,049.409 1,190.264 ↑ 2.2 287,445 1

Hash Right Join (cost=8,638.81..114,098.17 rows=641,110 width=126) (actual time=232.785..1,190.264 rows=287,445 loops=1)

  • Hash Cond: (tbl.sensor_id = d.sensor_id)
  • Join Filter: ((d.detected_timestamp >= tbl.effective_start) AND (d.detected_timestamp < tbl.effective_end))
  • Rows Removed by Join Filter: 13694538
10. 2.478 2.478 ↑ 1.3 288 1

Seq Scan on tirtl_beam_levels tbl (cost=0.00..1,182.20 rows=365 width=36) (actual time=0.009..2.478 rows=288 loops=1)

  • Filter: ((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))
  • Rows Removed by Filter: 36592
11. 72.300 138.377 ↓ 3.0 287,445 1

Hash (cost=6,036.84..6,036.84 rows=95,838 width=94) (actual time=138.377..138.377 rows=287,445 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 8 (originally 4) Memory Usage: 22463kB
12. 26.312 66.077 ↓ 3.0 287,445 1

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

13. 0.008 0.055 ↓ 5.0 5 1

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

  • Group Key: s.sensor_id
14. 0.047 0.047 ↓ 5.0 5 1

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

15. 39.710 39.710 ↑ 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..7.942 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