explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VCPh

Settings
# exclusive inclusive rows x rows loops node
1. 206.537 3,666.321 ↑ 3.4 287,445 1

Sort (cost=383,561.68..385,984.25 rows=969,029 width=126) (actual time=3,624.392..3,666.321 rows=287,445 loops=1)

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

CTE s

3. 0.007 0.042 ↓ 5.0 5 1

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

4. 0.006 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_2.location_id = l.location_id)
5. 0.008 0.008 ↓ 2.1 15 1

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

6. 0.005 0.011 ↓ 2.0 2 1

Hash (cost=1.08..1.08 rows=1 width=8) (actual time=0.011..0.011 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. 2,226.280 3,459.784 ↑ 3.4 287,445 1

Nested Loop Left Join (cost=10.06..161,340.03 rows=969,029 width=126) (actual time=0.128..3,459.784 rows=287,445 loops=1)

  • Join Filter: ((d.detected_timestamp >= tbl.effective_start) AND (d.detected_timestamp <= tbl.effective_end))
  • Rows Removed by Join Filter: 41104635
10. 31.206 83.724 ↓ 3.0 287,445 1

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

11. 0.008 0.053 ↓ 5.0 5 1

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

  • Group Key: s_1.sensor_id
12. 0.045 0.045 ↓ 5.0 5 1

CTE Scan on s s_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.029..0.045 rows=5 loops=1)

13. 52.465 52.465 ↑ 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.007..10.493 rows=57,489 loops=5)

  • Index Cond: (sensor_id = s_1.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
14. 1,149.718 1,149.780 ↓ 1.6 144 287,445

Materialize (cost=9.61..258.84 rows=91 width=32) (actual time=0.000..0.004 rows=144 loops=287,445)

15. 0.024 0.062 ↓ 1.6 144 1

Nested Loop (cost=9.61..258.38 rows=91 width=32) (actual time=0.034..0.062 rows=144 loops=1)

16. 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.sensor_id
17. 0.001 0.001 ↓ 5.0 5 1

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

18. 0.015 0.035 ↑ 3.1 29 5

Bitmap Heap Scan on tirtl_beam_levels tbl (cost=9.59..257.44 rows=91 width=36) (actual time=0.005..0.007 rows=29 loops=5)

  • Recheck Cond: ((sensor_id = s.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
19. 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.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))