explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1vQ0

Settings
# exclusive inclusive rows x rows loops node
1. 0.331 9,666.108 ↑ 1.0 20 1

Limit (cost=366,149.70..366,149.75 rows=20 width=679) (actual time=9,665.771..9,666.108 rows=20 loops=1)

2. 270.288 9,665.777 ↑ 11,412.2 20 1

Sort (cost=366,149.70..366,720.31 rows=228,244 width=679) (actual time=9,665.768..9,665.777 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: 36kB
3. 3,357.556 9,395.489 ↑ 1.1 209,012 1

GroupAggregate (cost=345,810.96..360,076.21 rows=228,244 width=679) (actual time=5,861.936..9,395.489 rows=209,012 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. 590.037 6,037.933 ↑ 1.1 214,838 1

Sort (cost=345,810.96..346,381.57 rows=228,244 width=598) (actual time=5,861.766..6,037.933 rows=214,838 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: 88488kB
5. 259.947 5,447.896 ↑ 1.1 214,838 1

Hash Left Join (cost=165,376.06..264,645.99 rows=228,244 width=598) (actual time=3,511.494..5,447.896 rows=214,838 loops=1)

  • Hash Cond: (boxes.id = audits_2.auditable_id)
6. 85.114 5,003.328 ↑ 1.1 214,838 1

Gather (cost=109,522.34..200,497.00 rows=228,244 width=590) (actual time=3,326.845..5,003.328 rows=214,838 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 110.211 4,918.214 ↑ 1.3 71,613 3

Hash Left Join (cost=108,522.34..176,672.60 rows=95,102 width=590) (actual time=3,388.479..4,918.214 rows=71,613 loops=3)

  • Hash Cond: (audits_1.user_id = created_user.id)
8. 207.653 4,807.972 ↑ 1.3 71,613 3

Hash Left Join (cost=108,510.09..176,410.71 rows=95,102 width=562) (actual time=3,388.382..4,807.972 rows=71,613 loops=3)

  • Hash Cond: (boxes.id = audits_1.auditable_id)
9. 258.065 3,837.124 ↑ 1.3 71,613 3

Hash Left Join (cost=62,555.58..108,994.34 rows=95,102 width=558) (actual time=2,622.809..3,837.124 rows=71,613 loops=3)

  • Hash Cond: (boxes.id = audits.auditable_id)
10. 118.659 2,822.285 ↑ 1.4 69,671 3

Hash Left Join (cost=17,414.26..42,510.48 rows=94,442 width=558) (actual time=1,863.646..2,822.285 rows=69,671 loops=3)

  • Hash Cond: (boxes.department_id = departments_join.id)
11. 134.966 2,697.434 ↑ 1.4 69,671 3

Hash Left Join (cost=17,310.21..42,158.30 rows=94,442 width=534) (actual time=1,857.420..2,697.434 rows=69,671 loops=3)

  • Hash Cond: (boxes.sub_company_id = sub_companies_join.id)
12. 121.544 2,545.693 ↑ 1.4 69,671 3

Hash Left Join (cost=17,006.14..41,606.20 rows=94,442 width=494) (actual time=1,840.556..2,545.693 rows=69,671 loops=3)

  • Hash Cond: (boxes.box_type_id = box_types_join.id)
13. 123.829 2,424.126 ↑ 1.4 69,671 3

Hash Left Join (cost=16,977.69..41,328.56 rows=94,442 width=450) (actual time=1,840.516..2,424.126 rows=69,671 loops=3)

  • Hash Cond: (boxes.client_id = clients_join.id)
14. 379.860 2,299.930 ↑ 1.4 69,671 3

Hash Left Join (cost=16,963.03..41,062.44 rows=94,442 width=427) (actual time=1,840.124..2,299.930 rows=69,671 loops=3)

  • Hash Cond: (boxes.id = space_numbers_boxes.box_id)
15. 83.912 83.912 ↑ 1.4 69,671 3

Parallel Seq Scan on boxes (cost=0.00..11,455.28 rows=94,442 width=369) (actual time=0.014..83.912 rows=69,671 loops=3)

  • Filter: ((status)::text = ANY ('{stored,sent,to_eliminate,pending}'::text[]))
  • Rows Removed by Filter: 36076
16. 231.701 1,836.158 ↓ 1.0 209,012 3

Hash (cost=11,901.87..11,901.87 rows=208,973 width=66) (actual time=1,836.157..1,836.158 rows=209,012 loops=3)

  • Buckets: 262144 Batches: 2 Memory Usage: 15667kB
17. 345.506 1,604.457 ↓ 1.0 209,012 3

Hash Left Join (cost=6,972.87..11,901.87 rows=208,973 width=66) (actual time=699.646..1,604.457 rows=209,012 loops=3)

  • Hash Cond: (space_numbers.shelf_columns_rack_id = shelf_columns_racks.id)
18. 409.525 1,227.045 ↓ 1.0 209,012 3

Hash Left Join (cost=6,443.24..10,823.54 rows=208,973 width=24) (actual time=667.641..1,227.045 rows=209,012 loops=3)

  • Hash Cond: (space_numbers_boxes.space_number_id = space_numbers.id)
19. 151.023 151.023 ↓ 1.0 209,012 3

Seq Scan on space_numbers_boxes (cost=0.00..3,831.73 rows=208,973 width=16) (actual time=0.013..151.023 rows=209,012 loops=3)

20. 347.658 666.497 ↓ 1.0 209,012 3

Hash (cost=3,831.44..3,831.44 rows=208,944 width=24) (actual time=666.496..666.497 rows=209,012 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 13479kB
21. 318.839 318.839 ↓ 1.0 209,012 3

Seq Scan on space_numbers (cost=0.00..3,831.44 rows=208,944 width=24) (actual time=0.016..318.839 rows=209,012 loops=3)

22. 17.601 31.906 ↓ 1.0 16,146 3

Hash (cost=328.17..328.17 rows=16,117 width=26) (actual time=31.905..31.906 rows=16,146 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 1138kB
23. 14.305 14.305 ↓ 1.0 16,146 3

Seq Scan on shelf_columns_racks (cost=0.00..328.17 rows=16,117 width=26) (actual time=0.026..14.305 rows=16,146 loops=3)

24. 0.171 0.367 ↑ 1.0 296 3

Hash (cost=10.96..10.96 rows=296 width=23) (actual time=0.366..0.367 rows=296 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
25. 0.196 0.196 ↑ 1.0 296 3

Seq Scan on clients clients_join (cost=0.00..10.96 rows=296 width=23) (actual time=0.024..0.196 rows=296 loops=3)

26. 0.011 0.023 ↑ 117.1 7 3

Hash (cost=18.20..18.20 rows=820 width=44) (actual time=0.022..0.023 rows=7 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.012 0.012 ↑ 117.1 7 3

Seq Scan on box_types box_types_join (cost=0.00..18.20 rows=820 width=44) (actual time=0.007..0.012 rows=7 loops=3)

28. 6.484 16.775 ↓ 1.0 8,584 3

Hash (cost=196.81..196.81 rows=8,581 width=40) (actual time=16.774..16.775 rows=8,584 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 740kB
29. 10.291 10.291 ↓ 1.0 8,584 3

Seq Scan on sub_companies sub_companies_join (cost=0.00..196.81 rows=8,581 width=40) (actual time=0.011..10.291 rows=8,584 loops=3)

30. 4.500 6.192 ↓ 1.0 3,235 3

Hash (cost=64.02..64.02 rows=3,202 width=24) (actual time=6.192..6.192 rows=3,235 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 210kB
31. 1.692 1.692 ↓ 1.0 3,235 3

Seq Scan on departments departments_join (cost=0.00..64.02 rows=3,202 width=24) (actual time=0.022..1.692 rows=3,235 loops=3)

32. 303.261 756.774 ↓ 1.0 323,065 3

Hash (cost=39,855.81..39,855.81 rows=322,121 width=4) (actual time=756.773..756.774 rows=323,065 loops=3)

  • Buckets: 524288 Batches: 2 Memory Usage: 9778kB
33. 453.513 453.513 ↓ 1.0 323,065 3

Seq Scan on audits (cost=0.00..39,855.81 rows=322,121 width=4) (actual time=2.974..453.513 rows=323,065 loops=3)

  • Filter: ((auditable_type)::text = 'Box'::text)
  • Rows Removed by Filter: 24511
34. 285.994 763.195 ↑ 1.0 317,239 3

Hash (cost=40,718.57..40,718.57 rows=319,115 width=8) (actual time=763.195..763.195 rows=317,239 loops=3)

  • Buckets: 524288 Batches: 2 Memory Usage: 9676kB
35. 477.201 477.201 ↑ 1.0 317,239 3

Seq Scan on audits audits_1 (cost=0.00..40,718.57 rows=319,115 width=8) (actual time=0.012..477.201 rows=317,239 loops=3)

  • Filter: (((auditable_type)::text = 'Box'::text) AND ((action)::text = 'create'::text))
  • Rows Removed by Filter: 30337
36. 0.006 0.031 ↑ 100.0 1 3

Hash (cost=11.00..11.00 rows=100 width=40) (actual time=0.030..0.031 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.025 0.025 ↑ 100.0 1 3

Seq Scan on users created_user (cost=0.00..11.00 rows=100 width=40) (actual time=0.023..0.025 rows=1 loops=3)

38. 3.640 184.621 ↓ 1.9 5,826 1

Hash (cost=55,816.18..55,816.18 rows=3,003 width=12) (actual time=184.620..184.621 rows=5,826 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 315kB
39. 9.948 180.981 ↓ 1.9 5,826 1

Nested Loop (cost=38,763.77..55,816.18 rows=3,003 width=12) (actual time=134.790..180.981 rows=5,826 loops=1)

40. 7.125 159.381 ↓ 1.9 5,826 1

Finalize GroupAggregate (cost=38,763.35..39,116.83 rows=3,003 width=12) (actual time=134.755..159.381 rows=5,826 loops=1)

  • Group Key: audits_3.auditable_id
41. 0.000 152.256 ↓ 2.3 5,826 1

Gather Merge (cost=38,763.35..39,074.28 rows=2,504 width=12) (actual time=134.730..152.256 rows=5,826 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
42. 6.390 379.113 ↓ 1.6 1,942 3

Partial GroupAggregate (cost=37,763.32..37,785.23 rows=1,252 width=12) (actual time=123.335..126.371 rows=1,942 loops=3)

  • Group Key: audits_3.auditable_id
43. 14.421 372.723 ↓ 1.6 1,942 3

Sort (cost=37,763.32..37,766.45 rows=1,252 width=12) (actual time=123.327..124.241 rows=1,942 loops=3)

  • Sort Key: audits_3.auditable_id
  • Sort Method: quicksort Memory: 63kB
44. 358.302 358.302 ↓ 1.6 1,942 3

Parallel Seq Scan on audits audits_3 (cost=0.00..37,698.91 rows=1,252 width=12) (actual time=64.162..119.434 rows=1,942 loops=3)

  • Filter: (((auditable_type)::text = 'Box'::text) AND ((action)::text = 'update'::text))
  • Rows Removed by Filter: 113917
45. 11.652 11.652 ↑ 1.0 1 5,826

Index Scan using audits_pkey on audits audits_2 (cost=0.42..5.55 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=5,826)

  • Index Cond: (id = (max(audits_3.id)))
Planning time : 7.055 ms
Execution time : 9,682.042 ms