API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 19th, 2019
194
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.36 KB | None | 0 0
raw download clone embed print report
  1. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, TIMING, SUMMARY)
  2. SELECT
  3. SUM(CASE WHEN just_pass = 1 THEN 1 ELSE 0 END) point,
  4. SUM(CASE WHEN week_of_birthday = TRUE THEN 1 ELSE 0 END) birthday,
  5. SUM(CASE WHEN fresh_grad = 1 THEN 1 ELSE 0 end) fresher,
  6. SUM(CASE WHEN intervention_type::INT = 2 OR intervention_type::INT = 3 THEN 1 ELSE 0 END) attention
  7. FROM
  8. (
  9. SELECT
  10. checkins.student_id, intercepts.intervention_type ,max(evaluation_date), just_pass,
  11. compute_week_of_birthday(student_birthdate, 4 , 'US/Central') as week_of_birthday,
  12. CASE
  13. WHEN student_enrolment_date NOTNULL AND student_enrolment_date >= '2017-01-29' AND student_enrolment_date < '2016-11-30'
  14. THEN 1 ELSE 0 END AS fresh_grad
  15. FROM
  16. (
  17. SELECT
  18. student_id
  19. FROM
  20. checkin_table
  21. WHERE
  22. house_id = 9001
  23. AND
  24. timestamp> '2019-06-11 01:00:40' AND timestamp<= '2019-06-11 01:00:50'
  25. GROUP BY
  26. student_id
  27. )
  28. checkins
  29. LEFT JOIN
  30. students
  31. ON
  32. checkins.student_id = students.student_id
  33. LEFT JOIN
  34. students_points points
  35. ON
  36. checkins.student_id = points.student_id
  37. LEFT JOIN
  38. (
  39. select
  40. record_id, student_id, intervention_type, intervention_date
  41. FROM
  42. intervention_table
  43. WHERE
  44. intervention_date
  45. IN
  46. (
  47. SELECT
  48. MAX(intervention_date)
  49. FROM
  50. intervention_table
  51. GROUP BY
  52. student_id
  53. )
  54. ) AS intercepts
  55. ON
  56. checkins.student_id = intercepts.student_id
  57. WHERE
  58. date_part('year',age(student_birthdate)) >=18
  59. AND
  60. lower(registration_type_description) !~* '.*temporary.*'
  61. GROUP BY
  62. checkins.student_id, students.student_enrolment_date, student_birthdate, just_pass, intercepts.intervention_type
  63. ) AS result
  64. WHERE
  65. max IN
  66. (
  67. SELECT
  68. evaluation_date
  69. FROM
  70. students_points
  71. ORDER BY
  72. evaluation_date DESC LIMIT 1
  73. )
  74. OR
  75. max ISNULL;
  76. QUERY PLAN
  77. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  78. Aggregate (cost=74433.83..74433.84 rows=1 width=32) (actual time=0.081..0.081 rows=1 loops=1)
  79. Output: sum(CASE WHEN (result.just_pass = 1) THEN 1 ELSE 0 END), sum(CASE WHEN result.week_of_birthday THEN 1 ELSE 0 END), sum(CASE WHEN (result.fresh_grad = 1) THEN 1 ELSE 0 END), sum(CASE WHEN (((result.intervention_type)::integer = 2) OR ((result.intervention_type)::integer = 3)) THEN 1 ELSE 0 END)
  80. Buffers: shared hit=20
  81. -> Subquery Scan on result (cost=74412.92..74432.98 rows=34 width=15) (actual time=0.079..0.079 rows=0 loops=1)
  82. Output: result.student_id, result.intervention_type, result.max, result.just_pass, result.week_of_birthday, result.fresh_grad, students.student_enrolment_date, students.student_birthdate
  83. Filter: ((hashed SubPlan 1) OR (result.max IS NULL))
  84. Buffers: shared hit=20
  85. -> GroupAggregate (cost=74412.31..74431.52 rows=68 width=35) (actual time=0.079..0.079 rows=0 loops=1)
  86. Output: checkin_table.student_id, intervention_table.intervention_type, max(points.evaluation_date), points.just_pass, compute_week_of_birthday(students.student_birthdate, 4, 'US/Central'::text), CASE WHEN ((students.student_enrolment_date IS NOT NULL) AND (students.student_enrolment_date >= '2017-01-29'::date) AND (students.student_enrolment_date < '2016-11-30'::date)) THEN 1 ELSE 0 END, students.student_enrolment_date, students.student_birthdate
  87. Group Key: checkin_table.student_id, students.student_enrolment_date, students.student_birthdate, points.just_pass, intervention_table.intervention_type
  88. Buffers: shared hit=20
  89. -> Sort (cost=74412.31..74412.48 rows=68 width=30) (actual time=0.078..0.078 rows=0 loops=1)
  90. Output: checkin_table.student_id, intervention_table.intervention_type, points.just_pass, students.student_enrolment_date, students.student_birthdate, points.evaluation_date
  91. Sort Key: checkin_table.student_id, students.student_enrolment_date, students.student_birthdate, points.just_pass, intervention_table.intervention_type
  92. Sort Method: quicksort Memory: 25kB
  93. Buffers: shared hit=20
  94. -> Nested Loop Left Join (cost=70384.64..74410.24 rows=68 width=30) (actual time=0.035..0.035 rows=0 loops=1)
  95. Output: checkin_table.student_id, intervention_table.intervention_type, points.just_pass, students.student_enrolment_date, students.student_birthdate, points.evaluation_date
  96. Buffers: shared hit=6
  97. -> Nested Loop Left Join (cost=70384.08..74151.91 rows=1 width=22) (actual time=0.035..0.035 rows=0 loops=1)
  98. Output: checkin_table.student_id, intervention_table.intervention_type, students.student_birthdate, students.student_enrolment_date
  99. Buffers: shared hit=6
  100. -> Nested Loop (cost=8.90..25.46 rows=1 width=16) (actual time=0.034..0.034 rows=0 loops=1)
  101. Output: checkin_table.student_id, students.student_birthdate, students.student_enrolment_date
  102. Buffers: shared hit=6
  103. -> Group (cost=8.46..8.47 rows=2 width=8) (actual time=0.034..0.034 rows=0 loops=1)
  104. Output: checkin_table.student_id
  105. Group Key: checkin_table.student_id
  106. Buffers: shared hit=6
  107. -> Sort (cost=8.46..8.47 rows=2 width=8) (actual time=0.033..0.033 rows=0 loops=1)
  108. Output: checkin_table.student_id
  109. Sort Key: checkin_table.student_id
  110. Sort Method: quicksort Memory: 25kB
  111. Buffers: shared hit=6
  112. -> Append (cost=0.00..8.45 rows=2 width=8) (actual time=0.027..0.027 rows=0 loops=1)
  113. Buffers: shared hit=6
  114. -> Seq Scan on public.checkin_table (cost=0.00..0.00 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)
  115. Output: checkin_table.student_id
  116. Filter: ((checkin_table.checkin_time > '2019-06-11 01:00:40+00'::timestamp with time zone) AND (checkin_table.checkin_time <= '2019-06-11 01:00:50+00'::timestamp with time zone) AND (checkin_table.house_id = 9001))
  117. -> Index Scan using checkins_y2019_m6_house_id_timestamp_idx on public.checkins_y2019_m6 (cost=0.43..8.45 rows=1 width=8) (actual time=0.024..0.024 rows=0 loops=1)
  118. Output: checkins_y2019_m6.student_id
  119. Index Cond: ((checkins_y2019_m6.house_id = 9001) AND (checkins_y2019_m6.checkin_time > '2019-06-11 01:00:40+00'::timestamp with time zone) AND (checkins_y2019_m6.checkin_time <= '2019-06-11 01:00:50+00'::timestamp with time zone))
  120. Buffers: shared hit=6
  121. -> Index Scan using students_student_id_idx on public.students (cost=0.43..8.47 rows=1 width=16) (never executed)
  122. Output: students.student_type, students.house_id, students.registration_id, students.registration_status, students.registration_type_status, students.total_non_core_subjects, students.registration_source, students.total_core_subjects, students.registration_type_description, students.non_access_flag, students.address_1, students.address_2, students.city, students.state, students.zipcode, students.registration_created_date, students.registration_activation_date, students.registration_cancellation_request_date, students.registration_termination_date, students.cancellation_reason, students.monthly_dues, students.student_id, students.student_type, students.student_status, students.student_first_name, students.student_last_name, students.email_address, students.student_enrolment_date, students.student_birthdate, students.student_gender, students.insert_time, students.update_time
  123. Index Cond: (students.student_id = checkin_table.student_id)
  124. Filter: ((lower((students.registration_type_description)::text) !~* '.*temporary.*'::text) AND (date_part('year'::text, age((CURRENT_DATE)::timestamp with time zone, (students.student_birthdate)::timestamp with time zone)) >= '18'::double precision))
  125. -> Nested Loop (cost=70375.18..74126.43 rows=2 width=14) (never executed)
  126. Output: intervention_table.intervention_type, intervention_table.student_id
  127. Join Filter: (intervention_table.intervention_date = (max(intervention_table_1.intervention_date)))
  128. -> HashAggregate (cost=70374.75..70376.75 rows=200 width=8) (never executed)
  129. Output: (max(intervention_table_1.intervention_date))
  130. Group Key: max(intervention_table_1.intervention_date)
  131. -> HashAggregate (cost=57759.88..63366.49 rows=560661 width=16) (never executed)
  132. Output: max(intervention_table_1.intervention_date), intervention_table_1.student_id
  133. Group Key: intervention_table_1.student_id
  134. -> Seq Scan on public.intervention_table intervention_table_1 (cost=0.00..46349.25 rows=2282125 width=16) (never executed)
  135. Output: intervention_table_1.record_id, intervention_table_1.student_id, intervention_table_1.intervention_type, intervention_table_1.intervention_date, intervention_table_1.house_id, intervention_table_1.teacher_id, intervention_table_1.expiration_date, intervention_table_1.point_at_intervention
  136. -> Index Scan using intervention_table_student_id_idx on public.intervention_table (cost=0.43..18.70 rows=4 width=22) (never executed)
  137. Output: intervention_table.record_id, intervention_table.student_id, intervention_table.intervention_type, intervention_table.intervention_date, intervention_table.house_id, intervention_table.teacher_id, intervention_table.expiration_date, intervention_table.point_at_intervention
  138. Index Cond: (checkin_table.student_id = intervention_table.student_id)
  139. -> Index Scan using students_latest_points_idx on public.students_points points (cost=0.57..257.65 rows=68 width=16) (never executed)
  140. Output: points.record_id, points.student_id, points.registration_id, points.house_id, points.evaluation_date, points.just_pass, points.five_star, points.star1, points.star2, points.star3, points.star4, points.updatedate
  141. Index Cond: (checkin_table.student_id = points.student_id)
  142. SubPlan 1
  143. -> Limit (cost=0.57..0.61 rows=1 width=4) (never executed)
  144. Output: students_points.evaluation_date
  145. -> Index Only Scan Backward using students_points_evaluation_date_idx on public.students_points (cost=0.57..4984972.45 rows=111195272 width=4) (never executed)
  146. Output: students_points.evaluation_date
  147. Heap Fetches: 0
  148. Planning time: 23.993 ms
  149. Execution time: 17.648 ms
  150. (72 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!