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

create table posts
(
    id serial not null,
    created_at date,
    constraint posts_pkey
        primary key (id)
);
/* random source data */
WITH RECURSIVE 
cte AS (SELECT CAST('2019-01-01' AS DATE) "date"
        UNION ALL
        SELECT "date" + 1 + CAST(random() AS INT) FROM cte WHERE "date" < CAST('2019-02-01' AS DATE))
INSERT INTO posts (created_at) 
SELECT "date" 
FROM cte
21 rows affected

/* look for generated source data */
SELECT * FROM posts


id
created_at


1
2019-01-01

2
2019-01-02

3
2019-01-04

4
2019-01-05

5
2019-01-07

6
2019-01-08

7
2019-01-10

8
2019-01-11

9
2019-01-13

10
2019-01-15

11
2019-01-17

12
2019-01-18

13
2019-01-19

14
2019-01-21

15
2019-01-22

16
2019-01-23

17
2019-01-25

18
2019-01-26

19
2019-01-28

20
2019-01-30

21
2019-02-01


/* Get N=3 (set into cte1.N as a literal) records 
   before and after the record with id=10 
   (set in cte1 WHERE condition as a literal) */
WITH 
/* set parameters */
cte1 AS ( SELECT created_at, 3 N 
          FROM posts WHERE id=10 ),
/* prepare data */
cte2 AS ( SELECT posts.created_at,
                 LAG(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () lbound, 
                 LEAD(posts.created_at, cte1.N, CAST('2000-01-01' AS DATE)) OVER () ubound
          FROM posts, cte1 )
/* select needed data */
SELECT posts.* 
FROM posts, cte1, cte2
WHERE posts.created_at BETWEEN cte2.lbound AND cte2.ubound
  AND cte2.created_at = cte1.created_at
ORDER BY posts.id


id
created_at


7
2019-01-10

8
2019-01-11

9
2019-01-13

10
2019-01-15

11
2019-01-17

12
2019-01-18

13
2019-01-19

id	created_at
1	2019-01-01
2	2019-01-02
3	2019-01-04
4	2019-01-05
5	2019-01-07
6	2019-01-08
7	2019-01-10
8	2019-01-11
9	2019-01-13
10	2019-01-15
11	2019-01-17
12	2019-01-18
13	2019-01-19
14	2019-01-21
15	2019-01-22
16	2019-01-23
17	2019-01-25
18	2019-01-26
19	2019-01-28
20	2019-01-30
21	2019-02-01