Postgres 14

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 messages (id int, verbosity int);
INSERT INTO messages VALUES
  (1, 20)
, (2, 20)
, (3, 20)
, (4, 30)
, (5, 100);

CREATE TABLE



INSERT 0 5


SELECT m.id, sum(m1.verbosity) AS total
FROM   messages m
JOIN   messages m1 ON m1.id <= m.id
WHERE  m.verbosity <= 70  -- optional, to avoid pointless evaluation
GROUP  BY m.id
HAVING SUM(m1.verbosity) < 70
ORDER  BY id;


id
total


1
20

2
40

3
60



SELECT 3


-- simple CTE
WITH cte AS (
   SELECT *, sum(verbosity) OVER (ORDER BY id) AS total
   FROM   messages
   )
SELECT *
FROM   cte
WHERE  total < 70
ORDER  BY id;


id
verbosity
total


1
20
20

2
20
40

3
20
60



SELECT 3


-- rCTE
WITH RECURSIVE cte AS (
   (
   SELECT id, verbosity, verbosity AS total
   FROM   messages
   WHERE  verbosity < 70
   ORDER  BY id
   LIMIT  1
   )

   UNION ALL 
   SELECT c1.id, c1.verbosity, c.total + c1.verbosity 
   FROM   cte c
   JOIN   LATERAL (
      SELECT *
      FROM   messages
      WHERE  id > c.id
      ORDER  BY id
      LIMIT  1
      ) c1 ON  c1.verbosity < 70 - c.total
   WHERE c.total < 70
   )
SELECT *
FROM   cte
ORDER  BY id;


id
verbosity
total


1
20
20

2
20
40

3
20
60



SELECT 3