Postgres 15

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?.

CREATE TABLE table_1 (
  person VARCHAR(50),
  start_time INT,
  end_time INT
);


CREATE TABLE


INSERT INTO table_1 (person, start_time, end_time)
VALUES
  ('John', 5, 16),
  ('Mary', 7, 10),
  ('Peter', 8, 12);


INSERT 0 3


WITH numbers AS (
  SELECT generate_series(1, 100) AS number 
  ) 
SELECT
    people
  , min(number) starts_time
  , max(number) end_time
FROM (
    SELECT
        n.number
      , string_agg(t.person,',') as people
    FROM table_1 as t 
    INNER JOIN numbers as n on n.number between t.start_time and t.end_time
    GROUP BY
        n.number
    ) AS p
GROUP BY
    people
ORDER BY
    people


people
starts_time
end_time


John
5
16

John,Mary
7
7

John,Mary,Peter
8
10

John,Peter
11
12



SELECT 4


WITH numbers AS (
  SELECT generate_series(1, 100) AS number
)
SELECT DISTINCT
  array_agg(people) OVER (ORDER BY start_time, end_time, person_range) AS people,
  start_time,
  end_time
FROM (
  SELECT
    t1.start_time,
    t2.end_time,
    t1.people,
    COUNT(t2.people) OVER (ORDER BY t1.start_time, t2.end_time) AS person_range
  FROM (
    SELECT
      t.start_time,
      t.end_time,
      array_agg(t.person) AS people
    FROM table_1 AS t
    GROUP BY t.start_time, t.end_time
    ORDER BY t.start_time, t.end_time
  ) AS t1
  JOIN (
    SELECT
      t.start_time,
      t.end_time,
      array_agg(t.person) AS people
    FROM table_1 AS t
    GROUP BY t.start_time, t.end_time
    ORDER BY t.start_time, t.end_time
  ) AS t2 ON t1.end_time >= t2.start_time AND t1.people @> t2.people
) AS subquery
ORDER BY start_time, end_time;



people
start_time
end_time


{{John}}
5
16

{{John},{Mary}}
7
10

{{John},{Mary},{Peter}}
8
12



SELECT 3