explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qqqy

Settings
# exclusive inclusive rows x rows loops node
1. 3.243 6,198.954 ↑ 15.7 2,641 1

Nested Loop (cost=7,786.21..51,353,620.49 rows=41,333 width=188) (actual time=28.382..6,198.954 rows=2,641 loops=1)

2. 17.158 44.822 ↑ 15.7 2,641 1

GroupAggregate (cost=6,544.03..8,610.68 rows=41,333 width=228) (actual time=25.711..44.822 rows=2,641 loops=1)

  • Group Key: dss.dsm_site_id, svo.interval_start, svo.interval_seconds, svo.sample_seconds
3. 8.844 27.664 ↑ 1.6 26,410 1

Sort (cost=6,544.03..6,647.36 rows=41,333 width=32) (actual time=25.686..27.664 rows=26,410 loops=1)

  • Sort Key: svo.interval_start, svo.interval_seconds, svo.sample_seconds
  • Sort Method: quicksort Memory: 2832kB
4. 3.710 18.820 ↑ 1.6 26,410 1

Hash Join (cost=16.21..3,374.82 rows=41,333 width=32) (actual time=0.096..18.820 rows=26,410 loops=1)

  • Hash Cond: (svo.sensor_id = s.sensor_id)
5. 1.856 15.096 ↓ 18.3 26,410 1

Nested Loop (cost=15.05..3,368.07 rows=1,447 width=40) (actual time=0.078..15.096 rows=26,410 loops=1)

6. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on dsm_sites ds (cost=0.00..1.02 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Filter: (dsm_site_id = 2)
  • Rows Removed by Filter: 6
7. 5.198 13.229 ↓ 18.3 26,410 1

Hash Join (cost=15.05..3,352.58 rows=1,447 width=40) (actual time=0.066..13.229 rows=26,410 loops=1)

  • Hash Cond: (svo.sensor_id = dss.sensor_id)
8. 8.022 8.022 ↓ 1.5 39,615 1

Seq Scan on svo (cost=0.00..3,267.01 rows=26,303 width=32) (actual time=0.053..8.022 rows=39,615 loops=1)

  • Filter: ((interval_start >= '2019-01-01 00:00:00+10'::timestamp with time zone) AND (interval_start < '2019-01-02 00:00:00+10'::timestamp with time zone) AND (lane = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[])))
  • Rows Removed by Filter: 32640
9. 0.001 0.009 ↑ 2.8 4 1

Hash (cost=14.91..14.91 rows=11 width=8) (actual time=0.009..0.009 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.003 0.008 ↑ 2.8 4 1

Bitmap Heap Scan on dsm_sites_sensors dss (cost=4.24..14.91 rows=11 width=8) (actual time=0.007..0.008 rows=4 loops=1)

  • Recheck Cond: (dsm_site_id = 2)
  • Heap Blocks: exact=1
11. 0.005 0.005 ↑ 1.6 7 1

Bitmap Index Scan on "IXFK_dsm_sites_sensors_dsm_sites" (cost=0.00..4.24 rows=11 width=0) (actual time=0.005..0.005 rows=7 loops=1)

  • Index Cond: (dsm_site_id = 2)
12. 0.005 0.014 ↓ 2.1 15 1

Hash (cost=1.07..1.07 rows=7 width=4) (actual time=0.014..0.014 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.009 0.009 ↓ 2.1 15 1

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

14. 2.641 6,150.889 ↑ 1.0 1 2,641

Aggregate (cost=1,242.19..1,242.20 rows=1 width=4) (actual time=2.329..2.329 rows=1 loops=2,641)

15. 5.251 6,148.248 ↑ 6,440.0 1 2,641

Hash Join (cost=16.21..1,209.99 rows=6,440 width=16) (actual time=0.025..2.328 rows=1 loops=2,641)

  • Hash Cond: (dss_1.sensor_id = s_1.sensor_id)
16. 21.128 6,142.966 ↑ 225.0 1 2,641

Hash Join (cost=15.05..1,208.23 rows=225 width=24) (actual time=0.023..2.326 rows=1 loops=2,641)

  • Hash Cond: (tbl.sensor_id = dss_1.sensor_id)
17. 6,095.428 6,095.428 ↑ 2,049.0 2 2,641

Seq Scan on tirtl_beam_levels tbl (cost=0.00..1,182.20 rows=4,098 width=20) (actual time=0.006..2.308 rows=2 loops=2,641)

  • Filter: ((svo.interval_start >= effective_start) AND (svo.interval_start < effective_end))
  • Rows Removed by Filter: 36878
18. 15.846 26.410 ↑ 2.8 4 2,641

Hash (cost=14.91..14.91 rows=11 width=4) (actual time=0.010..0.010 rows=4 loops=2,641)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 5.282 10.564 ↑ 2.8 4 2,641

Bitmap Heap Scan on dsm_sites_sensors dss_1 (cost=4.24..14.91 rows=11 width=4) (actual time=0.003..0.004 rows=4 loops=2,641)

  • Recheck Cond: (dsm_site_id = dss.dsm_site_id)
  • Heap Blocks: exact=2641
20. 5.282 5.282 ↑ 1.6 7 2,641

Bitmap Index Scan on "IXFK_dsm_sites_sensors_dsm_sites" (cost=0.00..4.24 rows=11 width=0) (actual time=0.002..0.002 rows=7 loops=2,641)

  • Index Cond: (dsm_site_id = dss.dsm_site_id)
21. 0.007 0.031 ↓ 2.1 15 1

Hash (cost=1.07..1.07 rows=7 width=4) (actual time=0.031..0.031 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.024 0.024 ↓ 2.1 15 1

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