Postgres 16

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 inquiries (
  id          bigint NOT NULL
, identity_id bigint NOT NULL
, received_at timestamp NOT NULL
, purpose_id  bigint NOT NULL
, location_id bigint NOT NULL
);

CREATE TABLE

-- enable index-only scans
VACUUM inquiries;

VACUUM

-- to show index usage in this minimal example
SET enable_seqscan = off;

SET

-- org. index
CREATE INDEX "inquiries_DATE_index" ON inquiries USING btree (date(received_at), location_id, purpose_id, identity_id);

CREATE INDEX

-- org query
EXPLAIN
SELECT DATE(received_at), location_id, purpose_id, COUNT(DISTINCT identity_id)
FROM inquiries
WHERE (DATE(received_at) >= '2023-11-01'::date)
  AND (DATE(received_at) <= '2023-11-30'::date + 1)
GROUP BY 1, 2, 3;

QUERY PLAN

GroupAggregate  (cost=0.12..8.17 rows=1 width=28)

  Group Key: date(received_at), location_id, purpose_id

  ->  Index Scan using "inquiries_DATE_index" on inquiries  (cost=0.12..8.15 rows=1 width=28)

        Index Cond: ((date(received_at) >= '2023-11-01'::date) AND (date(received_at) <= '2023-12-01'::date))

EXPLAIN

-- enable index-only scans with "INCLUDE (received_at)"
DROP INDEX "inquiries_DATE_index";
CREATE INDEX inquiries_date_plus_idx ON inquiries
  (date(received_at), location_id, purpose_id, identity_id) INCLUDE (received_at);

DROP INDEX

CREATE INDEX

-- org query (note "Index Only Scan"!)
EXPLAIN
SELECT date(received_at), location_id, purpose_id, COUNT(DISTINCT identity_id)
FROM inquiries
WHERE (date(received_at) >= '2023-11-01'::date)
  AND (date(received_at) <= '2023-11-30'::date + 1)
GROUP BY 1, 2, 3;

QUERY PLAN

GroupAggregate  (cost=0.12..8.17 rows=1 width=28)

  Group Key: (date(received_at)), location_id, purpose_id

  ->  Index Only Scan using inquiries_date_plus_idx on inquiries  (cost=0.12..8.15 rows=1 width=28)

        Index Cond: (((date(received_at)) >= '2023-11-01'::date) AND ((date(received_at)) <= '2023-12-01'::date))

EXPLAIN

-- better idx
DROP INDEX inquiries_date_plus_idx;
CREATE INDEX inquiries_received_at_plus_idx ON inquiries
  (received_at, location_id, purpose_id, identity_id);

DROP INDEX

CREATE INDEX

-- better query (note "Index Only Scan"!)
EXPLAIN
SELECT received_at::date, location_id, purpose_id, COUNT(DISTINCT identity_id)
FROM   inquiries
WHERE  received_at >= '2023-11-01'::date     -- $1
AND    received_at <  '2023-11-30'::date + 1 -- $2 + 1  -- oparator "<" !
GROUP  BY 1, 2, 3;

QUERY PLAN

GroupAggregate  (cost=8.16..8.18 rows=1 width=28)

  Group Key: ((received_at)::date), location_id, purpose_id

  ->  Sort  (cost=8.16..8.16 rows=1 width=28)

        Sort Key: ((received_at)::date), location_id, purpose_id, identity_id

        ->  Index Only Scan using inquiries_received_at_plus_idx on inquiries  (cost=0.12..8.15 rows=1 width=28)

              Index Cond: ((received_at >= '2023-11-01'::date) AND (received_at < '2023-12-01'::date))

EXPLAIN

-- probably better, yet (still "Index Only Scan"!)
EXPLAIN
SELECT received_at::date, location_id, purpose_id, count(*) AS dist_identies
FROM  (
   SELECT DISTINCT ON (1,2,3,4)
          received_at::date, location_id, purpose_id, identity_id
   FROM   inquiries
   WHERE  received_at >= '2023-11-01'::date     -- $1
   AND    received_at <  '2023-11-30'::date + 1 -- $2 + 1  -- oparator "<" !
   ) sub
GROUP  BY 1, 2, 3;

QUERY PLAN

GroupAggregate  (cost=8.16..8.20 rows=1 width=28)

  Group Key: ((inquiries.received_at)::date), inquiries.location_id, inquiries.purpose_id

  ->  Unique  (cost=8.16..8.17 rows=1 width=28)

        ->  Sort  (cost=8.16..8.16 rows=1 width=28)

              Sort Key: ((inquiries.received_at)::date), inquiries.location_id, inquiries.purpose_id, inquiries.identity_id

              ->  Index Only Scan using inquiries_received_at_plus_idx on inquiries  (cost=0.12..8.15 rows=1 width=28)

                    Index Cond: ((received_at >= '2023-11-01'::date) AND (received_at < '2023-12-01'::date))

EXPLAIN