explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S2sO

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 95,503.468 ↓ 0.0 0 1

Limit (cost=758.50..239,355.58 rows=1 width=358) (actual time=95,503.468..95,503.468 rows=0 loops=1)

2. 0.001 95,503.467 ↓ 0.0 0 1

Nested Loop (cost=758.50..239,355.58 rows=1 width=358) (actual time=95,503.467..95,503.467 rows=0 loops=1)

3. 0.000 95,503.466 ↓ 0.0 0 1

Nested Loop (cost=758.22..223,149.56 rows=1 width=272) (actual time=95,503.466..95,503.466 rows=0 loops=1)

4. 0.000 95,503.466 ↓ 0.0 0 1

Nested Loop Left Join (cost=757.80..223,148.99 rows=1 width=263) (actual time=95,503.466..95,503.466 rows=0 loops=1)

  • Join Filter: (countries.id = client_direction.country_id)
5. 0.000 95,503.466 ↓ 0.0 0 1

Nested Loop Left Join (cost=757.80..223,140.35 rows=1 width=245) (actual time=95,503.466..95,503.466 rows=0 loops=1)

  • Join Filter: (registration_status.id = client_originator_registration.registration_status_id)
6. 0.000 95,503.466 ↓ 0.0 0 1

Nested Loop (cost=757.80..223,139.17 rows=1 width=213) (actual time=95,503.466..95,503.466 rows=0 loops=1)

7. 0.001 95,503.466 ↓ 0.0 0 1

Nested Loop Left Join (cost=757.38..223,138.60 rows=1 width=204) (actual time=95,503.466..95,503.466 rows=0 loops=1)

  • Join Filter: (operators_groups.id = client_direction.operator_group_id)
8. 11.532 95,503.465 ↓ 0.0 0 1

Nested Loop Left Join (cost=757.38..223,137.42 rows=1 width=181) (actual time=95,503.465..95,503.465 rows=0 loops=1)

  • Filter: ((SubPlan 3) = client_direction.id)
  • Rows Removed by Filter: 1724
9. 14.365 37.501 ↓ 132.6 1,724 1

Hash Join (cost=757.25..3,366.25 rows=13 width=149) (actual time=10.391..37.501 rows=1,724 loops=1)

  • Hash Cond: (client_originator_registration.client_direction_id = client_direction.id)
10. 12.824 12.824 ↓ 1.1 71,017 1

Seq Scan on client_originator_registration (cost=0.00..2,357.45 rows=67,045 width=117) (actual time=0.056..12.824 rows=71,017 loops=1)

11. 0.013 10.312 ↓ 2.0 10 1

Hash (cost=757.18..757.18 rows=5 width=32) (actual time=10.312..10.312 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 10.299 10.299 ↓ 2.0 10 1

Seq Scan on client_direction (cost=0.00..757.18 rows=5 width=32) (actual time=0.462..10.299 rows=10 loops=1)

  • Filter: ((user_id IS NOT NULL) AND (user_id = 1213) AND (is_deleted = 0))
  • Rows Removed by Filter: 26232
13. 17.240 17.240 ↑ 1.0 1 1,724

Index Scan using service_type_pkey on service_type (cost=0.13..0.15 rows=1 width=40) (actual time=0.008..0.010 rows=1 loops=1,724)

  • Index Cond: (id = client_originator_registration.service_type_id)
14.          

SubPlan (forNested Loop Left Join)

15. 5.172 95,437.192 ↓ 0.0 0 1,724

Limit (cost=16,905.31..16,905.31 rows=1 width=16) (actual time=55.358..55.358 rows=0 loops=1,724)

16. 15.516 95,432.020 ↓ 0.0 0 1,724

Sort (cost=16,905.31..16,905.31 rows=1 width=16) (actual time=55.355..55.355 rows=0 loops=1,724)

  • Sort Key: rs_1.priority DESC
  • Sort Method: quicksort Memory: 25kB
17. 1.724 95,416.504 ↓ 0.0 0 1,724

Nested Loop (cost=15.74..16,905.30 rows=1 width=16) (actual time=55.346..55.346 rows=0 loops=1,724)

  • Join Filter: (cd_1.id = cor_1.client_direction_id)
18. 1.724 95,414.780 ↓ 0.0 0 1,724

Nested Loop (cost=15.45..16,896.97 rows=1 width=24) (actual time=55.345..55.345 rows=0 loops=1,724)

19. 1.724 95,413.056 ↓ 0.0 0 1,724

Nested Loop (cost=15.30..16,888.80 rows=1 width=32) (actual time=55.344..55.344 rows=0 loops=1,724)

20. 1.724 95,411.332 ↓ 0.0 0 1,724

Nested Loop (cost=15.30..16,887.61 rows=1 width=32) (actual time=55.343..55.343 rows=0 loops=1,724)

21. 1.724 95,409.608 ↓ 0.0 0 1,724

Nested Loop (cost=15.30..16,886.50 rows=1 width=40) (actual time=55.342..55.342 rows=0 loops=1,724)

22. 21.406 95,407.884 ↓ 0.0 0 1,724

Nested Loop (cost=15.02..16,842.33 rows=126 width=32) (actual time=55.341..55.341 rows=0 loops=1,724)

  • Join Filter: (por_1.provider_direction_id = pd_1.id)
  • Rows Removed by Join Filter: 134
23. 7.216 94,137.296 ↓ 0.0 0 1,724

Nested Loop (cost=0.42..13,768.88 rows=1 width=16) (actual time=53.051..54.604 rows=0 loops=1,724)

24. 94,128.676 94,128.676 ↓ 0.0 0 1,724

Seq Scan on provider_originator_registration por_1 (cost=0.00..13,764.43 rows=1 width=24) (actual time=53.046..54.599 rows=0 loops=1,724)

  • Filter: ((registration_status_id = 1) AND (originator_id = client_originator_registration.originator_change_id))
  • Rows Removed by Filter: 351630
25. 1.404 1.404 ↑ 1.0 1 54

Index Only Scan using originator_pkey on originator o_1 (cost=0.42..4.44 rows=1 width=8) (actual time=0.024..0.026 rows=1 loops=54)

  • Index Cond: (id = por_1.originator_change_id)
  • Heap Fetches: 18
26. 261.144 1,249.182 ↓ 11.3 4,269 54

Hash Join (cost=14.60..3,068.72 rows=378 width=40) (actual time=0.107..23.133 rows=4,269 loops=54)

  • Hash Cond: (r_1.provider_direction_id = pd_1.id)
27. 983.826 983.826 ↓ 1.0 56,480 54

Seq Scan on route r_1 (cost=0.00..2,846.44 rows=54,375 width=16) (actual time=0.006..18.219 rows=56,480 loops=54)

  • Filter: (is_deleted = 0)
  • Rows Removed by Filter: 58034
28. 0.208 4.212 ↑ 1.0 3 52

Hash (cost=14.56..14.56 rows=3 width=24) (actual time=0.081..0.081 rows=3 loops=52)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 4.004 4.004 ↑ 1.0 3 52

Seq Scan on provider_direction pd_1 (cost=0.00..14.56 rows=3 width=24) (actual time=0.009..0.077 rows=3 loops=52)

  • Filter: ((service_type_id = service_type.id) AND (gate_id = 99) AND (is_deleted = 0))
  • Rows Removed by Filter: 429
30. 0.000 0.000 ↓ 0.0 0

Index Scan using client_direction_pkey on client_direction cd_1 (cost=0.29..0.34 rows=1 width=8) (never executed)

  • Index Cond: (id = r_1.client_direction_id)
  • Filter: (user_id = client_direction.user_id)
31. 0.000 0.000 ↓ 0.0 0

Seq Scan on service_type st_1 (cost=0.00..1.10 rows=1 width=8) (never executed)

  • Filter: (id = service_type.id)
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on registration_status rs_1 (cost=0.00..1.18 rows=1 width=16) (never executed)

  • Filter: ((id = 1) AND CASE WHEN ((id = 3) OR (id = 5)) THEN (id = ANY ('{3,5}'::bigint[])) ELSE (id <> 100) END)
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_24190_s_id on turn_connect_out tco_1 (cost=0.14..8.16 rows=1 width=8) (never executed)

  • Index Cond: (s_id = 99)
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Index Scan using client_originator_registration_pkey on client_originator_registration cor_1 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: (id = client_originator_registration.id)
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on operators_groups (cost=0.00..1.08 rows=8 width=23) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Index Scan using originator_pkey on originator (cost=0.42..0.56 rows=1 width=17) (never executed)

  • Index Cond: (id = client_originator_registration.originator_id)
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on registration_status (cost=0.00..1.08 rows=8 width=40) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Seq Scan on countries (cost=0.00..5.51 rows=251 width=26) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Index Scan using originator_pkey on originator originators_changes (cost=0.42..0.56 rows=1 width=17) (never executed)

  • Index Cond: (id = client_originator_registration.originator_change_id)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_24354_primary on users (cost=0.28..8.30 rows=1 width=54) (never executed)

  • Index Cond: (id = 1213)
41.          

SubPlan (forNested Loop)

42. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..11.48 rows=1 width=8) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_originator_registration_document (cost=0.00..22.95 rows=2 width=8) (never executed)

  • Filter: (client_originator_registration_id = client_originator_registration.id)
44. 0.000 0.000 ↓ 0.0 0

Limit (cost=16,186.23..16,186.23 rows=1 width=64) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Sort (cost=16,186.23..16,186.23 rows=1 width=64) (never executed)

  • Sort Key: (CASE WHEN ((rs.id <> 3) OR (rs.id <> 5)) THEN rs.priority ELSE NULL::bigint END) DESC, r.originator_id DESC, r.priority DESC, (CASE WHEN ((rs.id = 3) OR (rs.id = 5)) THEN rs.priority ELSE NULL::bigint END) DESC
46. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14.93..16,186.22 rows=1 width=64) (never executed)

  • Join Filter: (cd.id = cor.client_direction_id)
47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14.63..16,177.87 rows=1 width=182) (never executed)

  • Join Filter: (pd.gate_id = tco.s_id)
48. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14.63..16,163.43 rows=1 width=136) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14.63..16,162.32 rows=1 width=104) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=14.22..16,157.88 rows=1 width=112) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=13.93..16,135.79 rows=63 width=104) (never executed)

  • Join Filter: (por.provider_direction_id = pd.id)
52. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=13.93..3,109.58 rows=4,531 width=56) (never executed)

  • Hash Cond: (r.provider_direction_id = pd.id)
53. 0.000 0.000 ↓ 0.0 0

Seq Scan on route r (cost=0.00..2,846.44 rows=54,375 width=32) (never executed)

  • Filter: (is_deleted = 0)
54. 0.000 0.000 ↓ 0.0 0

Hash (cost=13.48..13.48 rows=36 width=24) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Seq Scan on provider_direction pd (cost=0.00..13.48 rows=36 width=24) (never executed)

  • Filter: ((service_type_id = service_type.id) AND (is_deleted = 0))
56. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..12,890.28 rows=2 width=72) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..12,890.27 rows=2 width=72) (never executed)

  • Join Filter: (por.registration_status_id = rs.id)
58. 0.000 0.000 ↓ 0.0 0

Seq Scan on provider_originator_registration por (cost=0.00..12,888.86 rows=4 width=32) (never executed)

  • Filter: (originator_id = client_originator_registration.originator_change_id)
59. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.18 rows=4 width=48) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Seq Scan on registration_status rs (cost=0.00..1.16 rows=4 width=48) (never executed)

  • Filter: CASE WHEN ((id = 3) OR (id = 5)) THEN (id = ANY ('{3,5}'::bigint[])) ELSE (id <> 100) END
61. 0.000 0.000 ↓ 0.0 0

Index Scan using client_direction_pkey on client_direction cd (cost=0.29..0.34 rows=1 width=8) (never executed)

  • Index Cond: (id = r.client_direction_id)
  • Filter: (user_id = client_direction.user_id)
62. 0.000 0.000 ↓ 0.0 0

Index Only Scan using originator_pkey on originator o (cost=0.42..4.44 rows=1 width=8) (never executed)

  • Index Cond: (id = por.originator_change_id)
  • Heap Fetches: 0
63. 0.000 0.000 ↓ 0.0 0

Seq Scan on service_type st (cost=0.00..1.10 rows=1 width=40) (never executed)

  • Filter: (id = service_type.id)
64. 0.000 0.000 ↓ 0.0 0

Seq Scan on turn_connect_out tco (cost=0.00..11.97 rows=197 width=54) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Index Scan using client_originator_registration_pkey on client_originator_registration cor (cost=0.29..8.31 rows=1 width=8) (never executed)

  • Index Cond: (id = client_originator_registration.id)