Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


CREATE TABLE student_attendence_log
  (student_id int PRIMARY KEY, "date" varchar, attendence_status varchar);

INSERT INTO student_attendence_log
  (student_id, "date", attendence_status)
VALUES
  (1, '2022-01-08 04:05:06', 'present'),
  (2, '2022-01-08 04:05:06', 'present'),
  (3, '2022-01-08 04:05:06', 'tardy'),
  (4, '2022-01-08 04:05:06', 'present'),
  (5, '2022-01-08 04:05:06', 'absent'),
  (6, '2022-01-08 04:05:06', 'present'),
  (7, '2022-01-08 04:05:06', 'tardy'),
  (8, '2022-01-08 04:05:06', 'present'),
  (9, '2022-01-08 04:05:06', 'tardy')
;

CREATE TABLE student_demographic
  (student_id int PRIMARY KEY, grade_level varchar);

INSERT INTO student_demographic
  (student_id, grade_level)
VALUES
  (1, '11'),
  (2, '12'),
  (3, '12'),
  (4, '10'),
  (5, '11'),
  (6, '11'),
  (7, '12'),
  (8, '10'),
  (9, '10')
;
9 rows affected

9 rows affected

WITH merged AS (
  SELECT "date"::date, a.student_id, a.attendence_status, b.grade_level
  FROM student_demographic b
  LEFT JOIN student_attendence_log a
  ON a.student_id = b.student_id
  WHERE "date"::date = to_date('2022-01-08','YYYY-MM-DD')
), 
present_vs_total AS (
  SELECT 
    SUM(CASE 
      WHEN attendence_status = 'present' 
      THEN 1 
      ELSE 0 
      END) AS present,
    COUNT(*) AS total,
    grade_level
  FROM merged
  GROUP BY grade_level
)
SELECT 
  grade_level,
  ROUND((present::float / total::float)::numeric,2) AS attendence_rate
FROM present_vs_total


grade_level
attendence_rate


10
0.67

11
0.67

12
0.33