explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xbqZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 14.962 ↑ 1.0 100 1

Limit (cost=8.12..3,916.97 rows=100 width=298) (actual time=0.425..14.962 rows=100 loops=1)

2. 0.431 14.936 ↑ 3,271.9 100 1

Nested Loop Left Join (cost=8.12..12,789,535.85 rows=327,194 width=298) (actual time=0.424..14.936 rows=100 loops=1)

  • Join Filter: (ppt.id = ppp.type_id)
  • Rows Removed by Join Filter: 1800
3. 8.395 13.605 ↑ 3,271.9 100 1

Nested Loop Left Join (cost=8.12..7,055,760.83 rows=327,194 width=129) (actual time=0.368..13.605 rows=100 loops=1)

4. 0.053 5.210 ↑ 3,271.9 100 1

Nested Loop Left Join (cost=1.28..4,803,922.95 rows=327,194 width=121) (actual time=0.138..5.210 rows=100 loops=1)

  • Join Filter: (cpt.id = ro.period_type_id)
  • Rows Removed by Join Filter: 509
5. 0.078 5.057 ↑ 3,271.9 100 1

Nested Loop (cost=1.28..4,719,969.36 rows=327,194 width=50) (actual time=0.120..5.057 rows=100 loops=1)

6. 0.025 3.779 ↑ 3,273.0 100 1

Nested Loop (cost=0.85..4,548,832.52 rows=327,303 width=50) (actual time=0.080..3.779 rows=100 loops=1)

7. 0.098 3.454 ↑ 3,303.6 100 1

Nested Loop (cost=0.57..4,450,553.62 rows=330,356 width=54) (actual time=0.066..3.454 rows=100 loops=1)

8. 0.056 0.056 ↑ 7,131.6 100 1

Seq Scan on tp_roznica_outcome_service ro (cost=0.00..21,154.43 rows=713,156 width=46) (actual time=0.009..0.056 rows=100 loops=1)

  • Filter: (service_id = 3)
  • Rows Removed by Filter: 2
9. 0.215 3.300 ↑ 7.0 1 100

Append (cost=0.57..6.14 rows=7 width=12) (actual time=0.016..0.033 rows=1 loops=100)

10. 1.600 1.600 ↑ 1.0 1 100

Index Scan using tp_roznica_outcome_head_idx2 on tp_roznica_outcome_head roh (cost=0.57..2.49 rows=1 width=12) (actual time=0.015..0.016 rows=1 loops=100)

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

Index Scan using tp_roznica_outcome_head_2010_idx1 on tp_roznica_outcome_head_2010 roh_1 (cost=0.42..0.47 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))
12. 0.198 0.198 ↓ 0.0 0 99

Index Scan using tp_roznica_outcome_head_2011_idx1 on tp_roznica_outcome_head_2011 roh_2 (cost=0.43..0.52 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=99)

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

Index Scan using tp_roznica_outcome_head_2012_idx1 on tp_roznica_outcome_head_2012 roh_3 (cost=0.43..0.58 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=99)

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

Index Scan using tp_roznica_outcome_head_2013_idx1 on tp_roznica_outcome_head_2013 roh_4 (cost=0.43..0.68 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=99)

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

Index Scan using tp_roznica_outcome_head_2014_idx1 on tp_roznica_outcome_head_2014 roh_5 (cost=0.56..1.05 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))
16. 0.198 0.198 ↓ 0.0 0 99

Index Scan using tp_roznica_outcome_head_2015_idx1 on tp_roznica_outcome_head_2015 roh_6 (cost=0.29..0.31 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=99)

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

Index Scan using t_partner_pharm_pkey on t_partner_pharm pp (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (pharm_id = roh.pivot_partner_id)
  • Filter: (NOT is_test)
18. 1.200 1.200 ↑ 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.012..0.012 rows=1 loops=100)

  • Index Cond: (scan_code = ro.club_card_scan_code)
19. 0.089 0.100 ↑ 3.0 6 100

Materialize (cost=0.00..1.47 rows=18 width=79) (actual time=0.000..0.001 rows=6 loops=100)

20. 0.011 0.011 ↑ 1.8 10 1

Seq Scan on t_card_period_type cpt (cost=0.00..1.38 rows=18 width=79) (actual time=0.005..0.011 rows=10 loops=1)

21. 0.000 0.000 ↓ 0.0 0 100

Index Scan using t_card_period_pkey on t_card_period ppp (cost=6.84..6.88 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=100)

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

SubPlan (for Index Scan)

23. 0.000 8.300 ↓ 0.0 0 100

Limit (cost=6.40..6.41 rows=1 width=12) (actual time=0.083..0.083 rows=0 loops=100)

24. 0.200 8.300 ↓ 0.0 0 100

Sort (cost=6.40..6.41 rows=1 width=12) (actual time=0.083..0.083 rows=0 loops=100)

  • Sort Key: h_3.outcome_date_sale DESC
  • Sort Method: quicksort Memory: 25kB
25. 0.168 8.100 ↓ 0.0 0 100

Nested Loop (cost=1.43..6.39 rows=1 width=12) (actual time=0.081..0.081 rows=0 loops=100)

26. 0.340 7.600 ↓ 3.0 3 100

Nested Loop (cost=1.29..6.23 rows=1 width=16) (actual time=0.043..0.076 rows=3 loops=100)

27. 0.156 5.700 ↓ 4.0 4 100

Nested Loop (cost=0.87..5.72 rows=1 width=12) (actual time=0.028..0.057 rows=4 loops=100)

28. 0.900 0.900 ↑ 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.008..0.009 rows=1 loops=100)

  • Index Cond: (client_id = c.client_id)
29. 0.129 4.644 ↓ 3.0 3 129

Append (cost=0.44..2.87 rows=1 width=16) (actual time=0.015..0.036 rows=3 loops=129)

30. 4.515 4.515 ↓ 3.0 3 129

Index Scan using tp_roznica_outcome_head_idx1 on tp_roznica_outcome_head h_3 (cost=0.44..2.86 rows=1 width=16) (actual time=0.015..0.035 rows=3 loops=129)

  • Index Cond: ((loyal_card_id = ca_3.id) AND ((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: 1
31. 1.560 1.560 ↑ 1.0 1 390

Index Scan using t_card_period_pkey on t_card_period p (cost=0.43..0.51 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=390)

  • Index Cond: (id = h_3.loyal_period_id)
32. 0.332 0.332 ↓ 0.0 0 332

Index Scan using t_card_period_type_pkey on t_card_period_type t (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=332)

  • Index Cond: (id = p.type_id)
  • Filter: (price > '0'::numeric)
  • Rows Removed by Filter: 1
33. 0.093 0.100 ↑ 1.0 18 100

Materialize (cost=0.00..1.47 rows=18 width=8) (actual time=0.000..0.001 rows=18 loops=100)

34. 0.007 0.007 ↑ 1.0 18 1

Seq Scan on t_card_period_type ppt (cost=0.00..1.38 rows=18 width=8) (actual time=0.003..0.007 rows=18 loops=1)

35.          

SubPlan (for Nested Loop Left Join)

36. 0.040 0.800 ↑ 1.0 1 100

Nested Loop (cost=0.87..5.72 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=100)

37. 0.300 0.300 ↑ 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.003..0.003 rows=1 loops=100)

  • Index Cond: (client_id = c.client_id)
38. 0.115 0.460 ↑ 1.0 1 115

Append (cost=0.44..2.87 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=115)

39. 0.345 0.345 ↑ 1.0 1 115

Index Scan using tp_roznica_outcome_head_idx1 on tp_roznica_outcome_head h (cost=0.44..2.86 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=115)

  • Index Cond: ((loyal_card_id = ca.id) AND ((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: 0
40. 0.000 0.000 ↓ 0.0 0

Limit (cost=5.73..5.74 rows=1 width=12) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Sort (cost=5.73..5.74 rows=1 width=12) (never executed)

  • Sort Key: h_1.outcome_date_sale DESC
42. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..5.72 rows=1 width=12) (never executed)

43. 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)
44. 0.000 0.000 ↓ 0.0 0

Append (cost=0.44..2.87 rows=1 width=16) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Index Scan using tp_roznica_outcome_head_idx1 on tp_roznica_outcome_head h_1 (cost=0.44..2.86 rows=1 width=16) (never executed)

  • Index Cond: ((loyal_card_id = ca_1.id) AND ((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)
46. 0.000 0.000 ↓ 0.0 0

Limit (cost=5.73..5.74 rows=1 width=12) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Sort (cost=5.73..5.74 rows=1 width=12) (never executed)

  • Sort Key: h_2.outcome_date_sale DESC
48. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..5.72 rows=1 width=12) (never executed)

49. 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)
50. 0.000 0.000 ↓ 0.0 0

Append (cost=0.44..2.87 rows=1 width=16) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using tp_roznica_outcome_head_idx1 on tp_roznica_outcome_head h_2 (cost=0.44..2.86 rows=1 width=16) (never executed)

  • Index Cond: ((loyal_card_id = ca_2.id) AND ((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 : 14.456 ms
Execution time : 15.283 ms