explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mKx : test2

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 7,065.303 ↑ 1.0 100 1

"Limit (cost=324,487.26..324,487.51 rows=100 width=3,044) (actual time=7,065.258..7,065.303 rows=100 loops=1)

2.          

CTE avail

3. 541.693 6,921.398 ↑ 5.2 31,207 1

Hash Join (cost=2.15..296,660.46 rows=160,904 width=1,465) (actual time=33.488..6,921.398 rows=31,207 loops=1)

  • Hash Cond: (media.med_str_seq = storage.sto_seq)
4. 3,485.251 6,379.689 ↓ 18.0 4,344,406 1

Hash Join (cost=1.09..294,145.27 rows=241,356 width=799) (actual time=0.068..6,379.689 rows=4,344,406 loops=1)

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

Seq Scan on media (cost=0.00..286,300.12 rows=1,448,135 width=595) (actual time=0.049..2,894.431 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.002 0.016 ↑ 1.0 2 1

Hash (cost=1.04..1.04 rows=2 width=666) (actual time=0.016..0.016 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.013..0.014 rows=2 loops=1)

  • Filter: (sto_seq <> 5)
  • Rows Removed by Filter: 1
10. 0.375 7,065.273 ↑ 804.5 100 1

Sort (cost=27,826.79..28,027.92 rows=80,452 width=3,044) (actual time=7,065.257..7,065.273 rows=100 loops=1)

  • Sort Key: avail.med_seq
  • Sort Method: top-N heapsort Memory: 77kB
11. 3.665 7,064.898 ↑ 342.3 235 1

Hash Semi Join (cost=18,808.59..24,751.98 rows=80,452 width=3,044) (actual time=7,053.797..7,064.898 rows=235 loops=1)

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

CTE Scan on avail (cost=0.00..3,218.08 rows=160,904 width=3,044) (actual time=33.497..40.969 rows=31,207 loops=1)

13. 0.044 7,020.264 ↑ 1.3 235 1

Hash (cost=18,804.84..18,804.84 rows=300 width=8) (actual time=7,020.264..7,020.264 rows=235 loops=1)

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

HashAggregate (cost=18,798.84..18,801.84 rows=300 width=8) (actual time=7,020.168..7,020.220 rows=235 loops=1)

  • Group Key: avail_1.med_seq
15. 0.027 7,020.086 ↑ 1.3 235 1

Append (cost=3,248.85..18,798.09 rows=300 width=8) (actual time=6,988.476..7,020.086 rows=235 loops=1)

16. 0.017 6,988.501 ↑ 1.0 100 1

Limit (cost=3,248.85..3,249.10 rows=100 width=8) (actual time=6,988.475..6,988.501 rows=100 loops=1)

17. 3.652 6,988.484 ↑ 8.1 100 1

Sort (cost=3,248.85..3,250.86 rows=805 width=8) (actual time=6,988.473..6,988.484 rows=100 loops=1)

  • Sort Key: avail_1.med_seq
  • Sort Method: top-N heapsort Memory: 29kB
18. 6,984.832 6,984.832 ↓ 14.8 11,874 1

CTE Scan on avail avail_1 (cost=0.00..3,218.08 rows=805 width=8) (actual time=0.003..6,984.832 rows=11,874 loops=1)

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

Limit (cost=7,967.03..7,967.28 rows=100 width=8) (actual time=7.399..7.407 rows=35 loops=1)

20. 0.045 7.398 ↑ 2,408.3 35 1

Sort (cost=7,967.03..8,177.75 rows=84,290 width=8) (actual time=7.397..7.398 rows=35 loops=1)

  • Sort Key: avail_2.med_seq
  • Sort Method: quicksort Memory: 26kB
21. 0.043 7.353 ↑ 2,408.3 35 1

Hash Join (cost=84.17..4,745.53 rows=84,290 width=8) (actual time=0.779..7.353 rows=35 loops=1)

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

CTE Scan on avail avail_2 (cost=0.00..3,218.08 rows=160,099 width=16) (actual time=0.060..6.608 rows=35 loops=1)

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

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

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

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

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

Limit (cost=7,578.46..7,578.71 rows=100 width=8) (actual time=24.117..24.151 rows=100 loops=1)

26. 2.808 24.132 ↑ 761.9 100 1

Sort (cost=7,578.46..7,768.93 rows=76,190 width=8) (actual time=24.115..24.132 rows=100 loops=1)

  • Sort Key: avail_3.med_seq
  • Sort Method: top-N heapsort Memory: 29kB
27. 12.924 21.324 ↑ 3.9 19,298 1

Hash Join (cost=83.16..4,666.53 rows=76,190 width=8) (actual time=0.747..21.324 rows=19,298 loops=1)

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

CTE Scan on avail avail_3 (cost=0.00..3,218.08 rows=160,904 width=16) (actual time=0.006..7.690 rows=31,207 loops=1)

29. 0.154 0.710 ↑ 1.0 724 1

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

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

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

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