explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lv4L : Optimization for: Optimization for: Optimization for: Optimization for: plan #7Fl; plan #zul9; plan #wp51; plan #GXV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.148 16.657 ↑ 1.0 1 1

Nested Loop Left Join (cost=10.55..45,927.29 rows=1 width=374) (actual time=16.649..16.657 rows=1 loops=1)

  • Join Filter: (orders.klass = tarifi.id)
  • Rows Removed by Join Filter: 18
2.          

Initplan (for Nested Loop Left Join)

3. 0.000 0.000 ↓ 0.0 0

Index Scan using opt_opt_key on opt (cost=0.28..8.29 rows=1 width=16) (never executed)

  • Index Cond: ((opt)::text = 'order_creating_over'::text)
4. 0.001 0.049 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.26..39.26 rows=1 width=371) (actual time=0.043..0.049 rows=1 loops=1)

5. 0.004 0.041 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.85..34.05 rows=1 width=318) (actual time=0.037..0.041 rows=1 loops=1)

6. 0.006 0.024 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.56..33.66 rows=1 width=286) (actual time=0.022..0.024 rows=1 loops=1)

7. 0.000 0.015 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.27..25.35 rows=1 width=239) (actual time=0.014..0.015 rows=1 loops=1)

8. 0.005 0.014 ↑ 1.0 1 1

Nested Loop (cost=1.12..17.17 rows=1 width=211) (actual time=0.013..0.014 rows=1 loops=1)

9. 0.007 0.007 ↑ 1.0 1 1

Index Scan using orders_id_pk on orders orders_1 (cost=0.56..8.58 rows=1 width=141) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (id = 22204667)
10. 0.002 0.002 ↑ 1.0 1 1

Index Scan using orders_id_pk on orders (cost=0.56..8.58 rows=1 width=74) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (id = 22204667)
11. 0.001 0.001 ↓ 0.0 0 1

Index Scan using client_podarok_kod_pkey on client_podarok_kod (cost=0.15..8.17 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (id = orders.id_podarka)
12. 0.003 0.003 ↑ 1.0 1 1

Index Scan using bort_id_pk on bort (cost=0.29..8.30 rows=1 width=51) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (id = COALESCE(CASE WHEN (orders.id_bort > 0) THEN orders.id_bort ELSE NULL::integer END, orders.id_bort_vzyal))
13. 0.013 0.013 ↑ 1.0 1 1

Index Scan using car_id_pk on car (cost=0.29..0.38 rows=1 width=44) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (bort.id_car = id)
14. 0.007 0.007 ↑ 1.0 1 1

Index Scan using driver_id_pk on driver (cost=0.41..5.19 rows=1 width=61) (actual time=0.005..0.007 rows=1 loops=1)

  • Index Cond: (bort.id_driver = id)
15. 0.005 0.005 ↑ 1.0 19 1

Seq Scan on tarifi (cost=0.00..6.19 rows=19 width=7) (actual time=0.001..0.005 rows=19 loops=1)

16.          

SubPlan (for Nested Loop Left Join)

17. 0.011 4.104 ↑ 1.0 1 1

Subquery Scan on bort_s_klientom (cost=1.41..22,934.92 rows=1 width=4) (actual time=4.104..4.104 rows=1 loops=1)

  • Filter: (orders_1.id = bort_s_klientom.id_order)
  • Rows Removed by Filter: 25
18. 0.089 4.093 ↑ 10.5 26 1

HashSetOp Except (cost=1.41..22,931.52 rows=272 width=8) (actual time=4.091..4.093 rows=26 loops=1)

19. 0.024 4.004 ↓ 1.0 412 1

Append (cost=1.41..22,929.49 rows=405 width=8) (actual time=0.079..4.004 rows=412 loops=1)

20. 0.029 2.095 ↓ 1.2 330 1

Subquery Scan on "*SELECT* 1" (cost=1.41..11,468.59 rows=272 width=8) (actual time=0.079..2.095 rows=330 loops=1)

21. 0.000 2.066 ↓ 1.2 330 1

Nested Loop (cost=1.41..11,465.87 rows=272 width=8) (actual time=0.078..2.066 rows=330 loops=1)

22. 0.087 1.334 ↑ 2.6 246 1

Nested Loop (cost=0.85..2,703.04 rows=630 width=8) (actual time=0.069..1.334 rows=246 loops=1)

23. 0.711 0.711 ↑ 2.8 268 1

Index Scan using orders_idx22 on orders orders_2 (cost=0.56..1,440.42 rows=738 width=8) (actual time=0.018..0.711 rows=268 loops=1)

  • Index Cond: (end_ IS NULL)
24. 0.536 0.536 ↑ 1.0 1 268

Index Only Scan using bort_id_pk on bort bort_1 (cost=0.29..1.70 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=268)

  • Index Cond: (id = orders_2.id_bort)
  • Heap Fetches: 247
25. 0.738 0.738 ↑ 2.0 1 246

Index Scan using tochki_idx on tochki (cost=0.56..13.89 rows=2 width=4) (actual time=0.002..0.003 rows=1 loops=246)

  • Index Cond: (id_order = orders_2.id)
  • Filter: ((end_ IS NULL) AND (del_ IS NULL))
  • Rows Removed by Filter: 1
26. 0.010 1.885 ↑ 1.6 82 1

Subquery Scan on "*SELECT* 2" (cost=1.41..11,460.90 rows=133 width=8) (actual time=0.050..1.885 rows=82 loops=1)

27. 0.000 1.875 ↑ 1.6 82 1

Nested Loop (cost=1.41..11,459.57 rows=133 width=8) (actual time=0.048..1.875 rows=82 loops=1)

28. 0.011 1.177 ↑ 2.6 246 1

Nested Loop (cost=0.85..2,703.04 rows=630 width=8) (actual time=0.045..1.177 rows=246 loops=1)

29. 0.630 0.630 ↑ 2.8 268 1

Index Scan using orders_idx22 on orders orders_3 (cost=0.56..1,440.42 rows=738 width=8) (actual time=0.014..0.630 rows=268 loops=1)

  • Index Cond: (end_ IS NULL)
30. 0.536 0.536 ↑ 1.0 1 268

Index Only Scan using bort_id_pk on bort bort_2 (cost=0.29..1.70 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=268)

  • Index Cond: (id = orders_3.id_bort)
  • Heap Fetches: 247
31. 0.738 0.738 ↓ 0.0 0 246

Index Scan using tochki_idx on tochki tochki_1 (cost=0.56..13.89 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=246)

  • Index Cond: (id_order = orders_3.id)
  • Filter: ((end_ IS NULL) AND (del_ IS NULL) AND pervaya)
  • Rows Removed by Filter: 2
32. 0.007 4.774 ↑ 1.0 1 1

Subquery Scan on bort_s_klientom_1 (cost=1.41..22,934.92 rows=1 width=4) (actual time=4.774..4.774 rows=1 loops=1)

  • Filter: (orders_1.id = bort_s_klientom_1.id_order)
  • Rows Removed by Filter: 25
33. 0.134 4.767 ↑ 10.5 26 1

HashSetOp Except (cost=1.41..22,931.52 rows=272 width=8) (actual time=4.764..4.767 rows=26 loops=1)

34. 0.022 4.633 ↓ 1.0 412 1

Append (cost=1.41..22,929.49 rows=405 width=8) (actual time=0.069..4.633 rows=412 loops=1)

35. 0.043 2.460 ↓ 1.2 330 1

Subquery Scan on "*SELECT* 1_1" (cost=1.41..11,468.59 rows=272 width=8) (actual time=0.069..2.460 rows=330 loops=1)

36. 0.195 2.417 ↓ 1.2 330 1

Nested Loop (cost=1.41..11,465.87 rows=272 width=8) (actual time=0.069..2.417 rows=330 loops=1)

37. 0.154 1.484 ↑ 2.6 246 1

Nested Loop (cost=0.85..2,703.04 rows=630 width=8) (actual time=0.062..1.484 rows=246 loops=1)

38. 0.794 0.794 ↑ 2.8 268 1

Index Scan using orders_idx22 on orders orders_4 (cost=0.56..1,440.42 rows=738 width=8) (actual time=0.019..0.794 rows=268 loops=1)

  • Index Cond: (end_ IS NULL)
39. 0.536 0.536 ↑ 1.0 1 268

Index Only Scan using bort_id_pk on bort bort_3 (cost=0.29..1.70 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=268)

  • Index Cond: (id = orders_4.id_bort)
  • Heap Fetches: 247
40. 0.738 0.738 ↑ 2.0 1 246

Index Scan using tochki_idx on tochki tochki_2 (cost=0.56..13.89 rows=2 width=4) (actual time=0.003..0.003 rows=1 loops=246)

  • Index Cond: (id_order = orders_4.id)
  • Filter: ((end_ IS NULL) AND (del_ IS NULL))
  • Rows Removed by Filter: 1
41. 0.012 2.151 ↑ 1.6 82 1

Subquery Scan on "*SELECT* 2_1" (cost=1.41..11,460.90 rows=133 width=8) (actual time=0.064..2.151 rows=82 loops=1)

42. 0.097 2.139 ↑ 1.6 82 1

Nested Loop (cost=1.41..11,459.57 rows=133 width=8) (actual time=0.064..2.139 rows=82 loops=1)

43. 0.123 1.304 ↑ 2.6 246 1

Nested Loop (cost=0.85..2,703.04 rows=630 width=8) (actual time=0.058..1.304 rows=246 loops=1)

44. 0.645 0.645 ↑ 2.8 268 1

Index Scan using orders_idx22 on orders orders_5 (cost=0.56..1,440.42 rows=738 width=8) (actual time=0.018..0.645 rows=268 loops=1)

  • Index Cond: (end_ IS NULL)
45. 0.536 0.536 ↑ 1.0 1 268

Index Only Scan using bort_id_pk on bort bort_4 (cost=0.29..1.70 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=268)

  • Index Cond: (id = orders_5.id_bort)
  • Heap Fetches: 247
46. 0.738 0.738 ↓ 0.0 0 246

Index Scan using tochki_idx on tochki tochki_3 (cost=0.56..13.89 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=246)

  • Index Cond: (id_order = orders_5.id)
  • Filter: ((end_ IS NULL) AND (del_ IS NULL) AND pervaya)
  • Rows Removed by Filter: 2
47. 0.007 3.479 ↑ 1.0 1 1

Subquery Scan on bort_s_klientom_2 (cost=1.41..22,934.92 rows=1 width=4) (actual time=3.479..3.479 rows=1 loops=1)

  • Filter: (orders_1.id = bort_s_klientom_2.id_order)
  • Rows Removed by Filter: 25
48. 0.094 3.472 ↑ 10.5 26 1

HashSetOp Except (cost=1.41..22,931.52 rows=272 width=8) (actual time=3.468..3.472 rows=26 loops=1)

49. 0.029 3.378 ↓ 1.0 412 1

Append (cost=1.41..22,929.49 rows=405 width=8) (actual time=0.045..3.378 rows=412 loops=1)

50. 0.034 1.716 ↓ 1.2 330 1

Subquery Scan on "*SELECT* 1_2" (cost=1.41..11,468.59 rows=272 width=8) (actual time=0.045..1.716 rows=330 loops=1)

51. 0.166 1.682 ↓ 1.2 330 1

Nested Loop (cost=1.41..11,465.87 rows=272 width=8) (actual time=0.044..1.682 rows=330 loops=1)

52. 0.000 1.024 ↑ 2.6 246 1

Nested Loop (cost=0.85..2,703.04 rows=630 width=8) (actual time=0.040..1.024 rows=246 loops=1)

53. 0.537 0.537 ↑ 2.8 268 1

Index Scan using orders_idx22 on orders orders_6 (cost=0.56..1,440.42 rows=738 width=8) (actual time=0.012..0.537 rows=268 loops=1)

  • Index Cond: (end_ IS NULL)
54. 0.536 0.536 ↑ 1.0 1 268

Index Only Scan using bort_id_pk on bort bort_5 (cost=0.29..1.70 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=268)

  • Index Cond: (id = orders_6.id_bort)
  • Heap Fetches: 247
55. 0.492 0.492 ↑ 2.0 1 246

Index Scan using tochki_idx on tochki tochki_4 (cost=0.56..13.89 rows=2 width=4) (actual time=0.002..0.002 rows=1 loops=246)

  • Index Cond: (id_order = orders_6.id)
  • Filter: ((end_ IS NULL) AND (del_ IS NULL))
  • Rows Removed by Filter: 1
56. 0.005 1.633 ↑ 1.6 82 1

Subquery Scan on "*SELECT* 2_2" (cost=1.41..11,460.90 rows=133 width=8) (actual time=0.043..1.633 rows=82 loops=1)

57. 0.000 1.628 ↑ 1.6 82 1

Nested Loop (cost=1.41..11,459.57 rows=133 width=8) (actual time=0.043..1.628 rows=82 loops=1)

58. 0.000 0.954 ↑ 2.6 246 1

Nested Loop (cost=0.85..2,703.04 rows=630 width=8) (actual time=0.038..0.954 rows=246 loops=1)

59. 0.470 0.470 ↑ 2.8 268 1

Index Scan using orders_idx22 on orders orders_7 (cost=0.56..1,440.42 rows=738 width=8) (actual time=0.012..0.470 rows=268 loops=1)

  • Index Cond: (end_ IS NULL)
60. 0.536 0.536 ↑ 1.0 1 268

Index Only Scan using bort_id_pk on bort bort_6 (cost=0.29..1.70 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=268)

  • Index Cond: (id = orders_7.id_bort)
  • Heap Fetches: 247
61. 0.738 0.738 ↓ 0.0 0 246

Index Scan using tochki_idx on tochki tochki_5 (cost=0.56..13.89 rows=1 width=4) (actual time=0.002..0.003 rows=0 loops=246)

  • Index Cond: (id_order = orders_7.id)
  • Filter: ((end_ IS NULL) AND (del_ IS NULL) AND pervaya)
  • Rows Removed by Filter: 2
62. 0.007 3.098 ↑ 1.0 1 1

Subquery Scan on bort_s_klientom_3 (cost=1.41..22,934.92 rows=1 width=4) (actual time=3.098..3.098 rows=1 loops=1)

  • Filter: (orders_1.id = bort_s_klientom_3.id_order)
  • Rows Removed by Filter: 25
63. 0.082 3.091 ↑ 10.5 26 1

HashSetOp Except (cost=1.41..22,931.52 rows=272 width=8) (actual time=3.088..3.091 rows=26 loops=1)

64. 0.027 3.009 ↓ 1.0 412 1

Append (cost=1.41..22,929.49 rows=405 width=8) (actual time=0.044..3.009 rows=412 loops=1)

65. 0.032 1.537 ↓ 1.2 330 1

Subquery Scan on "*SELECT* 1_3" (cost=1.41..11,468.59 rows=272 width=8) (actual time=0.044..1.537 rows=330 loops=1)

66. 0.117 1.505 ↓ 1.2 330 1

Nested Loop (cost=1.41..11,465.87 rows=272 width=8) (actual time=0.043..1.505 rows=330 loops=1)

67. 0.176 0.896 ↑ 2.6 246 1

Nested Loop (cost=0.85..2,703.04 rows=630 width=8) (actual time=0.039..0.896 rows=246 loops=1)

68. 0.452 0.452 ↑ 2.8 268 1

Index Scan using orders_idx22 on orders orders_8 (cost=0.56..1,440.42 rows=738 width=8) (actual time=0.011..0.452 rows=268 loops=1)

  • Index Cond: (end_ IS NULL)
69. 0.268 0.268 ↑ 1.0 1 268

Index Only Scan using bort_id_pk on bort bort_7 (cost=0.29..1.70 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=268)

  • Index Cond: (id = orders_8.id_bort)
  • Heap Fetches: 247
70. 0.492 0.492 ↑ 2.0 1 246

Index Scan using tochki_idx on tochki tochki_6 (cost=0.56..13.89 rows=2 width=4) (actual time=0.002..0.002 rows=1 loops=246)

  • Index Cond: (id_order = orders_8.id)
  • Filter: ((end_ IS NULL) AND (del_ IS NULL))
  • Rows Removed by Filter: 1
71. 0.008 1.445 ↑ 1.6 82 1

Subquery Scan on "*SELECT* 2_3" (cost=1.41..11,460.90 rows=133 width=8) (actual time=0.041..1.445 rows=82 loops=1)

72. 0.105 1.437 ↑ 1.6 82 1

Nested Loop (cost=1.41..11,459.57 rows=133 width=8) (actual time=0.040..1.437 rows=82 loops=1)

73. 0.147 0.840 ↑ 2.6 246 1

Nested Loop (cost=0.85..2,703.04 rows=630 width=8) (actual time=0.036..0.840 rows=246 loops=1)

74. 0.425 0.425 ↑ 2.8 268 1

Index Scan using orders_idx22 on orders orders_9 (cost=0.56..1,440.42 rows=738 width=8) (actual time=0.011..0.425 rows=268 loops=1)

  • Index Cond: (end_ IS NULL)
75. 0.268 0.268 ↑ 1.0 1 268

Index Only Scan using bort_id_pk on bort bort_8 (cost=0.29..1.70 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=268)

  • Index Cond: (id = orders_9.id_bort)
  • Heap Fetches: 247
76. 0.492 0.492 ↓ 0.0 0 246

Index Scan using tochki_idx on tochki tochki_7 (cost=0.56..13.89 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=246)

  • Index Cond: (id_order = orders_9.id)
  • Filter: ((end_ IS NULL) AND (del_ IS NULL) AND pervaya)
  • Rows Removed by Filter: 2
Planning time : 12.310 ms