explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mi77

Settings
# exclusive inclusive rows x rows loops node
1. 2.302 227.190 ↑ 2.6 385 1

Subquery Scan on wanted (cost=1,971.39..826,893.97 rows=1,000 width=193) (actual time=6.905..227.190 rows=385 loops=1)

2.          

CTE providers_near

3. 0.845 6.311 ↓ 4.0 4 1

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

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

Nested Loop Left Join (cost=0.70..63.15 rows=1 width=52) (actual time=0.259..0.902 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.012 0.329 ↓ 4.0 4 1

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

6. 0.231 0.285 ↓ 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.140..0.285 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.054 ↑ 1.0 1 6

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

9. 0.042 0.042 ↑ 2.0 1 6

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

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

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

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

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

  • Group Key: portfolio_1.user_uid
12. 0.268 0.268 ↑ 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.011..0.067 rows=37 loops=4)

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

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

  • Group Key: booking.provider[1]
14. 2.064 2.064 ↓ 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.034..2.064 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.004 0.112 ↓ 3.0 3 1

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

  • Group Key: providers_near.user_uid
17. 0.009 0.108 ↓ 3.0 3 1

Sort (cost=6.92..6.92 rows=1 width=16) (actual time=0.099..0.108 rows=3 loops=1)

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

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

19. 0.002 0.072 ↑ 1.0 1 1

Group (cost=5.56..5.56 rows=1 width=8) (actual time=0.067..0.072 rows=1 loops=1)

  • Group Key: st2.name
20. 0.009 0.070 ↑ 1.0 1 1

Sort (cost=5.56..5.56 rows=1 width=8) (actual time=0.066..0.070 rows=1 loops=1)

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

Hash Join (cost=2.17..5.55 rows=1 width=8) (actual time=0.052..0.061 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.015 0.015 ↑ 1.0 30 1

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

23. 0.005 0.018 ↑ 1.0 1 1

Hash (cost=2.16..2.16 rows=1 width=11) (actual time=0.017..0.018 rows=1 loops=1)

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

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

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

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

26. 0.001 0.001 ↓ 4.0 4 1

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

27. 0.012 0.012 ↑ 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.003..0.003 rows=1 loops=4)

  • Index Cond: ((user_uid = providers_near.user_uid) AND (code = st2.name))
  • Heap Fetches: 0
28. 3.898 221.038 ↑ 2.6 385 1

Function Scan on generate_series timeslot (cost=1,108.84..820,513.88 rows=1,000 width=65) (actual time=6.885..221.038 rows=385 loops=1)

29.          

CTE providers_near_booking

30. 1.438 21.611 ↓ 3.1 148 1

Nested Loop (cost=0.28..1,108.83 rows=48 width=2,302) (actual time=0.120..21.611 rows=148 loops=1)

  • Join Filter: (providers_near_3.user_uid = ANY (booking_1.provider))
  • Rows Removed by Join Filter: 3,832
31. 0.013 0.013 ↓ 4.0 4 1

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

32. 20.160 20.160 ↓ 1.0 995 4

Index Scan using paid_booking_coupon_uid_idx on booking booking_1 (cost=0.28..1,085.24 rows=989 width=2,270) (actual time=0.021..5.040 rows=995 loops=4)

  • Filter: ((uid <> ALL ('{32b8641a-72c5-4988-a9a4-eb432b4f75c0}'::uuid[])) AND ((status)::text = 'paid'::text))
  • Rows Removed by Filter: 4,274
33.          

SubPlan (for Function Scan)

34. 1.540 35.035 ↓ 0.0 0 385

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

35. 2.310 33.495 ↓ 0.0 0 385

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

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

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

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

Seq Scan on location_exception (cost=0.00..1.02 rows=1 width=32) (actual time=0.006..0.006 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
38. 3.970 182.105 ↑ 1.5 2 385

Function Scan on unnest uid_4 (cost=815.68..815.91 rows=3 width=16) (actual time=0.472..0.473 rows=2 loops=385)

  • Filter: (uid <> ALL ($17))
39.          

Initplan (for Function Scan)

40. 0.480 101.520 ↓ 0.0 0 240

HashAggregate (cost=455.08..455.16 rows=8 width=16) (actual time=0.423..0.423 rows=0 loops=240)

  • Group Key: (providers_near_booking.provider[1])
41. 0.960 101.040 ↓ 0.0 0 240

Append (cost=0.00..455.06 rows=8 width=16) (actual time=0.421..0.421 rows=0 loops=240)

42. 69.840 69.840 ↓ 0.0 0 240

CTE Scan on providers_near_booking (cost=0.00..1.56 rows=1 width=16) (actual time=0.291..0.291 rows=0 loops=240)

  • Filter: (tstzrange(timeslot.timeslot, (timeslot.timeslot + ('120 minute'::cstring)::interval), '[]'::text) && booking_timerange)
  • Rows Removed by Filter: 148
43. 3.120 24.720 ↓ 0.0 0 240

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

  • Join Filter: (providers_near_4.user_uid = provider_exceptions.user_uid)
44. 0.480 0.480 ↓ 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.002 rows=4 loops=240)

45. 21.120 21.120 ↓ 0.0 0 960

Seq Scan on provider_exceptions (cost=0.00..1.73 rows=1 width=16) (actual time=0.022..0.022 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
46. 0.480 5.520 ↓ 0.0 0 240

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

47. 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)

48. 4.800 4.800 ↓ 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.005..0.005 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)))
49. 5.775 76.615 ↑ 3.0 2 385

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

  • Sort Key: providers_near_6.portfolio_level, providers_near_6.quality DESC
  • Sort Method: quicksort Memory: 25kB
50. 1.155 70.840 ↑ 3.0 2 385

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

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

Nested Loop (cost=0.31..360.43 rows=6 width=28) (actual time=0.123..0.181 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
52. 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.000..0.001 rows=3 loops=385)

53. 2.824 9.240 ↓ 4.2 25 1,155

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

54. 0.020 6.416 ↓ 4.2 25 1

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

55. 6.312 6.312 ↓ 4.0 4 1

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

56. 0.084 0.084 ↑ 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.018..0.021 rows=6 loops=4)

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

SubPlan (for Subquery Scan)

58. 1.071 2.310 ↓ 2.0 2 385

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

  • Hash Cond: (uid.uid = providers_near_1.user_uid)
59. 1.155 1.155 ↑ 5.0 2 385

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

60. 0.007 0.084 ↓ 3.0 3 1

Hash (cost=1.38..1.38 rows=1 width=16) (actual time=0.080..0.084 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
61. 0.004 0.077 ↓ 3.0 3 1

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

  • Group Key: providers_near_1.user_uid
62. 0.014 0.073 ↓ 3.0 3 1

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

  • Sort Key: providers_near_1.user_uid
  • Sort Method: quicksort Memory: 25kB
63. 0.005 0.059 ↓ 3.0 3 1

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

64. 0.002 0.002 ↓ 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.002 rows=4 loops=1)

65. 0.052 0.052 ↑ 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.012..0.013 rows=1 loops=4)

  • Index Cond: ((user_uid = providers_near_1.user_uid) AND (code = 'realestate'::text))
  • Heap Fetches: 0
66. 0.660 1.540 ↓ 2.0 2 385

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

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

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

68. 0.004 0.110 ↓ 3.0 3 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=0.109..0.110 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
69. 0.106 0.106 ↓ 3.0 3 1

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

70. 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)
71. 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)
72. 0.000 0.000 ↓ 0.0 0

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

73. 0.000 0.000 ↓ 0.0 0

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

74. 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
75. 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)

76. 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
77. 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)
78. 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)
79. 0.000 0.000 ↓ 0.0 0

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

80. 0.000 0.000 ↓ 0.0 0

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

81. 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
82. 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)

83. 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 : 7.630 ms
Execution time : 228.241 ms