explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oiBV : Optimization for: test; plan #Imoh

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.020 7,213.406 ↑ 1.0 100 1

Limit (cost=327,363.52..327,363.77 rows=100 width=3,044) (actual time=7,213.374..7,213.406 rows=100 loops=1)

2.          

CTE avail

3. 569.367 7,074.101 ↑ 5.3 31,207 1

Hash Join (cost=2.15..298,899.98 rows=164,610 width=1,465) (actual time=37.356..7,074.101 rows=31,207 loops=1)

  • Hash Cond: (media.med_str_seq = storage.sto_seq)
4. 3,588.193 6,504.717 ↓ 17.6 4,344,406 1

Hash Join (cost=1.09..296,326.90 rows=246,914 width=799) (actual time=0.071..6,504.717 rows=4,344,406 loops=1)

  • Hash Cond: (media.med_sta_seq = media_status.mst_seq)
5. 2,916.517 2,916.517 ↓ 2.9 4,344,406 1

Seq Scan on media (cost=0.00..288,301.10 rows=1,481,485 width=595) (actual time=0.056..2,916.517 rows=4,344,406 loops=1)

  • Filter: ((now() - (med_online_time)::timestamp with time zone) > '00:10:00'::interval)
6. 0.001 0.007 ↑ 1.0 1 1

Hash (cost=1.07..1.07 rows=1 width=204) (actual time=0.007..0.007 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on media_status (cost=0.00..1.07 rows=1 width=204) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: ((mst_key)::text = 'active'::text)
  • Rows Removed by Filter: 5
8. 0.003 0.017 ↑ 1.0 2 1

Hash (cost=1.04..1.04 rows=2 width=666) (actual time=0.017..0.017 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.014 0.014 ↑ 1.0 2 1

Seq Scan on storage (cost=0.00..1.04 rows=2 width=666) (actual time=0.014..0.014 rows=2 loops=1)

  • Filter: (sto_seq <> 5)
  • Rows Removed by Filter: 1
10. 0.338 7,213.386 ↑ 823.0 100 1

Sort (cost=28,463.55..28,669.31 rows=82,305 width=3,044) (actual time=7,213.372..7,213.386 rows=100 loops=1)

  • Sort Key: avail.med_seq
  • Sort Method: top-N heapsort Memory: 77kB
11. 3.447 7,213.048 ↑ 350.2 235 1

Hash Semi Join (cost=19,237.63..25,317.91 rows=82,305 width=3,044) (actual time=7,203.072..7,213.048 rows=235 loops=1)

  • Hash Cond: (avail.med_seq = avail_1.med_seq)
12. 43.919 43.919 ↑ 5.3 31,207 1

CTE Scan on avail (cost=0.00..3,292.20 rows=164,610 width=3,044) (actual time=37.364..43.919 rows=31,207 loops=1)

13. 0.050 7,165.682 ↑ 1.3 235 1

Hash (cost=19,233.88..19,233.88 rows=300 width=8) (actual time=7,165.682..7,165.682 rows=235 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
14. 0.109 7,165.632 ↑ 1.3 235 1

HashAggregate (cost=19,227.88..19,230.88 rows=300 width=8) (actual time=7,165.610..7,165.632 rows=235 loops=1)

  • Group Key: avail_1.med_seq
15. 0.020 7,165.523 ↑ 1.3 235 1

Append (cost=3,323.65..19,227.13 rows=300 width=8) (actual time=7,135.094..7,165.523 rows=235 loops=1)

16. 0.017 7,135.121 ↑ 1.0 100 1

Limit (cost=3,323.65..3,323.90 rows=100 width=8) (actual time=7,135.094..7,135.121 rows=100 loops=1)

17. 3.310 7,135.104 ↑ 8.2 100 1

Sort (cost=3,323.65..3,325.71 rows=823 width=8) (actual time=7,135.092..7,135.104 rows=100 loops=1)

  • Sort Key: avail_1.med_seq
  • Sort Method: top-N heapsort Memory: 29kB
18. 7,131.794 7,131.794 ↓ 14.4 11,874 1

CTE Scan on avail avail_1 (cost=0.00..3,292.20 rows=823 width=8) (actual time=0.002..7,131.794 rows=11,874 loops=1)

  • Filter: (med_unt_seq IS NULL)
  • Rows Removed by Filter: 19333
19. 0.004 6.941 ↑ 2.9 35 1

Limit (cost=8,148.62..8,148.87 rows=100 width=8) (actual time=6.936..6.941 rows=35 loops=1)

20. 0.057 6.937 ↑ 2,463.8 35 1

Sort (cost=8,148.62..8,364.20 rows=86,232 width=8) (actual time=6.935..6.937 rows=35 loops=1)

  • Sort Key: avail_2.med_seq
  • Sort Method: quicksort Memory: 26kB
21. 0.045 6.880 ↑ 2,463.8 35 1

Hash Join (cost=84.17..4,852.90 rows=86,232 width=8) (actual time=0.727..6.880 rows=35 loops=1)

  • Hash Cond: (avail_2.med_unt_seq = unit.unt_seq)
22. 6.183 6.183 ↑ 4,679.6 35 1

CTE Scan on avail avail_2 (cost=0.00..3,292.20 rows=163,787 width=16) (actual time=0.057..6.183 rows=35 loops=1)

  • Filter: (med_speed_max IS NOT NULL)
  • Rows Removed by Filter: 31172
23. 0.120 0.652 ↑ 1.0 805 1

Hash (cost=74.11..74.11 rows=805 width=8) (actual time=0.651..0.652 rows=805 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
24. 0.532 0.532 ↑ 1.0 805 1

Seq Scan on unit (cost=0.00..74.11 rows=805 width=8) (actual time=0.014..0.532 rows=805 loops=1)

  • Filter: ((unt_type)::text = 'unit.type.vehicle'::text)
  • Rows Removed by Filter: 724
25. 0.014 23.441 ↑ 1.0 100 1

Limit (cost=7,751.10..7,751.35 rows=100 width=8) (actual time=23.415..23.441 rows=100 loops=1)

26. 2.822 23.427 ↑ 779.5 100 1

Sort (cost=7,751.10..7,945.96 rows=77,945 width=8) (actual time=23.414..23.427 rows=100 loops=1)

  • Sort Key: avail_3.med_seq
  • Sort Method: top-N heapsort Memory: 29kB
27. 12.850 20.605 ↑ 4.0 19,298 1

Hash Join (cost=83.16..4,772.10 rows=77,945 width=8) (actual time=0.687..20.605 rows=19,298 loops=1)

  • Hash Cond: (avail_3.med_unt_seq = unit_1.unt_seq)
28. 7.115 7.115 ↑ 5.3 31,207 1

CTE Scan on avail avail_3 (cost=0.00..3,292.20 rows=164,610 width=16) (actual time=0.006..7.115 rows=31,207 loops=1)

29. 0.121 0.640 ↑ 1.0 724 1

Hash (cost=74.11..74.11 rows=724 width=8) (actual time=0.640..0.640 rows=724 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
30. 0.519 0.519 ↑ 1.0 724 1

Seq Scan on unit unit_1 (cost=0.00..74.11 rows=724 width=8) (actual time=0.019..0.519 rows=724 loops=1)

  • Filter: ((unt_type)::text <> 'unit.type.vehicle'::text)
  • Rows Removed by Filter: 805