Postgres 12

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

DROP TABLE IF EXISTS time_series;

CREATE TEMPORARY TABLE time_series (
    id SERIAL PRIMARY KEY,
    created TIMESTAMP WITH TIME ZONE NOT NULL,
    category TEXT,
    value INT
);

CREATE UNIQUE INDEX ON time_series (created, category);

INSERT INTO time_series (created, category, value)
VALUES ('2000-01-01 00:00:00Z', 'foo', 1),
       ('2000-01-01 12:00:00Z', 'bar', 5),
       ('2000-01-02 00:00:00Z', 'bar', 5),
       ('2000-01-02 12:34:45Z', 'bar', 2),
       ('2000-01-03 00:00:00Z', 'bar', 3),
       ('2000-01-04 00:00:00Z', 'bar', 3),
       ('2000-01-04 11:11:11Z', 'foo', 4),
       ('2000-01-04 22:22:22Z', 'bar', 5),
       ('2000-01-04 23:23:23Z', 'bar', 4),
       ('2000-01-05 00:00:00Z', 'foo', 1);

(SELECT created, value
   FROM time_series
  WHERE category = 'bar'
    AND created < '2000-01-02 06:00:00Z'
  ORDER BY created DESC
  LIMIT 1)
 UNION
(SELECT created, value
   FROM time_series
  WHERE category = 'bar'
    AND created >= '2000-01-02 06:00:00Z'
    AND created < '2000-01-04 12:00:00Z')
 UNION
(SELECT created, value
   FROM time_series
  WHERE category = 'bar'
    AND created >= '2000-01-04 12:00:00Z'
  ORDER BY created
  LIMIT 1)
 ORDER BY created;

10 rows affected

created	value
2000-01-02 00:00:00+00	5
2000-01-02 12:34:45+00	2
2000-01-03 00:00:00+00	3
2000-01-04 00:00:00+00	3
2000-01-04 22:22:22+00	5