Postgres 13

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

7 rows affected

WITH RECURSIVE moving_target(initial_timestamp, recurring) AS (
   VALUES (timestamp '2020-06-07', interval '12 days')  -- search term
   )
,  x AS (
   SELECT t.id
          -- advance to the closest day before or at moving target:
        , t_date + ((m_date - t_date) / t_days) * t_days AS t_date
        , t_days
        , m.*
   FROM  (  -- normalize table data
      SELECT id
           , initial_timestamp::date AS t_date
           , EXTRACT ('day' FROM recurring)::int AS t_days
      FROM   tbl
      WHERE  recurring IS NOT NULL  -- exclude!
      ) t
   CROSS  JOIN (  -- normalize input
      SELECT initial_timestamp::date AS m_date
           , EXTRACT ('days' FROM recurring)::int AS m_days
      FROM   moving_target
      ) m
   )
, rcte AS (
   SELECT id, t_date, t_days, m_date, m_days
        , ARRAY[m_date - t_date] AS gaps        -- keep track of gaps
        , CASE
            WHEN t_date = m_date     THEN true  -- found match
            WHEN t_days % m_days = 0 THEN false -- can never match
            WHEN (m_days - t_days) % 2 = 1
             AND t_days % 2 = 0
             AND m_days % 2 = 0      THEN false -- can never match
         -- WHEN <stop conditions?>  THEN false -- hard to determine!
         -- ELSE                          null  -- keep searching
          END AS match
   FROM   x

UNION ALL
   SELECT id, t_date, t_days, m_date, m_days
        , gaps || m_date - t_date
        , CASE
            WHEN t_date = m_date                THEN true
            WHEN (m_date - t_date) = ANY (gaps) THEN false  -- gap repeated!
         -- ELSE                                     null   -- keep searching
          END AS match
   FROM  (
      SELECT id
           , t_date + (((m_date + m_days) - t_date) / t_days) * t_days AS t_date
           , t_days
           , m_date + m_days AS m_date -- + 1 step
           , m_days
           , gaps
      FROM   rcte
      WHERE  match IS NULL
      ) sub
   )
SELECT id, t.initial_timestamp, t.recurring
     , CASE WHEN r.match THEN r.t_date END AS match_date
FROM   rcte r
JOIN   tbl  t USING (id)
WHERE  r.match IS NOT NULL;

id	initial_timestamp	recurring	match_date
11	2020-05-02 00:00:00	2 days	2020-06-07
14	2020-06-01 00:00:00	1 day	2020-06-07
12	2020-05-02 00:00:00	30 days	2020-07-01
24	2020-06-03 00:00:00	10 days	2020-07-13
27	2020-06-02 00:00:00	21 days	null
13	2020-05-01 00:00:00	13 days	2020-10-17