explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cpdw

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.799 8,628.812 ↑ 2.6 385 1

Subquery Scan on wanted (cost=862.56..2,120,108.60 rows=1,000 width=193) (actual time=11.107..8,628.812 rows=385 loops=1)

2.          

CTE providers_near

3. 0.906 9.654 ↓ 4.0 4 1

Nested Loop Left Join (cost=785.60..855.62 rows=1 width=36) (actual time=7.312..9.654 rows=4 loops=1)

  • Join Filter: (user_provider.user_uid = (booking.provider[1]))
  • Rows Removed by Join Filter: 246
4. 0.051 2.096 ↓ 4.0 4 1

Nested Loop Left Join (cost=0.70..63.15 rows=1 width=52) (actual time=0.400..2.096 rows=4 loops=1)

  • Join Filter: (user_provider.user_uid = portfolio_1.user_uid)
  • Rows Removed by Join Filter: 143
  • Filter: (((CASE WHEN ('banned'::text = ANY (array_agg(portfolio_1.level))) THEN 2 WHEN ('top'::text = ANY (array_agg(portfolio_1.level))) THEN 0 WHEN ('default'::text = ANY (array_agg(portfolio_1.level))) THEN 1 ELSE NULL::integer END) IS NULL) OR ((CASE WHEN ('banned'::text = ANY (array_agg(portfolio_1.level))) THEN 2 WHEN ('top'::text = ANY (array_agg(portfolio_1.level))) THEN 0 WHEN ('default'::text = ANY (array_agg(portfolio_1.level))) THEN 1 ELSE NULL::integer END) <> 2))
5. 0.019 0.473 ↓ 4.0 4 1

Nested Loop (cost=0.42..8.56 rows=1 width=48) (actual time=0.238..0.473 rows=4 loops=1)

6. 0.332 0.410 ↓ 4.0 4 1

Index Scan using user_provider_service_area_idx on user_provider (cost=0.14..6.25 rows=1 width=16) (actual time=0.219..0.410 rows=4 loops=1)

  • Index Cond: (st_flipcoordinates(service_area) ~ '0101000020E6100000622D3E0540925EC0F7297DD756C94240'::geometry)
  • Filter: (accept_bookings AND ((status)::text = 'active'::text) AND _st_contains(st_flipcoordinates(service_area), '0101000020E6100000622D3E0540925EC0F7297DD756C94240'::geometry) AND ((SubPlan 1) IS NOT NULL))
  • Rows Removed by Filter: 62
7.          

SubPlan (for Index Scan)

8. 0.012 0.078 ↑ 1.0 1 6

Limit (cost=0.28..3.84 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=6)

9. 0.066 0.066 ↑ 2.0 1 6

Index Scan using portfolio_pkey on portfolio (cost=0.28..7.41 rows=2 width=4) (actual time=0.011..0.011 rows=1 loops=6)

  • Index Cond: (user_uid = user_provider.user_uid)
  • Filter: (array_length(images, 1) > 0)
10. 0.044 0.044 ↑ 1.0 1 4

Index Scan using user_pkey on "user" (cost=0.28..2.30 rows=1 width=48) (actual time=0.011..0.011 rows=1 loops=4)

  • Index Cond: (uid = user_provider.user_uid)
11. 1.076 1.572 ↑ 1.4 36 4

GroupAggregate (cost=0.28..53.37 rows=49 width=20) (actual time=0.043..0.393 rows=36 loops=4)

  • Group Key: portfolio_1.user_uid
12. 0.496 0.496 ↑ 1.5 37 4

Index Scan using portfolio_pkey on portfolio portfolio_1 (cost=0.28..50.11 rows=57 width=23) (actual time=0.028..0.124 rows=37 loops=4)

  • Index Cond: ((code)::text = 'realestate'::text)
13. 3.384 6.652 ↑ 3.0 62 4

HashAggregate (cost=784.90..787.70 rows=187 width=96) (actual time=1.508..1.663 rows=62 loops=4)

  • Group Key: booking.provider[1]
14. 3.268 3.268 ↓ 1.1 655 1

Index Scan using booking_status_rating_service_partial_index on booking (cost=0.28..771.76 rows=584 width=64) (actual time=0.050..3.268 rows=655 loops=1)

  • Index Cond: ((status)::text = ANY ('{paid,completed,refunded,cancelled}'::text[]))
  • Filter: (((rating_product ->> 'value'::text))::numeric > '0'::numeric)
  • Rows Removed by Filter: 5
15.          

CTE related_specialist_providers_near

16. 0.005 0.166 ↓ 3.0 3 1

Group (cost=6.92..6.93 rows=1 width=16) (actual time=0.153..0.166 rows=3 loops=1)

  • Group Key: providers_near.user_uid
17. 0.012 0.161 ↓ 3.0 3 1

Sort (cost=6.92..6.92 rows=1 width=16) (actual time=0.152..0.161 rows=3 loops=1)

  • Sort Key: providers_near.user_uid
  • Sort Method: quicksort Memory: 25kB
18. 0.008 0.149 ↓ 3.0 3 1

Nested Loop (cost=5.83..6.91 rows=1 width=16) (actual time=0.124..0.149 rows=3 loops=1)

19. 0.002 0.105 ↑ 1.0 1 1

Group (cost=5.56..5.56 rows=1 width=8) (actual time=0.100..0.105 rows=1 loops=1)

  • Group Key: st2.name
20. 0.012 0.103 ↑ 1.0 1 1

Sort (cost=5.56..5.56 rows=1 width=8) (actual time=0.098..0.103 rows=1 loops=1)

  • Sort Key: st2.name
  • Sort Method: quicksort Memory: 25kB
21. 0.040 0.091 ↑ 1.0 1 1

Hash Join (cost=2.17..5.55 rows=1 width=8) (actual time=0.079..0.091 rows=1 loops=1)

  • Hash Cond: (st2."group" = shoottype."group")
  • Join Filter: (CASE WHEN shoottype.is_specialist_required THEN shoottype.name ELSE st2.name END = st2.name)
  • Rows Removed by Join Filter: 4
22. 0.022 0.022 ↑ 1.0 30 1

Seq Scan on shoottype st2 (cost=0.00..3.30 rows=30 width=10) (actual time=0.012..0.022 rows=30 loops=1)

23. 0.007 0.029 ↑ 1.0 1 1

Hash (cost=2.16..2.16 rows=1 width=11) (actual time=0.027..0.029 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.022 0.022 ↑ 1.0 1 1

Index Scan using shoottype_pkey on shoottype (cost=0.14..2.16 rows=1 width=11) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: (name = 'realestate'::text)
25. 0.010 0.036 ↓ 3.0 3 1

Nested Loop (cost=0.28..1.33 rows=1 width=24) (actual time=0.018..0.036 rows=3 loops=1)

26. 0.002 0.002 ↓ 4.0 4 1

CTE Scan on providers_near (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.002 rows=4 loops=1)

27. 0.024 0.024 ↑ 1.0 1 4

Index Only Scan using portfolio_pkey on portfolio portfolio_2 (cost=0.28..1.29 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=4)

  • Index Cond: ((user_uid = providers_near.user_uid) AND (code = st2.name))
  • Heap Fetches: 0
28. 9.253 8,612.848 ↑ 2.6 385 1

Function Scan on generate_series timeslot (cost=0.01..2,113,728.50 rows=1,000 width=65) (actual time=11.077..8,612.848 rows=385 loops=1)

29.          

SubPlan (for Function Scan)

30. 1.540 55.825 ↓ 0.0 0 385

Limit (cost=0.14..3.45 rows=1 width=16) (actual time=0.145..0.145 rows=0 loops=385)

31. 3.465 54.285 ↓ 0.0 0 385

Nested Loop (cost=0.14..3.45 rows=1 width=16) (actual time=0.141..0.141 rows=0 loops=385)

  • Join Filter: (location.uid = location_exception.location_uid)
32. 47.355 47.355 ↑ 1.0 1 385

Index Scan using location_polygon_idx on location (cost=0.14..2.42 rows=1 width=16) (actual time=0.121..0.123 rows=1 loops=385)

  • Index Cond: (st_flipcoordinates(polygon) && '0101000020E6100000622D3E0540925EC0F7297DD756C94240'::geometry)
  • Filter: _st_intersects('0101000020E6100000622D3E0540925EC0F7297DD756C94240'::geometry, st_flipcoordinates(polygon))
33. 3.465 3.465 ↓ 0.0 0 385

Seq Scan on location_exception (cost=0.00..1.02 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=385)

  • Filter: (tstzrange(start_at, end_at, '[]'::text) && tstzrange(timeslot.timeslot, (timeslot.timeslot + ('120 minute'::cstring)::interval), '[]'::text))
  • Rows Removed by Filter: 1
34. 7.510 8,547.770 ↑ 1.5 2 385

Function Scan on unnest uid_4 (cost=2,110.01..2,110.23 rows=3 width=16) (actual time=22.200..22.202 rows=2 loops=385)

  • Filter: (uid <> ALL ($16))
35.          

Initplan (for Function Scan)

36. 2.640 8,427.840 ↓ 0.0 0 240

HashAggregate (cost=1,749.40..1,749.48 rows=8 width=16) (actual time=35.116..35.116 rows=0 loops=240)

  • Group Key: (booking_1.provider[1])
37. 3.120 8,425.200 ↓ 0.0 0 240

Append (cost=0.28..1,749.38 rows=8 width=16) (actual time=35.105..35.105 rows=0 loops=240)

38. 4.320 8,375.280 ↓ 0.0 0 240

Nested Loop (cost=0.28..1,295.88 rows=1 width=16) (actual time=34.897..34.897 rows=0 loops=240)

  • Join Filter: (providers_near_3.user_uid = ANY (booking_1.provider))
  • Rows Removed by Join Filter: 0
39. 4.560 4.560 ↓ 4.0 4 240

CTE Scan on providers_near providers_near_3 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.019 rows=4 loops=240)

40. 8,366.400 8,366.400 ↓ 0.0 0 960

Index Scan using paid_booking_coupon_uid_idx on booking booking_1 (cost=0.28..1,295.64 rows=10 width=37) (actual time=8.376..8.715 rows=0 loops=960)

  • Filter: ((uid <> ALL ('{32b8641a-72c5-4988-a9a4-eb432b4f75c0}'::uuid[])) AND ((status)::text = 'paid'::text) AND (tstzrange(timeslot.timeslot, (timeslot.timeslot + ('120 minute'::cstring)::interval), '[]'::text) && tstzrange(to_timestamp((starttime)::double precision), (to_timestamp((starttime)::double precision) + (((package)::text || ' minute'::text))::interval), '[]'::text)))
  • Rows Removed by Filter: 5,269
41. 6.480 36.000 ↓ 0.0 0 240

Nested Loop (cost=0.00..1.77 rows=1 width=16) (actual time=0.150..0.150 rows=0 loops=240)

  • Join Filter: (providers_near_4.user_uid = provider_exceptions.user_uid)
42. 0.720 0.720 ↓ 4.0 4 240

CTE Scan on providers_near providers_near_4 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.003 rows=4 loops=240)

43. 28.800 28.800 ↓ 0.0 0 960

Seq Scan on provider_exceptions (cost=0.00..1.73 rows=1 width=16) (actual time=0.030..0.030 rows=0 loops=960)

  • Filter: (tstzrange(timeslot.timeslot, (timeslot.timeslot + ('120 minute'::cstring)::interval), '[]'::text) && tstzrange(to_timestamp((start)::double precision), to_timestamp(("end")::double precision), '(]'::text))
  • Rows Removed by Filter: 21
44. 1.920 10.800 ↓ 0.0 0 240

Nested Loop (cost=0.28..451.61 rows=6 width=16) (actual time=0.045..0.045 rows=0 loops=240)

45. 0.240 0.240 ↓ 4.0 4 240

CTE Scan on providers_near providers_near_5 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.001 rows=4 loops=240)

46. 8.640 8.640 ↓ 0.0 0 960

Index Scan using provider_external_exception_user_provider_uid_idx on provider_external_exception (cost=0.28..451.53 rows=6 width=16) (actual time=0.009..0.009 rows=0 loops=960)

  • Index Cond: (user_provider_uid = providers_near_5.user_uid)
  • Filter: (block_availability AND (tstzrange(timeslot.timeslot, (timeslot.timeslot + ('120 minute'::cstring)::interval), '[]'::text) && tstzrange(start_at, end_at, '(]'::text)))
47. 8.085 112.420 ↑ 3.0 2 385

Sort (cost=360.50..360.52 rows=6 width=28) (actual time=0.291..0.292 rows=2 loops=385)

  • Sort Key: providers_near_6.portfolio_level, providers_near_6.quality DESC
  • Sort Method: quicksort Memory: 25kB
48. 2.695 104.335 ↑ 3.0 2 385

Result (cost=0.31..360.43 rows=6 width=28) (actual time=0.191..0.271 rows=2 loops=385)

  • One-Time Filter: (lower(tstzrange(timeslot.timeslot, (timeslot.timeslot + ('120 minute'::cstring)::interval), '[]'::text)) > (now() + ('120 minute'::cstring)::interval))
49. 86.240 101.640 ↑ 3.0 2 385

Nested Loop (cost=0.31..360.43 rows=6 width=28) (actual time=0.185..0.264 rows=2 loops=385)

  • Join Filter: (tstzrange(timeslot.timeslot, (timeslot.timeslot + ('120 minute'::cstring)::interval), '[]'::text) <@ tstzrange((week_start.week_start + (((provider_availability.start)::text || ' second'::text))::interval), (week_start.week_start + (((provider_availability."end")::text || ' second'::text))::interval), '[]'::text))
  • Rows Removed by Join Filter: 73
50. 0.385 0.385 ↑ 333.3 3 385

Function Scan on generate_series week_start (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.001..0.001 rows=3 loops=385)

51. 5.252 15.015 ↓ 4.2 25 1,155

Materialize (cost=0.28..5.41 rows=6 width=36) (actual time=0.007..0.013 rows=25 loops=1,155)

52. 0.030 9.763 ↓ 4.2 25 1

Nested Loop (cost=0.28..5.38 rows=6 width=36) (actual time=7.340..9.763 rows=25 loops=1)

53. 9.661 9.661 ↓ 4.0 4 1

CTE Scan on providers_near providers_near_6 (cost=0.00..0.02 rows=1 width=28) (actual time=7.317..9.661 rows=4 loops=1)

54. 0.072 0.072 ↑ 1.0 6 4

Index Scan using provider_availability_user_uid_index on provider_availability (cost=0.28..5.30 rows=6 width=24) (actual time=0.012..0.018 rows=6 loops=4)

  • Index Cond: (user_uid = providers_near_6.user_uid)
55.          

SubPlan (for Subquery Scan)

56. 3.378 8.085 ↓ 2.0 2 385

Hash Join (cost=1.39..1.53 rows=1 width=16) (actual time=0.019..0.021 rows=2 loops=385)

  • Hash Cond: (uid.uid = providers_near_1.user_uid)
57. 4.620 4.620 ↑ 5.0 2 385

Function Scan on unnest uid (cost=0.00..0.10 rows=10 width=16) (actual time=0.012..0.012 rows=2 loops=385)

58. 0.008 0.087 ↓ 3.0 3 1

Hash (cost=1.38..1.38 rows=1 width=16) (actual time=0.083..0.087 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
59. 0.007 0.079 ↓ 3.0 3 1

Group (cost=1.36..1.37 rows=1 width=16) (actual time=0.072..0.079 rows=3 loops=1)

  • Group Key: providers_near_1.user_uid
60. 0.020 0.072 ↓ 3.0 3 1

Sort (cost=1.36..1.37 rows=1 width=16) (actual time=0.069..0.072 rows=3 loops=1)

  • Sort Key: providers_near_1.user_uid
  • Sort Method: quicksort Memory: 25kB
61. 0.009 0.052 ↓ 3.0 3 1

Nested Loop (cost=0.28..1.35 rows=1 width=16) (actual time=0.033..0.052 rows=3 loops=1)

62. 0.003 0.003 ↓ 4.0 4 1

CTE Scan on providers_near providers_near_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.003 rows=4 loops=1)

63. 0.040 0.040 ↑ 1.0 1 4

Index Only Scan using portfolio_pkey on portfolio portfolio_3 (cost=0.28..1.29 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=4)

  • Index Cond: ((user_uid = providers_near_1.user_uid) AND (code = 'realestate'::text))
  • Heap Fetches: 0
64. 1.758 3.080 ↓ 2.0 2 385

Hash Join (cost=0.04..0.18 rows=1 width=16) (actual time=0.006..0.008 rows=2 loops=385)

  • Hash Cond: (uid_1.uid = related_specialist_providers_near.user_uid)
65. 1.155 1.155 ↑ 5.0 2 385

Function Scan on unnest uid_1 (cost=0.00..0.10 rows=10 width=16) (actual time=0.002..0.003 rows=2 loops=385)

66. 0.006 0.167 ↓ 3.0 3 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.166..0.167 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
67. 0.161 0.161 ↓ 3.0 3 1

CTE Scan on related_specialist_providers_near (cost=0.00..0.02 rows=1 width=16) (actual time=0.156..0.161 rows=3 loops=1)

68. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.32..1.90 rows=1 width=16) (never executed)

  • Join Filter: (providers_near_2.user_uid = user_provider_1.user_uid)
69. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.05..0.20 rows=1 width=32) (never executed)

  • Hash Cond: (uid_2.uid = providers_near_2.user_uid)
70. 0.000 0.000 ↓ 0.0 0

Function Scan on unnest uid_2 (cost=0.00..0.10 rows=10 width=16) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.03..0.03 rows=1 width=16) (never executed)

72. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.02..0.03 rows=1 width=16) (never executed)

  • Group Key: providers_near_2.user_uid
73. 0.000 0.000 ↓ 0.0 0

CTE Scan on providers_near providers_near_2 (cost=0.00..0.02 rows=1 width=16) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Index Scan using user_provider_user_uid_index on user_provider user_provider_1 (cost=0.27..1.69 rows=1 width=16) (never executed)

  • Index Cond: (user_uid = uid_2.uid)
  • Filter: accepting_short_notice_bookings
75. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.32..1.90 rows=1 width=16) (never executed)

  • Join Filter: (related_specialist_providers_near_1.user_uid = user_provider_2.user_uid)
76. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.05..0.20 rows=1 width=32) (never executed)

  • Hash Cond: (uid_3.uid = related_specialist_providers_near_1.user_uid)
77. 0.000 0.000 ↓ 0.0 0

Function Scan on unnest uid_3 (cost=0.00..0.10 rows=10 width=16) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.03..0.03 rows=1 width=16) (never executed)

79. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.02..0.03 rows=1 width=16) (never executed)

  • Group Key: related_specialist_providers_near_1.user_uid
80. 0.000 0.000 ↓ 0.0 0

CTE Scan on related_specialist_providers_near related_specialist_providers_near_1 (cost=0.00..0.02 rows=1 width=16) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Index Scan using user_provider_user_uid_index on user_provider user_provider_2 (cost=0.27..1.69 rows=1 width=16) (never executed)

  • Index Cond: (user_uid = uid_3.uid)
  • Filter: accepting_short_notice_bookings
Planning time : 13.912 ms
Execution time : 8,630.414 ms