explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ecl9

Settings
# exclusive inclusive rows x rows loops node
1. 146.383 4,932.013 ↓ 1.0 240,774 1

Hash Left Join (cost=251,729.86..360,657.45 rows=236,641 width=261) (actual time=2,834.143..4,932.013 rows=240,774 loops=1)

  • Hash Cond: (services.service_type_id = service_types.id)
2. 84.432 4,785.529 ↓ 1.0 240,774 1

Hash Left Join (cost=251,720.22..356,210.80 rows=236,641 width=258) (actual time=2,829.409..4,785.529 rows=240,774 loops=1)

  • Hash Cond: (services.worker_vehicle_id = workers_vehicles.id)
3. 71.145 4,699.315 ↓ 1.0 240,774 1

Hash Left Join (cost=251,599.53..353,110.56 rows=236,641 width=255) (actual time=2,827.604..4,699.315 rows=240,774 loops=1)

  • Hash Cond: (services.organization_id = organizations.id)
4. 178.771 4,627.838 ↓ 1.0 240,774 1

Hash Left Join (cost=251,574.51..349,831.73 rows=236,641 width=239) (actual time=2,827.248..4,627.838 rows=240,774 loops=1)

  • Hash Cond: (services.id = "Consulta SQL personalizada3".service)
5. 406.845 3,146.136 ↓ 1.0 240,774 1

Hash Left Join (cost=144,687.02..236,425.64 rows=236,641 width=211) (actual time=1,524.268..3,146.136 rows=240,774 loops=1)

  • Hash Cond: (tasks.id = tasks_1.id)
6. 250.958 2,519.545 ↓ 1.0 240,774 1

Hash Left Join (cost=118,297.39..188,498.08 rows=236,423 width=183) (actual time=1,303.944..2,519.545 rows=240,774 loops=1)

  • Hash Cond: (tasks.id = t_1.id)
7. 45.996 1,710.901 ↑ 1.0 234,403 1

Hash Left Join (cost=63,173.29..117,238.03 rows=236,423 width=153) (actual time=745.639..1,710.901 rows=234,403 loops=1)

  • Hash Cond: (tasks.cancellation_task_cause_id = cancellation_task_causes.id)
8. 169.195 1,664.880 ↑ 1.0 234,403 1

Hash Left Join (cost=63,172.20..116,326.55 rows=236,423 width=145) (actual time=745.541..1,664.880 rows=234,403 loops=1)

  • Hash Cond: (tasks.service_id = services.id)
9. 317.543 1,453.006 ↑ 1.0 234,403 1

Hash Right Join (cost=59,006.10..108,909.63 rows=236,423 width=121) (actual time=702.278..1,453.006 rows=234,403 loops=1)

  • Hash Cond: (t.id = tasks.id)
10. 56.597 722.036 ↑ 1.0 227,827 1

Hash Join (cost=31,001.47..66,535.13 rows=236,206 width=32) (actual time=288.527..722.036 rows=227,827 loops=1)

  • Hash Cond: (rst.region_destiny_id = rd.id)
11. 60.458 664.429 ↑ 1.0 227,827 1

Hash Join (cost=30,859.60..63,145.44 rows=236,206 width=36) (actual time=287.455..664.429 rows=227,827 loops=1)

  • Hash Cond: (rst.region_origin_id = ro.id)
12. 147.631 599.490 ↑ 1.0 227,827 1

Hash Join (cost=30,075.13..59,113.14 rows=236,206 width=12) (actual time=282.907..599.490 rows=227,827 loops=1)

  • Hash Cond: (t.region_service_type_id = rst.id)
13. 170.111 170.111 ↑ 1.0 234,403 1

Seq Scan on tasks t (cost=0.00..21,591.06 rows=236,206 width=8) (actual time=0.013..170.111 rows=234,403 loops=1)

14. 106.238 281.748 ↓ 1.0 495,925 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 3700kB
15. 175.510 175.510 ↓ 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.020..175.510 rows=495,925 loops=1)

16. 0.793 4.481 ↓ 1.0 3,404 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 253kB
17. 1.402 3.688 ↓ 1.0 3,404 1

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

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

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

19. 0.011 0.022 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.011 0.011 ↑ 1.0 5 1

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

21. 0.548 1.010 ↓ 1.0 3,404 1

Hash (cost=99.33..99.33 rows=3,403 width=4) (actual time=1.010..1.010 rows=3,404 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 152kB
22. 0.462 0.462 ↓ 1.0 3,404 1

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

  • Heap Fetches: 15
23. 112.510 413.427 ↑ 1.0 234,403 1

Hash (cost=21,591.06..21,591.06 rows=236,206 width=93) (actual time=413.427..413.427 rows=234,403 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 3783kB
24. 300.917 300.917 ↑ 1.0 234,403 1

Seq Scan on tasks (cost=0.00..21,591.06 rows=236,206 width=93) (actual time=0.040..300.917 rows=234,403 loops=1)

25. 15.076 42.679 ↓ 1.0 43,232 1

Hash (cost=3,626.60..3,626.60 rows=43,160 width=28) (actual time=42.679..42.679 rows=43,232 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3012kB
26. 27.603 27.603 ↓ 1.0 43,232 1

Seq Scan on services (cost=0.00..3,626.60 rows=43,160 width=28) (actual time=0.021..27.603 rows=43,232 loops=1)

27. 0.011 0.025 ↑ 1.0 4 1

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

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

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

29. 34.419 557.686 ↓ 1.0 95,532 1

Hash (cost=53,219.40..53,219.40 rows=93,736 width=34) (actual time=557.686..557.686 rows=95,532 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3438kB
30. 25.847 523.267 ↓ 1.0 95,532 1

Hash Join (cost=19,978.63..53,219.40 rows=93,736 width=34) (actual time=161.593..523.267 rows=95,532 loops=1)

  • Hash Cond: (so.hub_id = sh.id)
31. 124.374 497.148 ↓ 1.0 95,532 1

Hash Join (cost=19,957.47..51,909.37 rows=93,736 width=12) (actual time=161.286..497.148 rows=95,532 loops=1)

  • Hash Cond: (sot.sodimac_order_id = so.id)
32. 129.204 253.449 ↓ 1.0 95,532 1

Hash Join (cost=3,476.06..32,442.09 rows=93,736 width=12) (actual time=40.790..253.449 rows=95,532 loops=1)

  • Hash Cond: (t_1.id = sot.task_id)
33. 84.546 84.546 ↑ 1.0 234,403 1

Seq Scan on tasks t_1 (cost=0.00..21,591.06 rows=236,206 width=4) (actual time=0.009..84.546 rows=234,403 loops=1)

34. 23.908 39.699 ↓ 1.0 95,543 1

Hash (cost=1,846.36..1,846.36 rows=93,736 width=12) (actual time=39.699..39.699 rows=95,543 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3262kB
35. 15.791 15.791 ↓ 1.0 95,543 1

Seq Scan on sodimac_order_tasks sot (cost=0.00..1,846.36 rows=93,736 width=12) (actual time=0.013..15.791 rows=95,543 loops=1)

36. 38.985 119.325 ↓ 1.0 159,898 1

Hash (cost=13,702.96..13,702.96 rows=159,796 width=16) (actual time=119.325..119.325 rows=159,898 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2897kB
37. 80.340 80.340 ↓ 1.0 159,898 1

Seq Scan on sodimac_orders so (cost=0.00..13,702.96 rows=159,796 width=16) (actual time=0.012..80.340 rows=159,898 loops=1)

38. 0.128 0.272 ↑ 1.0 585 1

Hash (cost=13.85..13.85 rows=585 width=38) (actual time=0.272..0.272 rows=585 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
39. 0.144 0.144 ↑ 1.0 585 1

Seq Scan on sodimac_hubs sh (cost=0.00..13.85 rows=585 width=38) (actual time=0.013..0.144 rows=585 loops=1)

40. 66.155 219.746 ↑ 1.0 234,403 1

Hash (cost=21,591.06..21,591.06 rows=236,206 width=36) (actual time=219.746..219.746 rows=234,403 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2461kB
41. 153.591 153.591 ↑ 1.0 234,403 1

Seq Scan on tasks tasks_1 (cost=0.00..21,591.06 rows=236,206 width=36) (actual time=0.028..153.591 rows=234,403 loops=1)

42. 7.759 1,302.931 ↓ 213.8 42,763 1

Hash (cost=106,884.99..106,884.99 rows=200 width=36) (actual time=1,302.931..1,302.931 rows=42,763 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2404kB
43. 5.504 1,295.172 ↓ 213.8 42,763 1

Subquery Scan on Consulta SQL personalizada3 (cost=106,882.49..106,884.99 rows=200 width=36) (actual time=1,278.193..1,295.172 rows=42,763 loops=1)

44. 27.762 1,289.668 ↓ 213.8 42,763 1

Sort (cost=106,882.49..106,882.99 rows=200 width=36) (actual time=1,278.190..1,289.668 rows=42,763 loops=1)

  • Sort Key: x.service DESC
  • Sort Method: quicksort Memory: 3370kB
45.          

CTE x

46. 95.381 1,158.049 ↑ 3.6 64,725 1

GroupAggregate (cost=95,651.06..100,375.18 rows=236,206 width=16) (actual time=1,005.915..1,158.049 rows=64,725 loops=1)

  • Group Key: t_2.region_service_type_id, s.id
47. 258.227 1,062.668 ↑ 1.0 227,826 1

Sort (cost=95,651.06..96,241.58 rows=236,206 width=8) (actual time=1,005.870..1,062.668 rows=227,826 loops=1)

  • Sort Key: t_2.region_service_type_id, s.id
  • Sort Method: external merge Disk: 4000kB
48. 96.702 804.441 ↑ 1.0 227,826 1

Hash Join (cost=32,558.05..71,339.55 rows=236,206 width=8) (actual time=278.451..804.441 rows=227,826 loops=1)

  • Hash Cond: (t_2.service_id = s.id)
49. 54.212 691.551 ↑ 1.0 227,827 1

Hash Join (cost=30,358.86..65,892.52 rows=236,206 width=8) (actual time=261.734..691.551 rows=227,827 loops=1)

  • Hash Cond: (rst_1.region_destiny_id = origen1.id)
50. 54.464 636.440 ↑ 1.0 227,827 1

Hash Join (cost=30,216.99..62,502.83 rows=236,206 width=12) (actual time=260.771..636.440 rows=227,827 loops=1)

  • Hash Cond: (rst_1.region_origin_id = origen.id)
51. 157.361 581.129 ↑ 1.0 227,827 1

Hash Join (cost=30,075.13..59,113.14 rows=236,206 width=16) (actual time=259.868..581.129 rows=227,827 loops=1)

  • Hash Cond: (t_2.region_service_type_id = rst_1.id)
52. 164.939 164.939 ↑ 1.0 234,403 1

Seq Scan on tasks t_2 (cost=0.00..21,591.06 rows=236,206 width=8) (actual time=0.014..164.939 rows=234,403 loops=1)

53. 101.788 258.829 ↓ 1.0 495,925 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 3700kB
54. 157.041 157.041 ↓ 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.009..157.041 rows=495,925 loops=1)

55. 0.523 0.847 ↓ 1.0 3,404 1

Hash (cost=99.33..99.33 rows=3,403 width=4) (actual time=0.847..0.847 rows=3,404 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 152kB
56. 0.324 0.324 ↓ 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.018..0.324 rows=3,404 loops=1)

  • Heap Fetches: 15
57. 0.513 0.899 ↓ 1.0 3,404 1

Hash (cost=99.33..99.33 rows=3,403 width=4) (actual time=0.899..0.899 rows=3,404 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 152kB
58. 0.386 0.386 ↓ 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.025..0.386 rows=3,404 loops=1)

  • Heap Fetches: 15
59. 8.892 16.188 ↓ 1.0 43,232 1

Hash (cost=1,659.69..1,659.69 rows=43,160 width=4) (actual time=16.188..16.188 rows=43,232 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2032kB
60. 7.296 7.296 ↓ 1.0 43,232 1

Index Only Scan using services_pkey on services s (cost=0.29..1,659.69 rows=43,160 width=4) (actual time=0.065..7.296 rows=43,232 loops=1)

  • Heap Fetches: 1614
61. 86.341 1,261.906 ↓ 213.8 42,763 1

HashAggregate (cost=6,495.66..6,499.66 rows=200 width=36) (actual time=1,231.957..1,261.906 rows=42,763 loops=1)

  • Group Key: x.service
62. 1,175.565 1,175.565 ↑ 3.6 64,725 1

CTE Scan on x (cost=0.00..4,724.12 rows=236,206 width=12) (actual time=1,005.920..1,175.565 rows=64,725 loops=1)

63. 0.131 0.332 ↓ 1.0 542 1

Hash (cost=18.34..18.34 rows=534 width=24) (actual time=0.332..0.332 rows=542 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
64. 0.201 0.201 ↓ 1.0 542 1

Seq Scan on organizations (cost=0.00..18.34 rows=534 width=24) (actual time=0.045..0.201 rows=542 loops=1)

65. 0.297 1.782 ↓ 1.0 1,577 1

Hash (cost=100.99..100.99 rows=1,576 width=11) (actual time=1.782..1.782 rows=1,577 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
66. 0.437 1.485 ↓ 1.0 1,577 1

Hash Left Join (cost=49.56..100.99 rows=1,576 width=11) (actual time=0.798..1.485 rows=1,577 loops=1)

  • Hash Cond: (workers_vehicles.vehicle_id = vehicles.id)
67. 0.276 0.276 ↓ 1.0 1,577 1

Seq Scan on workers_vehicles (cost=0.00..29.76 rows=1,576 width=12) (actual time=0.014..0.276 rows=1,577 loops=1)

68. 0.238 0.772 ↑ 1.0 1,290 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 72kB
69. 0.534 0.534 ↑ 1.0 1,290 1

Seq Scan on vehicles (cost=0.00..33.14 rows=1,314 width=11) (actual time=0.019..0.534 rows=1,290 loops=1)

70. 0.046 0.101 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
71. 0.055 0.055 ↑ 1.0 117 1

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