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 prompt (
  id serial PRIMARY KEY
, "dateKey" date NULL  -- type date!
);

CREATE TABLE  post (
  id serial PRIMARY KEY
, "authorId" integer NOT NULL
, "promptId" integer NOT NULL
, content text NOT NULL
-- , CONSTRAINT post_fk_authorid FOREIGN KEY ("authorId") REFERENCES author (id)
, CONSTRAINT post_fk_promptid FOREIGN KEY ("promptId") REFERENCES prompt (id)
);

CREATE TABLE



CREATE TABLE


-- Prompts
INSERT INTO prompt ("dateKey")
VALUES
  ('20231229')
, ('20231230')
, ('20231231')
, ('20240101')
, ('20240102')
, ('20240103')
, ('20240104')
RETURNING *;

-- Posts
INSERT INTO post ("authorId", "promptId", content)
VALUES
  (1, 1, 'My content1')
, (1, 2, 'My content2')
, (1, 5, 'My content3')
, (1, 6, 'My content4')
, (2, 4, 'Second user content1')
, (2, 5, 'Second user content2')
, (2, 6, 'Second user content3')
, (2, 7, 'Second user content4')
, (3, 1, 'Third user content1')
, (3, 2, 'Third user content2')
RETURNING *;


id
dateKey


1
2023-12-29

2
2023-12-30

3
2023-12-31

4
2024-01-01

5
2024-01-02

6
2024-01-03

7
2024-01-04



INSERT 0 7




id
authorId
promptId
content


1
1
1
My content1

2
1
2
My content2

3
1
5
My content3

4
1
6
My content4

5
2
4
Second user content1

6
2
5
Second user content2

7
2
6
Second user content3

8
2
7
Second user content4

9
3
1
Third user content1

10
3
2
Third user content2



INSERT 0 10


WITH RECURSIVE cte AS (
   SELECT pt."dateKey" AS the_day, p."authorId" AS author_id
   FROM   prompt pt
   JOIN   post  p ON p."promptId" = pt.id
   WHERE  pt."dateKey" = '20240104' -- CURRENT_DATE
   AND    p."authorId" = 2  -- your author here!
   
   UNION ALL
   SELECT c.the_day - 1, p."authorId"   -- assuming no gaps in prompt!
   FROM   cte   c
   JOIN   prompt pt ON pt."dateKey" = c.the_day - 1
   JOIN   post  p  ON p."promptId" = pt.id
   WHERE  p."authorId" = c.author_id
   )
SELECT count(*)
FROM   cte;


count


4



SELECT 1

id	dateKey
1	2023-12-29
2	2023-12-30
3	2023-12-31
4	2024-01-01
5	2024-01-02
6	2024-01-03
7	2024-01-04

id	authorId	promptId	content
1	1	1	My content1
2	1	2	My content2
3	1	5	My content3
4	1	6	My content4
5	2	4	Second user content1
6	2	5	Second user content2
7	2	6	Second user content3
8	2	7	Second user content4
9	3	1	Third user content1
10	3	2	Third user content2