explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6u0a

Settings
# exclusive inclusive rows x rows loops node
1. 142.053 6,733.235 ↑ 1.0 241,590 1

Hash Left Join (cost=342,998.53..395,486.66 rows=241,948 width=196) (actual time=5,616.226..6,733.235 rows=241,590 loops=1)

  • Hash Cond: (services.service_type_id = service_types.id)
2. 77.907 6,591.108 ↑ 1.0 241,590 1

Hash Left Join (cost=342,988.90..390,940.51 rows=241,948 width=193) (actual time=5,616.103..6,591.108 rows=241,590 loops=1)

  • Hash Cond: (services.worker_vehicle_id = workers_vehicles.id)
3. 156.909 6,511.883 ↑ 1.0 241,590 1

Hash Left Join (cost=342,866.86..387,779.60 rows=241,948 width=190) (actual time=5,614.761..6,511.883 rows=241,590 loops=1)

  • Hash Cond: (services.id = "Consulta SQL personalizada3".service)
4. 43.290 5,050.081 ↑ 1.0 241,590 1

Hash Left Join (cost=231,306.54..269,554.74 rows=241,948 width=162) (actual time=4,309.842..5,050.081 rows=241,590 loops=1)

  • Hash Cond: (tasks.cancellation_task_cause_id = cancellation_task_causes.id)
5. 175.078 5,006.769 ↑ 1.0 241,590 1

Hash Left Join (cost=231,305.45..268,626.50 rows=241,948 width=154) (actual time=4,309.801..5,006.769 rows=241,590 loops=1)

  • Hash Cond: (tasks.service_id = services.id)
6. 359.605 4,789.232 ↑ 1.0 241,590 1

Hash Right Join (cost=226,487.14..260,481.41 rows=241,948 width=114) (actual time=4,267.211..4,789.232 rows=241,590 loops=1)

  • Hash Cond: (tasks_1.id = tasks.id)
7. 162.529 162.529 ↑ 1.0 241,587 1

Seq Scan on tasks tasks_1 (cost=0.00..23,577.48 rows=241,948 width=36) (actual time=0.028..162.529 rows=241,587 loops=1)

8. 103.232 4,267.098 ↑ 1.0 241,590 1

Hash (cost=220,154.79..220,154.79 rows=241,948 width=86) (actual time=4,267.098..4,267.098 rows=241,590 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 3902kB
9. 304.933 4,163.866 ↑ 1.0 241,590 1

Hash Right Join (cost=205,476.18..220,154.79 rows=241,948 width=86) (actual time=2,228.584..4,163.866 rows=241,590 loops=1)

  • Hash Cond: ("Consulta SQL personalizada".id = tasks.id)
10. 30.479 2,588.800 ↑ 1.0 234,816 1

Subquery Scan on Consulta SQL personalizada (cost=99,307.44..104,751.27 rows=241,948 width=32) (actual time=958.137..2,588.800 rows=234,816 loops=1)

11. 1,473.675 2,558.321 ↑ 1.0 234,816 1

Group (cost=99,307.44..102,331.79 rows=241,948 width=53) (actual time=958.134..2,558.321 rows=234,816 loops=1)

  • Group Key: c.name, t.id, ro.name, rd.name
12. 397.700 1,084.646 ↑ 1.0 234,816 1

Sort (cost=99,307.44..99,912.31 rows=241,948 width=53) (actual time=958.131..1,084.646 rows=234,816 loops=1)

  • Sort Key: c.name, t.id, ro.name, rd.name
  • Sort Method: external merge Disk: 13960kB
13. 60.414 686.946 ↑ 1.0 234,816 1

Hash Join (cost=31,596.17..69,401.55 rows=241,948 width=53) (actual time=256.389..686.946 rows=234,816 loops=1)

  • Hash Cond: (rst.region_destiny_id = rd.id)
14. 62.174 623.428 ↑ 1.0 234,816 1

Hash Join (cost=30,859.60..65,338.19 rows=241,948 width=36) (actual time=253.261..623.428 rows=234,816 loops=1)

  • Hash Cond: (rst.region_origin_id = ro.id)
15. 151.197 557.585 ↑ 1.0 234,816 1

Hash Join (cost=30,075.13..61,226.94 rows=241,948 width=12) (actual time=249.561..557.585 rows=234,816 loops=1)

  • Hash Cond: (t.region_service_type_id = rst.id)
16. 157.131 157.131 ↑ 1.0 241,587 1

Seq Scan on tasks t (cost=0.00..23,577.48 rows=241,948 width=8) (actual time=0.012..157.131 rows=241,587 loops=1)

17. 100.906 249.257 ↓ 1.0 495,925 1

Hash (cost=21,454.17..21,454.17 rows=495,917 width=12) (actual time=249.257..249.257 rows=495,925 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3700kB
18. 148.351 148.351 ↓ 1.0 495,925 1

Seq Scan on regions_service_types rst (cost=0.00..21,454.17 rows=495,917 width=12) (actual time=0.011..148.351 rows=495,925 loops=1)

19. 0.712 3.669 ↓ 1.0 3,404 1

Hash (cost=741.93..741.93 rows=3,403 width=32) (actual time=3.669..3.669 rows=3,404 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 253kB
20. 1.636 2.957 ↓ 1.0 3,404 1

Hash Join (cost=1.11..741.93 rows=3,403 width=32) (actual time=0.050..2.957 rows=3,404 loops=1)

  • Hash Cond: (ro.country_id = c.id)
21. 1.303 1.303 ↓ 1.0 3,404 1

Seq Scan on regions ro (cost=0.00..694.03 rows=3,403 width=29) (actual time=0.010..1.303 rows=3,404 loops=1)

22. 0.009 0.018 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=11) (actual time=0.018..0.018 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.009 0.009 ↑ 1.0 5 1

Seq Scan on countries c (cost=0.00..1.05 rows=5 width=11) (actual time=0.009..0.009 rows=5 loops=1)

24. 0.856 3.104 ↓ 1.0 3,404 1

Hash (cost=694.03..694.03 rows=3,403 width=25) (actual time=3.104..3.104 rows=3,404 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 228kB
25. 2.248 2.248 ↓ 1.0 3,404 1

Seq Scan on regions rd (cost=0.00..694.03 rows=3,403 width=25) (actual time=0.018..2.248 rows=3,404 loops=1)

26. 75.502 1,270.133 ↑ 1.0 241,590 1

Hash (cost=100,544.38..100,544.38 rows=241,948 width=58) (actual time=1,270.133..1,270.133 rows=241,590 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3348kB
27. 195.654 1,194.631 ↑ 1.0 241,590 1

Hash Right Join (cost=94,060.13..100,544.38 rows=241,948 width=58) (actual time=947.807..1,194.631 rows=241,590 loops=1)

  • Hash Cond: ("Consulta SQL personalizada2"."Task ID" = tasks.id)
28. 9.668 740.607 ↑ 1.1 92,372 1

Subquery Scan on Consulta SQL personalizada2 (cost=64,858.30..66,597.80 rows=99,400 width=4) (actual time=689.129..740.607 rows=92,372 loops=1)

29. 17.852 730.939 ↑ 1.1 92,372 1

Group (cost=64,858.30..65,603.80 rows=99,400 width=34) (actual time=689.127..730.939 rows=92,372 loops=1)

  • Group Key: sh.name, t_1.id
30. 219.358 713.087 ↑ 1.0 98,964 1

Sort (cost=64,858.30..65,106.80 rows=99,400 width=34) (actual time=689.122..713.087 rows=98,964 loops=1)

  • Sort Key: sh.name, t_1.id
  • Sort Method: external merge Disk: 3928kB
31. 24.836 493.729 ↑ 1.0 98,964 1

Hash Join (cost=44,130.88..53,888.13 rows=99,400 width=34) (actual time=231.168..493.729 rows=98,964 loops=1)

  • Hash Cond: (so.hub_id = sh.id)
32. 115.207 468.679 ↑ 1.0 98,964 1

Hash Join (cost=44,107.07..52,497.57 rows=99,400 width=12) (actual time=230.938..468.679 rows=98,964 loops=1)

  • Hash Cond: (sot.sodimac_order_id = so.id)
33. 106.901 252.804 ↑ 1.0 98,964 1

Hash Join (cost=27,547.83..32,804.58 rows=99,400 width=12) (actual time=129.836..252.804 rows=98,964 loops=1)

  • Hash Cond: (sot.task_id = t_1.id)
34. 16.380 16.380 ↑ 1.0 98,975 1

Seq Scan on sodimac_order_tasks sot (cost=0.00..1,972.00 rows=99,400 width=12) (actual time=0.012..16.380 rows=98,975 loops=1)

35. 45.212 129.523 ↑ 1.0 241,587 1

Hash (cost=23,577.48..23,577.48 rows=241,948 width=4) (actual time=129.523..129.523 rows=241,587 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 3150kB
36. 84.311 84.311 ↑ 1.0 241,587 1

Seq Scan on tasks t_1 (cost=0.00..23,577.48 rows=241,948 width=4) (actual time=0.010..84.311 rows=241,587 loops=1)

37. 34.685 100.668 ↓ 1.0 163,099 1

Hash (cost=13,731.33..13,731.33 rows=162,633 width=16) (actual time=100.668..100.668 rows=163,099 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2935kB
38. 65.983 65.983 ↓ 1.0 163,099 1

Seq Scan on sodimac_orders so (cost=0.00..13,731.33 rows=162,633 width=16) (actual time=0.010..65.983 rows=163,099 loops=1)

39. 0.078 0.214 ↑ 1.1 590 1

Hash (cost=15.58..15.58 rows=658 width=38) (actual time=0.214..0.214 rows=590 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
40. 0.136 0.136 ↑ 1.1 590 1

Seq Scan on sodimac_hubs sh (cost=0.00..15.58 rows=658 width=38) (actual time=0.011..0.136 rows=590 loops=1)

41. 81.621 258.370 ↑ 1.0 241,587 1

Hash (cost=23,577.48..23,577.48 rows=241,948 width=58) (actual time=258.370..258.370 rows=241,587 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3348kB
42. 176.749 176.749 ↑ 1.0 241,587 1

Seq Scan on tasks (cost=0.00..23,577.48 rows=241,948 width=58) (actual time=0.018..176.749 rows=241,587 loops=1)

43. 12.478 42.459 ↓ 1.0 44,289 1

Hash (cost=4,267.26..4,267.26 rows=44,084 width=44) (actual time=42.459..42.459 rows=44,289 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3987kB
44. 17.076 29.981 ↓ 1.0 44,289 1

Hash Left Join (cost=25.26..4,267.26 rows=44,084 width=44) (actual time=0.334..29.981 rows=44,289 loops=1)

  • Hash Cond: (services.organization_id = organizations.id)
45. 12.602 12.602 ↓ 1.0 44,289 1

Seq Scan on services (cost=0.00..3,635.84 rows=44,084 width=28) (actual time=0.013..12.602 rows=44,289 loops=1)

46. 0.116 0.303 ↓ 1.0 549 1

Hash (cost=18.45..18.45 rows=545 width=24) (actual time=0.303..0.303 rows=549 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
47. 0.187 0.187 ↓ 1.0 549 1

Seq Scan on organizations (cost=0.00..18.45 rows=545 width=24) (actual time=0.013..0.187 rows=549 loops=1)

48. 0.002 0.022 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=24) (actual time=0.022..0.022 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.020 0.020 ↑ 1.0 4 1

Seq Scan on cancellation_task_causes (cost=0.00..1.04 rows=4 width=24) (actual time=0.017..0.020 rows=4 loops=1)

50. 7.287 1,304.893 ↓ 219.0 43,804 1

Hash (cost=111,557.82..111,557.82 rows=200 width=36) (actual time=1,304.893..1,304.893 rows=43,804 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2450kB
51. 5.402 1,297.606 ↓ 219.0 43,804 1

Subquery Scan on Consulta SQL personalizada3 (cost=111,555.32..111,557.82 rows=200 width=36) (actual time=1,281.194..1,297.606 rows=43,804 loops=1)

52. 27.349 1,292.204 ↓ 219.0 43,804 1

Sort (cost=111,555.32..111,555.82 rows=200 width=36) (actual time=1,281.192..1,292.204 rows=43,804 loops=1)

  • Sort Key: x.service DESC
  • Sort Method: quicksort Memory: 3419kB
53.          

CTE x

54. 102.009 1,161.792 ↑ 3.6 66,318 1

GroupAggregate (cost=100,051.15..104,890.11 rows=241,948 width=16) (actual time=1,002.473..1,161.792 rows=66,318 loops=1)

  • Group Key: t_2.region_service_type_id, s.id
55. 254.615 1,059.783 ↑ 1.0 234,815 1

Sort (cost=100,051.15..100,656.02 rows=241,948 width=8) (actual time=1,002.436..1,059.783 rows=234,815 loops=1)

  • Sort Key: t_2.region_service_type_id, s.id
  • Sort Method: external merge Disk: 4128kB
56. 97.811 805.168 ↑ 1.0 234,815 1

Hash Join (cost=33,972.59..75,104.75 rows=241,948 width=8) (actual time=269.943..805.168 rows=234,815 loops=1)

  • Hash Cond: (t_2.service_id = s.id)
57. 56.956 692.598 ↑ 1.0 234,816 1

Hash Join (cost=30,659.60..68,464.97 rows=241,948 width=8) (actual time=254.997..692.598 rows=234,816 loops=1)

  • Hash Cond: (rst_1.region_origin_id = origen.id)
58. 55.086 633.519 ↑ 1.0 234,816 1

Hash Join (cost=30,367.36..64,845.96 rows=241,948 width=12) (actual time=252.841..633.519 rows=234,816 loops=1)

  • Hash Cond: (rst_1.region_destiny_id = destino.id)
59. 160.480 576.387 ↑ 1.0 234,816 1

Hash Join (cost=30,075.13..61,226.94 rows=241,948 width=16) (actual time=250.762..576.387 rows=234,816 loops=1)

  • Hash Cond: (t_2.region_service_type_id = rst_1.id)
60. 165.464 165.464 ↑ 1.0 241,587 1

Seq Scan on tasks t_2 (cost=0.00..23,577.48 rows=241,948 width=8) (actual time=0.019..165.464 rows=241,587 loops=1)

61. 101.126 250.443 ↓ 1.0 495,925 1

Hash (cost=21,454.17..21,454.17 rows=495,917 width=12) (actual time=250.443..250.443 rows=495,925 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3700kB
62. 149.317 149.317 ↓ 1.0 495,925 1

Seq Scan on regions_service_types rst_1 (cost=0.00..21,454.17 rows=495,917 width=12) (actual time=0.011..149.317 rows=495,925 loops=1)

63. 0.397 2.046 ↓ 1.0 3,404 1

Hash (cost=249.70..249.70 rows=3,403 width=8) (actual time=2.046..2.046 rows=3,404 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 165kB
64. 1.006 1.649 ↓ 1.0 3,404 1

Merge Join (cost=0.56..249.70 rows=3,403 width=8) (actual time=0.024..1.649 rows=3,404 loops=1)

  • Merge Cond: (destino.id = origen1.id)
65. 0.310 0.310 ↓ 1.0 3,404 1

Index Only Scan using regions_pkey on regions destino (cost=0.28..99.33 rows=3,403 width=4) (actual time=0.014..0.310 rows=3,404 loops=1)

  • Heap Fetches: 15
66. 0.333 0.333 ↓ 1.0 3,404 1

Index Only Scan using regions_pkey on regions origen1 (cost=0.28..99.33 rows=3,403 width=4) (actual time=0.007..0.333 rows=3,404 loops=1)

  • Heap Fetches: 15
67. 0.381 2.123 ↓ 1.0 3,404 1

Hash (cost=249.70..249.70 rows=3,403 width=8) (actual time=2.123..2.123 rows=3,404 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 165kB
68. 1.087 1.742 ↓ 1.0 3,404 1

Merge Join (cost=0.56..249.70 rows=3,403 width=8) (actual time=0.036..1.742 rows=3,404 loops=1)

  • Merge Cond: (origen.id = destino1.id)
69. 0.319 0.319 ↓ 1.0 3,404 1

Index Only Scan using regions_pkey on regions origen (cost=0.28..99.33 rows=3,403 width=4) (actual time=0.021..0.319 rows=3,404 loops=1)

  • Heap Fetches: 15
70. 0.336 0.336 ↓ 1.0 3,404 1

Index Only Scan using regions_pkey on regions destino1 (cost=0.28..99.33 rows=3,403 width=4) (actual time=0.009..0.336 rows=3,404 loops=1)

  • Heap Fetches: 15
71. 6.984 14.759 ↓ 1.0 44,289 1

Hash (cost=2,761.94..2,761.94 rows=44,084 width=4) (actual time=14.759..14.759 rows=44,289 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2070kB
72. 7.775 7.775 ↓ 1.0 44,289 1

Index Only Scan using services_pkey on services s (cost=0.29..2,761.94 rows=44,084 width=4) (actual time=0.025..7.775 rows=44,289 loops=1)

  • Heap Fetches: 4559
73. 85.779 1,264.855 ↓ 219.0 43,804 1

HashAggregate (cost=6,653.57..6,657.57 rows=200 width=36) (actual time=1,235.408..1,264.855 rows=43,804 loops=1)

  • Group Key: x.service
74. 1,179.076 1,179.076 ↑ 3.6 66,318 1

CTE Scan on x (cost=0.00..4,838.96 rows=241,948 width=12) (actual time=1,002.477..1,179.076 rows=66,318 loops=1)

75. 0.183 1.318 ↑ 1.0 1,613 1

Hash (cost=101.87..101.87 rows=1,613 width=11) (actual time=1.318..1.318 rows=1,613 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 84kB
76. 0.402 1.135 ↑ 1.0 1,613 1

Hash Left Join (cost=49.56..101.87 rows=1,613 width=11) (actual time=0.516..1.135 rows=1,613 loops=1)

  • Hash Cond: (workers_vehicles.vehicle_id = vehicles.id)
77. 0.257 0.257 ↑ 1.0 1,613 1

Seq Scan on workers_vehicles (cost=0.00..30.13 rows=1,613 width=12) (actual time=0.026..0.257 rows=1,613 loops=1)

78. 0.154 0.476 ↓ 1.0 1,315 1

Hash (cost=33.14..33.14 rows=1,314 width=11) (actual time=0.476..0.476 rows=1,315 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
79. 0.322 0.322 ↓ 1.0 1,315 1

Seq Scan on vehicles (cost=0.00..33.14 rows=1,314 width=11) (actual time=0.010..0.322 rows=1,315 loops=1)

80. 0.020 0.074 ↑ 1.0 117 1

Hash (cost=8.17..8.17 rows=117 width=27) (actual time=0.074..0.074 rows=117 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
81. 0.054 0.054 ↑ 1.0 117 1

Seq Scan on service_types (cost=0.00..8.17 rows=117 width=27) (actual time=0.016..0.054 rows=117 loops=1)