explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AXcD

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 314.475 ↑ 1.0 20 1

Limit (cost=9,659.16..9,659.21 rows=20 width=629) (actual time=314.459..314.475 rows=20 loops=1)

2. 11.137 314.459 ↑ 484.1 20 1

Sort (cost=9,659.16..9,683.36 rows=9,681 width=629) (actual time=314.457..314.459 rows=20 loops=1)

  • Sort Key: (array_agg(DISTINCT space_numbers.space_number ORDER BY space_numbers.space_number))
  • Sort Method: top-N heapsort Memory: 48kB
3. 185.205 303.322 ↑ 1.0 9,663 1

GroupAggregate (cost=8,796.49..9,401.55 rows=9,681 width=629) (actual time=115.417..303.322 rows=9,663 loops=1)

  • Group Key: boxes.id, clients_join.id, sub_companies_join.id, departments_join.id, box_types_join.id, shelf_columns_racks.rack_code, shelf_columns_racks.column_number, shelf_columns_racks.shelf_number
4. 22.404 118.117 ↓ 1.0 9,708 1

Sort (cost=8,796.49..8,820.69 rows=9,681 width=518) (actual time=115.268..118.117 rows=9,708 loops=1)

  • Sort Key: boxes.id, clients_join.id, sub_companies_join.id, departments_join.id, box_types_join.id, shelf_columns_racks.rack_code, shelf_columns_racks.column_number, shelf_columns_racks.shelf_number
  • Sort Method: external merge Disk: 3440kB
5. 3.944 95.713 ↓ 1.0 9,708 1

Hash Left Join (cost=5,099.63..5,905.06 rows=9,681 width=518) (actual time=33.743..95.713 rows=9,708 loops=1)

  • Hash Cond: (audits_1.user_id = created_user.id)
6. 6.909 91.740 ↓ 1.0 9,708 1

Hash Left Join (cost=5,093.43..5,873.45 rows=9,681 width=499) (actual time=33.680..91.740 rows=9,708 loops=1)

  • Hash Cond: (boxes.id = audits.auditable_id)
7. 7.083 78.907 ↑ 1.0 9,664 1

Hash Left Join (cost=3,723.46..4,309.86 rows=9,681 width=499) (actual time=27.664..78.907 rows=9,664 loops=1)

  • Hash Cond: (boxes.id = audits_1.auditable_id)
8. 4.225 63.936 ↑ 1.0 9,664 1

Hash Left Join (cost=2,319.67..2,712.45 rows=9,681 width=495) (actual time=19.689..63.936 rows=9,664 loops=1)

  • Hash Cond: (boxes.id = audits_2.auditable_id)
9. 5.175 55.371 ↑ 1.0 9,664 1

Hash Left Join (cost=1,029.32..1,385.78 rows=9,681 width=487) (actual time=15.335..55.371 rows=9,664 loops=1)

  • Hash Cond: (boxes.department_id = departments_join.id)
10. 5.049 49.176 ↑ 1.0 9,664 1

Hash Left Join (cost=941.26..1,272.29 rows=9,681 width=469) (actual time=14.293..49.176 rows=9,664 loops=1)

  • Hash Cond: (boxes.sub_company_id = sub_companies_join.id)
11. 4.817 43.843 ↑ 1.0 9,664 1

Hash Left Join (cost=917.90..1,223.36 rows=9,681 width=443) (actual time=13.996..43.843 rows=9,664 loops=1)

  • Hash Cond: (boxes.box_type_id = box_types_join.id)
12. 4.648 39.019 ↑ 1.0 9,664 1

Hash Left Join (cost=889.45..1,169.36 rows=9,681 width=399) (actual time=13.978..39.019 rows=9,664 loops=1)

  • Hash Cond: (boxes.client_id = clients_join.id)
13. 7.730 34.288 ↑ 1.0 9,664 1

Hash Right Join (cost=882.28..1,136.21 rows=9,681 width=377) (actual time=13.883..34.288 rows=9,664 loops=1)

  • Hash Cond: (space_numbers_boxes.box_id = boxes.id)
14. 5.575 17.102 ↑ 1.0 9,643 1

Hash Left Join (cost=357.20..585.75 rows=9,663 width=66) (actual time=4.339..17.102 rows=9,643 loops=1)

  • Hash Cond: (space_numbers.shelf_columns_rack_id = shelf_columns_racks.id)
15. 5.450 11.271 ↑ 1.0 9,643 1

Hash Left Join (cost=336.70..539.71 rows=9,663 width=24) (actual time=4.060..11.271 rows=9,643 loops=1)

  • Hash Cond: (space_numbers_boxes.space_number_id = space_numbers.id)
16. 1.853 1.853 ↑ 1.0 9,643 1

Seq Scan on space_numbers_boxes (cost=0.00..177.63 rows=9,663 width=16) (actual time=0.008..1.853 rows=9,643 loops=1)

17. 2.187 3.968 ↑ 1.0 10,800 1

Hash (cost=200.20..200.20 rows=10,920 width=24) (actual time=3.968..3.968 rows=10,800 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 719kB
18. 1.781 1.781 ↑ 1.0 10,800 1

Seq Scan on space_numbers (cost=0.00..200.20 rows=10,920 width=24) (actual time=0.007..1.781 rows=10,800 loops=1)

19. 0.132 0.256 ↑ 1.0 600 1

Hash (cost=13.00..13.00 rows=600 width=26) (actual time=0.256..0.256 rows=600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
20. 0.124 0.124 ↑ 1.0 600 1

Seq Scan on shelf_columns_racks (cost=0.00..13.00 rows=600 width=26) (actual time=0.010..0.124 rows=600 loops=1)

21. 4.426 9.456 ↑ 1.0 9,662 1

Hash (cost=404.07..404.07 rows=9,681 width=319) (actual time=9.456..9.456 rows=9,662 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2121kB
22. 5.030 5.030 ↑ 1.0 9,662 1

Seq Scan on boxes (cost=0.00..404.07 rows=9,681 width=319) (actual time=0.011..5.030 rows=9,662 loops=1)

  • Filter: ((status)::text = ANY ('{stored,sent,to_eliminate,pending}'::text[]))
  • Rows Removed by Filter: 30
23. 0.046 0.083 ↓ 1.0 187 1

Hash (cost=4.85..4.85 rows=185 width=22) (actual time=0.083..0.083 rows=187 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
24. 0.037 0.037 ↓ 1.0 187 1

Seq Scan on clients clients_join (cost=0.00..4.85 rows=185 width=22) (actual time=0.006..0.037 rows=187 loops=1)

25. 0.004 0.007 ↑ 205.0 4 1

Hash (cost=18.20..18.20 rows=820 width=44) (actual time=0.007..0.007 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.003 0.003 ↑ 205.0 4 1

Seq Scan on box_types box_types_join (cost=0.00..18.20 rows=820 width=44) (actual time=0.003..0.003 rows=4 loops=1)

27. 0.159 0.284 ↑ 1.1 631 1

Hash (cost=14.83..14.83 rows=683 width=26) (actual time=0.284..0.284 rows=631 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
28. 0.125 0.125 ↑ 1.1 631 1

Seq Scan on sub_companies sub_companies_join (cost=0.00..14.83 rows=683 width=26) (actual time=0.008..0.125 rows=631 loops=1)

29. 0.581 1.020 ↑ 1.0 2,758 1

Hash (cost=53.58..53.58 rows=2,758 width=18) (actual time=1.020..1.020 rows=2,758 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 167kB
30. 0.439 0.439 ↑ 1.0 2,758 1

Seq Scan on departments departments_join (cost=0.00..53.58 rows=2,758 width=18) (actual time=0.005..0.439 rows=2,758 loops=1)

31. 0.010 4.340 ↓ 22.0 22 1

Hash (cost=1,290.34..1,290.34 rows=1 width=12) (actual time=4.340..4.340 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.018 4.330 ↓ 22.0 22 1

Nested Loop (cost=1,282.29..1,290.34 rows=1 width=12) (actual time=4.264..4.330 rows=22 loops=1)

33. 0.024 4.246 ↓ 22.0 22 1

GroupAggregate (cost=1,282.00..1,282.03 rows=1 width=12) (actual time=4.224..4.246 rows=22 loops=1)

  • Group Key: audits_3.auditable_id
34. 0.065 4.222 ↓ 73.0 73 1

Sort (cost=1,282.00..1,282.01 rows=1 width=12) (actual time=4.217..4.222 rows=73 loops=1)

  • Sort Key: audits_3.auditable_id
  • Sort Method: quicksort Memory: 28kB
35. 4.157 4.157 ↓ 73.0 73 1

Seq Scan on audits audits_3 (cost=0.00..1,281.99 rows=1 width=12) (actual time=2.369..4.157 rows=73 loops=1)

  • Filter: (((auditable_type)::text = 'Box'::text) AND ((action)::text = 'update'::text))
  • Rows Removed by Filter: 13663
36. 0.066 0.066 ↑ 1.0 1 22

Index Scan using audits_pkey on audits audits_2 (cost=0.29..8.30 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=22)

  • Index Cond: (id = (max(audits_3.id)))
37. 2.783 7.888 ↑ 1.0 9,692 1

Hash (cost=1,281.99..1,281.99 rows=9,744 width=8) (actual time=7.888..7.888 rows=9,692 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 469kB
38. 5.105 5.105 ↑ 1.0 9,692 1

Seq Scan on audits audits_1 (cost=0.00..1,281.99 rows=9,744 width=8) (actual time=0.038..5.105 rows=9,692 loops=1)

  • Filter: (((auditable_type)::text = 'Box'::text) AND ((action)::text = 'create'::text))
  • Rows Removed by Filter: 4044
39. 2.032 5.924 ↓ 1.0 9,765 1

Hash (cost=1,248.16..1,248.16 rows=9,744 width=4) (actual time=5.924..5.924 rows=9,765 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 472kB
40. 3.892 3.892 ↓ 1.0 9,765 1

Seq Scan on audits (cost=0.00..1,248.16 rows=9,744 width=4) (actual time=0.031..3.892 rows=9,765 loops=1)

  • Filter: ((auditable_type)::text = 'Box'::text)
  • Rows Removed by Filter: 3971
41. 0.006 0.029 ↑ 1.0 9 1

Hash (cost=6.09..6.09 rows=9 width=31) (actual time=0.029..0.029 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.023 0.023 ↑ 1.0 9 1

Seq Scan on users created_user (cost=0.00..6.09 rows=9 width=31) (actual time=0.008..0.023 rows=9 loops=1)

Planning time : 12.747 ms
Execution time : 315.798 ms