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 groups (
  group_id int PRIMARY KEY
, groupname text NOT NULL
);

INSERT INTO groups (group_id, groupname)
VALUES
  (1, 'foo')
, (2, 'bar')
, (3, 'baz')
, (4, 'bam');

CREATE TABLE counter (
  id bigserial PRIMARY KEY
, ts timestamp NOT NULL
, group_id bigint NOT NULL
);

CREATE INDEX ts_index       ON counter (ts);
CREATE INDEX group_id_index ON counter (group_id);
CREATE INDEX comp_1_index   ON counter (ts, group_id);
CREATE INDEX comp_2_index   ON counter (group_id, ts);

INSERT INTO counter (ts, group_id)
VALUES
  ('2022-03-02 01:00:00', 1),
  ('2022-03-02 02:00:00', 2),
  ('2022-03-02 03:00:00', 1),
  ('2022-03-02 04:00:00', 3),
  ('2022-03-02 05:00:00', 2) ;

INSERT INTO counter (ts, group_id)
VALUES
  (date '2022-03-02' + interval '1 year', 1),
  (date '2022-03-02' + interval '1 year', 2),
  (date '2022-03-02' + interval '1 year', 1),
  (date '2022-03-02' + interval '1 year', 3),
  (date '2022-03-02' - interval '5 year', 4) ;

INSERT INTO counter (ts, group_id)
SELECT a.ts + interval '2 year', a.group_id
FROM   counter a
CROSS  JOIN counter b;

INSERT INTO counter (ts, group_id)
SELECT a.ts - interval '4 month', a.group_id
FROM   counter a
CROSS  JOIN counter b;

ANALYZE groups;
ANALYZE counter;

CREATE TABLE

INSERT 0 4

CREATE TABLE

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

INSERT 0 5

INSERT 0 5

INSERT 0 100

INSERT 0 12100

ANALYZE

ANALYZE

group_id
1
2
3

SELECT 3