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 test ( 
  event      int
, created_at timestamp
);

INSERT INTO test (event, created_at) VALUES
(16, '2021-11-11 11:15'),(16, '2021-11-11 11:15'),(16, '2021-11-13 10:45'),(16, '2021-11-16 10:40'),
(16, '2021-11-23 11:15'),(16, '2021-11-23 11:15'),(16, '2021-12-10 13:00'),(16, '2021-12-15 13:25'),
(16, '2021-12-15 13:25'),(16, '2021-12-15 13:25'),(16, '2021-12-20 13:15'),(16, '2021-12-23 13:15'),
(16, '2021-12-31 13:25'),(16, '2022-02-05 15:00'),(16, '2022-02-05 15:00'),(16, '2022-02-13 17:15'),
(16, '2022-02-21 10:09'),
(43, '2021-11-26 11:00'),(43, '2022-01-01 15:00'),(43, '2022-04-13 10:07'),(43, '2022-04-13 10:09'),
(43, '2022-04-13 10:09'),(43, '2022-04-13 10:09'),(43, '2022-04-13 10:10'),(43, '2022-04-13 10:10'),
(43, '2022-04-13 10:47'),(43, '2022-05-11 20:25'),
(75, '2021-10-21 12:50'),(75, '2021-11-02 12:50'),(75, '2021-11-18 11:15'),(75, '2021-11-18 12:55'),
(75, '2021-11-18 16:35'),(75, '2021-11-24 11:00'),(75, '2021-12-01 11:00'),(75, '2021-12-14 13:25'),
(75, '2021-12-15 13:35'),(75, '2021-12-26 13:25'),(75, '2022-01-31 15:00'),(75, '2022-02-02 15:30'),
(75, '2022-02-03 15:00'),(75, '2022-02-17 15:00'),(75, '2022-02-17 15:00'),(75, '2022-02-18 15:00'),
(75, '2022-02-23 15:00'),(75, '2022-02-25 15:00'),(75, '2022-03-04 10:46'),(75, '2022-03-08 21:05');

-- essential index !!!
CREATE INDEX test_event_created_at_idx ON test (event, created_at);

CREATE TABLE



INSERT 0 47



CREATE INDEX


-- requested count
WITH RECURSIVE hit AS (
   (
   SELECT event, created_at
   FROM   test
   ORDER  BY event, created_at
   LIMIT  1
   )
   
   UNION ALL
   SELECT t.*
   FROM   hit h
   CROSS  JOIN LATERAL (
      SELECT t.event, t.created_at
      FROM   test t
      WHERE  (t.event, t.created_at)
           > (h.event, h.created_at + interval '14 days')
      ORDER  BY t.event, t.created_at
      LIMIT  1
      ) t
   )
SELECT count(*) AS hits FROM hit;


hits


15



SELECT 1


-- show qualifying events
WITH RECURSIVE hit AS (
   (
   SELECT event, created_at
   FROM   test
   ORDER  BY event, created_at
   LIMIT  1
   )
   
   UNION ALL
   SELECT t.*
   FROM   hit h
   CROSS  JOIN LATERAL (
      SELECT t.event, t.created_at
      FROM   test t
      WHERE  (t.event, t.created_at)
           > (h.event, h.created_at + interval '14 days')
      ORDER  BY t.event, t.created_at
      LIMIT  1
      ) t
   )
TABLE hit;


event
created_at


16
2021-11-11 11:15:00

16
2021-12-10 13:00:00

16
2021-12-31 13:25:00

16
2022-02-05 15:00:00

16
2022-02-21 10:09:00

43
2021-11-26 11:00:00

43
2022-01-01 15:00:00

43
2022-04-13 10:07:00

43
2022-05-11 20:25:00

75
2021-10-21 12:50:00

75
2021-11-18 11:15:00

75
2021-12-14 13:25:00

75
2022-01-31 15:00:00

75
2022-02-17 15:00:00

75
2022-03-04 10:46:00



SELECT 15


-- all index-only scans even with minimal table!
-- SET enable_seqscan = OFF;  -- to show possible index usage for minimal table

-- plan with count
EXPLAIN
WITH RECURSIVE hit AS (
   (
   SELECT event, created_at
   FROM   test
   ORDER  BY event, created_at
   LIMIT  1
   )
   
   UNION ALL
   SELECT t.*
   FROM   hit h
   CROSS  JOIN LATERAL (
      SELECT t.event, t.created_at
      FROM   test t
      WHERE  (t.event, t.created_at)
           > (h.event, h.created_at + interval '14 days')
      ORDER  BY t.event, t.created_at
      LIMIT  1
      ) t
   )
SELECT count(*) AS hits FROM hit;


QUERY PLAN


Aggregate  (cost=74.70..74.71 rows=1 width=8)

CTE hit

->  Recursive Union  (cost=0.14..72.43 rows=101 width=12)

->  Limit  (cost=0.14..0.41 rows=1 width=12)

->  Index Only Scan using test_event_created_at_idx on test  (cost=0.14..12.85 rows=47 width=12)

->  Nested Loop  (cost=0.14..7.00 rows=10 width=12)

->  WorkTable Scan on hit h  (cost=0.00..0.20 rows=10 width=12)

->  Limit  (cost=0.14..0.66 rows=1 width=12)

->  Index Only Scan using test_event_created_at_idx on test t  (cost=0.14..8.42 rows=16 width=12)

Index Cond: (ROW(event, created_at) > ROW(h.event, (h.created_at + '14 days'::interval)))

->  CTE Scan on hit  (cost=0.00..2.02 rows=101 width=0)



EXPLAIN

event	created_at
16	2021-11-11 11:15:00
16	2021-12-10 13:00:00
16	2021-12-31 13:25:00
16	2022-02-05 15:00:00
16	2022-02-21 10:09:00
43	2021-11-26 11:00:00
43	2022-01-01 15:00:00
43	2022-04-13 10:07:00
43	2022-05-11 20:25:00
75	2021-10-21 12:50:00
75	2021-11-18 11:15:00
75	2021-12-14 13:25:00
75	2022-01-31 15:00:00
75	2022-02-17 15:00:00
75	2022-03-04 10:46:00