explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3IFS

Settings
# exclusive inclusive rows x rows loops node
1. 0.124 105.384 ↑ 1.0 50 1

Limit (cost=9.49..852.54 rows=50 width=503) (actual time=3.898..105.384 rows=50 loops=1)

2. 44.988 105.260 ↑ 110.1 50 1

Nested Loop Left Join (cost=9.49..92,863.77 rows=5,507 width=503) (actual time=3.897..105.260 rows=50 loops=1)

  • Join Filter: ((assigned_territories.uuid)::text = COALESCE(lbto.office_uuid, lbto_def.office_uuid))
  • Rows Removed by Join Filter: 141,250
3. 0.151 50.722 ↑ 8.1 50 1

Nested Loop Left Join (cost=9.21..70,692.39 rows=407 width=1,033) (actual time=1.983..50.722 rows=50 loops=1)

4. 0.071 50.271 ↑ 8.0 50 1

Nested Loop Left Join (cost=8.92..69,690.38 rows=400 width=1,006) (actual time=1.973..50.271 rows=50 loops=1)

  • Join Filter: (lbto_def.branch_code = client_contract.responsible_office_id)
5. 0.128 50.100 ↑ 8.0 50 1

Nested Loop Left Join (cost=8.65..69,664.24 rows=400 width=973) (actual time=1.878..50.100 rows=50 loops=1)

6. 0.131 49.622 ↑ 8.0 50 1

Nested Loop Left Join (cost=8.24..68,612.24 rows=400 width=939) (actual time=1.868..49.622 rows=50 loops=1)

7. 0.081 48.741 ↑ 1.1 50 1

Nested Loop Left Join (cost=7.69..68,573.26 rows=54 width=917) (actual time=1.858..48.741 rows=50 loops=1)

  • Join Filter: (creator_def.code = client_contract.creator_id)
8. 0.110 48.660 ↑ 1.1 50 1

Nested Loop Left Join (cost=6.99..68,567.32 rows=53 width=844) (actual time=1.847..48.660 rows=50 loops=1)

9. 0.140 47.800 ↑ 1.0 50 1

Nested Loop Left Join (cost=6.44..68,428.70 rows=50 width=792) (actual time=1.831..47.800 rows=50 loops=1)

10. 0.069 47.310 ↑ 1.0 50 1

Nested Loop Left Join (cost=6.02..68,296.95 rows=50 width=750) (actual time=1.820..47.310 rows=50 loops=1)

  • Join Filter: (contract_status_def.code = client_contract.status_id)
  • Rows Removed by Join Filter: 111
11. 0.062 47.191 ↑ 1.0 50 1

Nested Loop Left Join (cost=6.02..68,294.93 rows=50 width=688) (actual time=1.811..47.191 rows=50 loops=1)

  • Join Filter: (contract_type_def.code = client_contract.type_id)
  • Rows Removed by Join Filter: 52
12. 0.059 47.079 ↑ 1.0 50 1

Nested Loop (cost=6.02..68,293.07 rows=50 width=594) (actual time=1.798..47.079 rows=50 loops=1)

13. 6.872 46.970 ↓ 8.3 50 1

Nested Loop Left Join (cost=5.47..68,281.91 rows=6 width=572) (actual time=1.776..46.970 rows=50 loops=1)

  • Join Filter: (lbto.branch_code = client_contract.responsible_office_id)
  • Rows Removed by Join Filter: 65,400
14. 0.122 34.998 ↓ 8.3 50 1

Nested Loop Left Join (cost=5.47..68,172.61 rows=6 width=535) (actual time=0.955..34.998 rows=50 loops=1)

15. 0.187 34.376 ↓ 8.3 50 1

Nested Loop Left Join (cost=5.06..68,156.83 rows=6 width=501) (actual time=0.933..34.376 rows=50 loops=1)

16. 0.132 33.839 ↓ 8.3 50 1

Nested Loop Left Join (cost=4.78..68,141.80 rows=6 width=470) (actual time=0.920..33.839 rows=50 loops=1)

17. 0.106 33.207 ↓ 8.3 50 1

Nested Loop (cost=4.36..68,125.99 rows=6 width=423) (actual time=0.911..33.207 rows=50 loops=1)

18. 0.149 33.051 ↓ 8.3 50 1

Nested Loop Left Join (cost=3.66..68,120.76 rows=6 width=345) (actual time=0.873..33.051 rows=50 loops=1)

19. 0.199 32.502 ↓ 50.0 50 1

Nested Loop Left Join (cost=3.39..68,120.46 rows=1 width=336) (actual time=0.858..32.502 rows=50 loops=1)

  • Join Filter: (contract_status.code = client_contract.status_id)
  • Rows Removed by Join Filter: 111
20. 0.197 32.203 ↓ 50.0 50 1

Nested Loop Left Join (cost=3.39..68,119.18 rows=1 width=272) (actual time=0.850..32.203 rows=50 loops=1)

  • Join Filter: (contract_type.code = client_contract.type_id)
  • Rows Removed by Join Filter: 52
21. 1.066 31.906 ↓ 50.0 50 1

Nested Loop Left Join (cost=3.39..68,118.06 rows=1 width=176) (actual time=0.841..31.906 rows=50 loops=1)

22. 4.212 30.640 ↓ 50.0 50 1

Nested Loop Left Join (cost=1.40..68,114.94 rows=1 width=168) (actual time=0.811..30.640 rows=50 loops=1)

  • Join Filter: ((contracts_reverse_rule.contract_id)::integer = client_contract.id)
  • Rows Removed by Join Filter: 37,300
23. 0.122 23.078 ↓ 50.0 50 1

Nested Loop Left Join (cost=1.40..68,094.16 rows=1 width=168) (actual time=0.684..23.078 rows=50 loops=1)

24. 0.212 22.406 ↓ 50.0 50 1

Nested Loop (cost=0.97..68,092.61 rows=1 width=166) (actual time=0.668..22.406 rows=50 loops=1)

25. 20.819 20.819 ↓ 18.3 55 1

Index Scan Backward using idx_client_contract_creation_date on client_contract (cost=0.42..68,084.28 rows=3 width=82) (actual time=0.638..20.819 rows=55 loops=1)

  • Filter: ((id IS NOT NULL) AND (creator_id = '31399'::text) AND ((responsible_office_id = '1210'::text) OR (responsible_office_id = '44'::text)))
  • Rows Removed by Filter: 8,122
26. 1.375 1.375 ↑ 1.0 1 55

Index Scan using contragent_pkey on contragent (cost=0.55..2.78 rows=1 width=84) (actual time=0.025..0.025 rows=1 loops=55)

  • Index Cond: ((id = client_contract.contragent_id) AND (lang = 'rus'::text))
  • Filter: (city_code = '44'::text)
  • Rows Removed by Filter: 0
27. 0.550 0.550 ↑ 1.0 1 50

Index Only Scan using contracts_currencies_pkey on contracts_currencies (cost=0.42..1.54 rows=1 width=6) (actual time=0.009..0.011 rows=1 loops=50)

  • Index Cond: (contract_id = client_contract.id)
  • Heap Fetches: 0
28. 3.350 3.350 ↑ 1.0 746 50

Seq Scan on contracts_reverse_rule (cost=0.00..11.46 rows=746 width=4) (actual time=0.005..0.067 rows=746 loops=50)

29. 0.000 0.200 ↑ 1.0 1 50

Index Only Scan using contract_id_status_code_change_date_unique on contract_status_history csh (cost=1.99..3.12 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=50)

  • Index Cond: ((contract_id = client_contract.id) AND (status_code = client_contract.status_id) AND (change_date = (SubPlan 2)))
  • Heap Fetches: 0
30.          

SubPlan (for Index Only Scan)

31. 0.100 0.750 ↑ 1.0 1 50

Result (cost=1.55..1.56 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=50)

32.          

Initplan (for Result)

33. 0.100 0.650 ↑ 1.0 1 50

Limit (cost=0.43..1.55 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=50)

34. 0.550 0.550 ↑ 1.0 1 50

Index Only Scan Backward using contract_id_status_code_change_date_unique on contract_status_history cshi (cost=0.43..1.55 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=50)

  • Index Cond: ((contract_id = client_contract.id) AND (status_code = client_contract.status_id) AND (change_date IS NOT NULL))
  • Heap Fetches: 0
35. 0.100 0.100 ↓ 2.0 2 50

Seq Scan on contract_type (cost=0.00..1.11 rows=1 width=96) (actual time=0.002..0.002 rows=2 loops=50)

  • Filter: (lang = 'rus'::text)
36. 0.100 0.100 ↓ 3.0 3 50

Seq Scan on contract_status (cost=0.00..1.26 rows=1 width=96) (actual time=0.002..0.002 rows=3 loops=50)

  • Filter: (lang = 'rus'::text)
37. 0.400 0.400 ↑ 1.0 1 50

Index Scan using currency_code_lang_key on currency (cost=0.28..0.30 rows=1 width=13) (actual time=0.008..0.008 rows=1 loops=50)

  • Index Cond: ((code = contracts_currencies.currency_id) AND (lang = 'rus'::text))
38. 0.013 0.050 ↑ 1.0 1 50

Materialize (cost=0.70..5.16 rows=1 width=83) (actual time=0.001..0.001 rows=1 loops=50)

39. 0.002 0.037 ↑ 1.0 1 1

Nested Loop (cost=0.70..5.15 rows=1 width=83) (actual time=0.034..0.037 rows=1 loops=1)

40. 0.020 0.020 ↑ 1.0 1 1

Index Scan using users_catalog_code_lang_key on users_catalog creator (cost=0.41..2.64 rows=1 width=56) (actual time=0.018..0.020 rows=1 loops=1)

  • Index Cond: ((code = '31399'::text) AND (lang = 'rus'::text))
  • Filter: (subdivision_id = '458'::text)
41. 0.015 0.015 ↑ 1.0 1 1

Index Scan using subdivision_code_lang_key on subdivision creatorsubdivision (cost=0.29..2.50 rows=1 width=35) (actual time=0.014..0.015 rows=1 loops=1)

  • Index Cond: ((code = '458'::text) AND (lang = 'rus'::text))
42. 0.500 0.500 ↑ 1.0 1 50

Index Scan using users_catalog_code_lang_key on users_catalog auditor (cost=0.41..2.63 rows=1 width=52) (actual time=0.010..0.010 rows=1 loops=50)

  • Index Cond: ((code = client_contract.auditor_id) AND (lang = 'rus'::text))
43. 0.350 0.350 ↑ 1.0 1 50

Index Scan using subdivision_code_lang_key on subdivision (cost=0.29..2.50 rows=1 width=35) (actual time=0.007..0.007 rows=1 loops=50)

  • Index Cond: ((code = client_contract.subdivision_id) AND (lang = 'rus'::text))
44. 0.500 0.500 ↑ 1.0 1 50

Index Scan using branch_code_lang_key on branch responsible_office (cost=0.41..2.63 rows=1 width=39) (actual time=0.010..0.010 rows=1 loops=50)

  • Index Cond: ((code = client_contract.responsible_office_id) AND (lang = 'rus'::text))
45. 4.606 5.100 ↓ 2.8 1,308 50

Materialize (cost=0.00..69.05 rows=460 width=42) (actual time=0.000..0.102 rows=1,308 loops=50)

46. 0.494 0.494 ↓ 2.8 1,308 1

Seq Scan on link_branch_to_office lbto (cost=0.00..66.75 rows=460 width=42) (actual time=0.006..0.494 rows=1,308 loops=1)

  • Filter: (lang = 'rus'::text)
47. 0.029 0.050 ↑ 8.0 1 50

Materialize (cost=0.55..10.58 rows=8 width=30) (actual time=0.001..0.001 rows=1 loops=50)

48. 0.021 0.021 ↑ 8.0 1 1

Index Scan using city_code_lang_key on city contragentcity (cost=0.55..10.54 rows=8 width=30) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: ((code = '44'::text) AND (lang = 'rus'::text))
49. 0.043 0.050 ↓ 2.0 2 50

Materialize (cost=0.00..1.12 rows=1 width=96) (actual time=0.000..0.001 rows=2 loops=50)

50. 0.007 0.007 ↓ 3.0 3 1

Seq Scan on contract_type contract_type_def (cost=0.00..1.11 rows=1 width=96) (actual time=0.006..0.007 rows=3 loops=1)

  • Filter: (lang = 'eng'::text)
  • Rows Removed by Filter: 3
51. 0.043 0.050 ↓ 3.0 3 50

Materialize (cost=0.00..1.27 rows=1 width=96) (actual time=0.000..0.001 rows=3 loops=50)

52. 0.007 0.007 ↓ 5.0 5 1

Seq Scan on contract_status contract_status_def (cost=0.00..1.26 rows=1 width=96) (actual time=0.004..0.007 rows=5 loops=1)

  • Filter: (lang = 'eng'::text)
  • Rows Removed by Filter: 7
53. 0.350 0.350 ↓ 0.0 0 50

Index Scan using users_catalog_code_lang_key on users_catalog auditor_def (cost=0.41..2.63 rows=1 width=52) (actual time=0.007..0.007 rows=0 loops=50)

  • Index Cond: ((code = client_contract.auditor_id) AND (lang = 'eng'::text))
54. 0.750 0.750 ↑ 1.0 1 50

Index Scan using contragent_pkey on contragent contragent_def (cost=0.55..2.77 rows=1 width=84) (actual time=0.015..0.015 rows=1 loops=50)

  • Index Cond: ((id = client_contract.contragent_id) AND (lang = 'eng'::text))
55. 0.000 0.000 ↓ 0.0 0 50

Materialize (cost=0.70..5.15 rows=1 width=83) (actual time=0.000..0.000 rows=0 loops=50)

56. 0.001 0.007 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.70..5.14 rows=1 width=83) (actual time=0.006..0.007 rows=0 loops=1)

57. 0.006 0.006 ↓ 0.0 0 1

Index Scan using users_catalog_code_lang_key on users_catalog creator_def (cost=0.41..2.63 rows=1 width=56) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((code = '31399'::text) AND (lang = 'eng'::text))
58. 0.000 0.000 ↓ 0.0 0

Index Scan using subdivision_code_lang_key on subdivision creatorsubdivision_def (cost=0.29..2.50 rows=1 width=35) (never executed)

  • Index Cond: ((code = creator_def.subdivision_id) AND (lang = 'eng'::text))
59. 0.750 0.750 ↑ 7.0 1 50

Index Scan using city_code_lang_key on city contragentcity_def (cost=0.55..1.77 rows=7 width=30) (actual time=0.015..0.015 rows=1 loops=50)

  • Index Cond: ((code = contragent_def.city_code) AND (lang = 'eng'::text))
60. 0.350 0.350 ↑ 1.0 1 50

Index Scan using branch_code_lang_key on branch responsible_office_def (cost=0.41..2.63 rows=1 width=39) (actual time=0.007..0.007 rows=1 loops=50)

  • Index Cond: ((code = client_contract.responsible_office_id) AND (lang = 'eng'::text))
61. 0.008 0.100 ↓ 0.0 0 50

Materialize (cost=0.27..20.15 rows=1 width=42) (actual time=0.002..0.002 rows=0 loops=50)

62. 0.092 0.092 ↓ 0.0 0 1

Index Scan using link_branch_to_office_code_lang_key on link_branch_to_office lbto_def (cost=0.27..20.14 rows=1 width=42) (actual time=0.092..0.092 rows=0 loops=1)

  • Index Cond: (lang = 'eng'::text)
63. 0.300 0.300 ↑ 1.0 1 50

Index Scan using subdivision_code_lang_key on subdivision subdivision_def (cost=0.29..2.50 rows=1 width=35) (actual time=0.006..0.006 rows=1 loops=50)

  • Index Cond: ((code = client_contract.subdivision_id) AND (lang = 'eng'::text))
64. 9.116 9.550 ↓ 1.0 2,825 50

Materialize (cost=0.28..68.70 rows=2,706 width=16) (actual time=0.001..0.191 rows=2,825 loops=50)

65. 0.434 0.434 ↓ 1.0 2,825 1

Index Only Scan using idx_assigned_territories_uuid on assigned_territories (cost=0.28..55.17 rows=2,706 width=16) (actual time=0.016..0.434 rows=2,825 loops=1)

  • Heap Fetches: 0
Planning time : 97.384 ms
Execution time : 105.879 ms