explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VRBy

Settings
# exclusive inclusive rows x rows loops node
1. 0.119 100,456.377 ↑ 1.0 100 1

Limit (cost=36,372.63..10,958,381.77 rows=100 width=297) (actual time=1,136.275..100,456.377 rows=100 loops=1)

2. 2.551 100,456.258 ↑ 2,248,095.3 100 1

Nested Loop (cost=36,372.63..24,553,717,112,789.21 rows=224,809,527 width=297) (actual time=1,136.273..100,456.258 rows=100 loops=1)

3. 0.343 60,291.907 ↑ 7,228.4 100 1

Nested Loop Left Join (cost=36,372.22..26,303,094,215.28 rows=722,840 width=128) (actual time=606.546..60,291.907 rows=100 loops=1)

  • Join Filter: (ppt.id = ppp.type_id)
  • Rows Removed by Join Filter: 1800
4. 60,288.369 60,291.364 ↑ 7,228.4 100 1

Nested Loop Left Join (cost=36,372.22..26,302,929,736.52 rows=722,840 width=128) (actual time=606.525..60,291.364 rows=100 loops=1)

5. 0.295 2.995 ↑ 7,228.4 100 1

Nested Loop Left Join (cost=1.14..12,423,338.79 rows=722,840 width=120) (actual time=1.220..2.995 rows=100 loops=1)

  • Join Filter: (cpt.id = ro.period_type_id)
  • Rows Removed by Join Filter: 576
6. 0.289 2.600 ↑ 7,228.4 100 1

Nested Loop (cost=1.14..12,250,621.11 rows=722,840 width=50) (actual time=1.196..2.600 rows=100 loops=1)

7. 0.087 1.611 ↑ 7,228.4 100 1

Nested Loop (cost=0.71..11,873,630.38 rows=722,840 width=50) (actual time=1.183..1.611 rows=100 loops=1)

8. 0.020 0.020 ↑ 2,159.0 1 1

Index Scan using t_partner_pharm_pkey on t_partner_pharm pp (cost=0.28..360.56 rows=2,159 width=4) (actual time=0.020..0.020 rows=1 loops=1)

  • Filter: (NOT is_test)
9. 0.040 1.504 ↑ 5.6 100 1

Append (cost=0.42..5,493.81 rows=562 width=46) (actual time=1.159..1.504 rows=100 loops=1)

10. 1.464 1.464 ↑ 5.6 100 1

Index Scan using t_roznica_outcome_service_default_date_pivot_partner_id_idx on t_roznica_outcome_service_default ro (cost=0.42..5,491.00 rows=562 width=46) (actual time=1.156..1.464 rows=100 loops=1)

  • Index Cond: (pivot_partner_id = pp.pharm_id)
  • Filter: (service_id = 3)
11. 0.700 0.700 ↑ 1.0 1 100

Index Scan using t_card_idx1 on t_card c (cost=0.43..0.52 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=100)

  • Index Cond: (scan_code = ro.club_card_scan_code)
12. 0.084 0.100 ↑ 2.3 7 100

Materialize (cost=0.00..1.44 rows=16 width=78) (actual time=0.001..0.001 rows=7 loops=100)

13. 0.016 0.016 ↑ 1.6 10 1

Seq Scan on t_card_period_type cpt (cost=0.00..1.36 rows=16 width=78) (actual time=0.009..0.016 rows=10 loops=1)

14. 0.000 0.000 ↓ 0.0 0 100

Index Scan using t_card_period_pkey on t_card_period ppp (cost=36,371.08..36,371.13 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=100)

  • Index Cond: (id = (SubPlan 4))
15.          

SubPlan (for Index Scan)

16. 0.300 60,287.700 ↓ 0.0 0 100

Limit (cost=36,370.65..36,370.65 rows=1 width=12) (actual time=602.877..602.877 rows=0 loops=100)

17. 0.900 60,287.400 ↓ 0.0 0 100

Sort (cost=36,370.65..36,370.65 rows=1 width=12) (actual time=602.874..602.874 rows=0 loops=100)

  • Sort Key: h_12.outcome_date_sale DESC
  • Sort Method: quicksort Memory: 25kB
18. 0.586 60,286.500 ↓ 0.0 0 100

Nested Loop (cost=1.28..36,370.64 rows=1 width=12) (actual time=602.865..602.865 rows=0 loops=100)

  • Join Filter: (p.type_id = t.id)
  • Rows Removed by Join Filter: 18
19. 0.430 60,285.200 ↑ 1.0 1 100

Nested Loop (cost=1.28..36,369.06 rows=1 width=16) (actual time=427.414..602.852 rows=1 loops=100)

20. 5,646.272 60,284.100 ↑ 1.0 1 100

Nested Loop (cost=0.85..36,367.66 rows=1 width=12) (actual time=415.122..602.841 rows=1 loops=100)

  • Join Filter: (ca_3.id = h_12.loyal_card_id)
  • Rows Removed by Join Filter: 1387710
21. 0.700 0.700 ↑ 1.0 1 100

Index Scan using t_card_idx2 on t_card ca_3 (cost=0.43..2.85 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=100)

  • Index Cond: (client_id = c.client_id)
22. 54,637.128 54,637.128 ↓ 64.4 1,334,338 104

Append (cost=0.42..36,105.63 rows=20,735 width=16) (actual time=0.019..525.357 rows=1,334,338 loops=104)

  • -> Index Scan using t_roznica_outcome_head_2018_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2018 h_12 (cost=0.42..622.84 rows=190 width=16) (actual time=0.014..34.316 rows= (...)
  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
  • -> Index Scan using t_roznica_outcome_head_2019_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2019 h_13 (cost=0.44..35365.28 rows=20541 width=16) (actual time=0.015..432.650 (...)
  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
  • Rows Removed by Filter: 8571
  • -> Index Scan using t_roznica_outcome_head_defaul_date_pivot_partner_id_employ__idx on t_roznica_outcome_head_default h_15 (cost=0.28..11.26 rows=3 width=16) (actual time=0.017..0.481 rows=1 (...)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_2020_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2020 h_14 (cost=0.15..2.57 rows=1 width=16) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
24. 0.670 0.670 ↑ 1.0 1 134

Index Scan using t_card_period_pkey on t_card_period p (cost=0.43..1.40 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=134)

  • Index Cond: (id = h_12.loyal_period_id)
25. 0.714 0.714 ↓ 1.1 15 119

Seq Scan on t_card_period_type t (cost=0.00..1.40 rows=14 width=4) (actual time=0.004..0.006 rows=15 loops=119)

  • Filter: (price > '0'::numeric)
  • Rows Removed by Filter: 3
26. 0.187 0.200 ↓ 1.1 18 100

Materialize (cost=0.00..1.44 rows=16 width=8) (actual time=0.001..0.002 rows=18 loops=100)

27. 0.013 0.013 ↓ 1.1 18 1

Seq Scan on t_card_period_type ppt (cost=0.00..1.36 rows=16 width=8) (actual time=0.011..0.013 rows=18 loops=1)

28. 0.507 2.400 ↑ 4.0 1 100

Append (cost=0.42..1.87 rows=4 width=12) (actual time=0.013..0.024 rows=1 loops=100)

29. 0.500 0.500 ↓ 0.0 0 100

Index Scan using t_roznica_outcome_head_2018_roznica_outcome_head_id_pivot_p_idx on t_roznica_outcome_head_2018 roh (cost=0.42..0.44 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=100)

  • Index Cond: ((roznica_outcome_head_id = ro.roznica_outcome_head_id) AND (pivot_partner_id = ro.pivot_partner_id))
30. 0.700 0.700 ↑ 1.0 1 100

Index Scan using t_roznica_outcome_head_2019_roznica_outcome_head_id_pivot_p_idx on t_roznica_outcome_head_2019 roh_1 (cost=0.43..0.95 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=100)

  • Index Cond: ((roznica_outcome_head_id = ro.roznica_outcome_head_id) AND (pivot_partner_id = ro.pivot_partner_id))
31. 0.297 0.297 ↓ 0.0 0 99

Index Scan using t_roznica_outcome_head_2020_roznica_outcome_head_id_pivot_p_idx on t_roznica_outcome_head_2020 roh_2 (cost=0.14..0.17 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=99)

  • Index Cond: ((roznica_outcome_head_id = ro.roznica_outcome_head_id) AND (pivot_partner_id = ro.pivot_partner_id))
32. 0.396 0.396 ↓ 0.0 0 99

Index Scan using t_roznica_outcome_head_defaul_roznica_outcome_head_id_pivot_idx on t_roznica_outcome_head_default roh_3 (cost=0.28..0.30 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=99)

  • Index Cond: ((roznica_outcome_head_id = ro.roznica_outcome_head_id) AND (pivot_partner_id = ro.pivot_partner_id))
33.          

SubPlan (for Nested Loop)

34. 4,012.807 40,159.400 ↓ 0.0 0 100

Nested Loop (cost=0.85..36,367.66 rows=1 width=0) (actual time=401.594..401.594 rows=0 loops=100)

  • Join Filter: (ca.id = h.loyal_card_id)
  • Rows Removed by Join Filter: 960866
35. 0.700 0.700 ↑ 1.0 1 100

Index Scan using t_card_idx2 on t_card ca (cost=0.43..2.85 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=100)

  • Index Cond: (client_id = c.client_id)
36. 4,470.222 36,145.893 ↓ 45.0 932,881 103

Append (cost=0.42..36,105.63 rows=20,735 width=4) (actual time=0.018..350.931 rows=932,881 loops=103)

37. 3,346.058 3,346.058 ↓ 577.1 109,651 103

Index Scan using t_roznica_outcome_head_2018_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2018 h (cost=0.42..622.84 rows=190 width=4) (actual time=0.013..32.486 rows=109,651 loops=103)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
38. 28,302.296 28,302.296 ↓ 46.9 962,515 88

Index Scan using t_roznica_outcome_head_2019_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2019 h_1 (cost=0.44..35,365.28 rows=20,541 width=4) (actual time=0.014..321.617 rows=962,515 loops=88)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
  • Rows Removed by Filter: 5888
39. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_2020_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2020 h_2 (cost=0.15..2.57 rows=1 width=4) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
40. 27.317 27.317 ↓ 515.7 1,547 59

Index Scan using t_roznica_outcome_head_defaul_date_pivot_partner_id_employ__idx on t_roznica_outcome_head_default h_3 (cost=0.28..11.26 rows=3 width=4) (actual time=0.016..0.463 rows=1,547 loops=59)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
41. 0.000 0.000 ↓ 0.0 0

Limit (cost=36,367.67..36,367.68 rows=1 width=12) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Sort (cost=36,367.67..36,367.68 rows=1 width=12) (never executed)

  • Sort Key: h_4.outcome_date_sale DESC
43. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..36,367.66 rows=1 width=12) (never executed)

  • Join Filter: (ca_1.id = h_4.loyal_card_id)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using t_card_idx2 on t_card ca_1 (cost=0.43..2.85 rows=1 width=4) (never executed)

  • Index Cond: (client_id = c.client_id)
45. 0.000 0.000 ↓ 0.0 0

Append (cost=0.42..36,105.63 rows=20,735 width=16) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_2018_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2018 h_4 (cost=0.42..622.84 rows=190 width=16) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_2019_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2019 h_5 (cost=0.44..35,365.28 rows=20,541 width=16) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_2020_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2020 h_6 (cost=0.15..2.57 rows=1 width=16) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_defaul_date_pivot_partner_id_employ__idx on t_roznica_outcome_head_default h_7 (cost=0.28..11.26 rows=3 width=16) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
50. 0.000 0.000 ↓ 0.0 0

Limit (cost=36,367.67..36,367.68 rows=1 width=12) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Sort (cost=36,367.67..36,367.68 rows=1 width=12) (never executed)

  • Sort Key: h_8.outcome_date_sale DESC
52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..36,367.66 rows=1 width=12) (never executed)

  • Join Filter: (ca_2.id = h_8.loyal_card_id)
53. 0.000 0.000 ↓ 0.0 0

Index Scan using t_card_idx2 on t_card ca_2 (cost=0.43..2.85 rows=1 width=4) (never executed)

  • Index Cond: (client_id = c.client_id)
54. 0.000 0.000 ↓ 0.0 0

Append (cost=0.42..36,105.63 rows=20,735 width=16) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_2018_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2018 h_8 (cost=0.42..622.84 rows=190 width=16) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_2019_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2019 h_9 (cost=0.44..35,365.28 rows=20,541 width=16) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
57. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_2020_date_pivot_partner_id_employ_id_idx on t_roznica_outcome_head_2020 h_10 (cost=0.15..2.57 rows=1 width=16) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using t_roznica_outcome_head_defaul_date_pivot_partner_id_employ__idx on t_roznica_outcome_head_default h_11 (cost=0.28..11.26 rows=3 width=16) (never executed)

  • Index Cond: (((outcome_date_sale)::date >= '2018-10-01'::date) AND ((outcome_date_sale)::date <= (ro.outcome_date_sale)::date))
  • Filter: (outcome_date_sale < ro.outcome_date_sale)
Planning time : 7.401 ms
Execution time : 100,456.945 ms