Fast search of row ID that has "nearest" "created" timetsamp to NOW() - INTERVAL 'N days' (a-la loose index scan technique)

gistfile1.txt

-- Use this in your matview/report as a tool to fetch all IDs that "slightly" overlap "N last days" of data

-- This recursive CTE walks back step by step, assuming that orders of "id" and "created" fields are the same.
-- Step size is 50,000 here,
-- time window is 30 days
-- (edit both params and/or embed them to SQL code itself, if needed).
-- [TODO] Warning: in case of missing IDs in the table, line #26 can lead to a bit incorrect result

SET user_vars.reporting_window = '30 day';
SET user_vars.reporting_scan_stepsize = 50000;

WITH RECURSIVE h AS
(
	(SELECT id, created, '*' as hop
	FROM mytable 
	WHERE 
		id >= (select max(id) from mytable) - current_setting('user_vars.reporting_scan_stepsize')::int8 
	ORDER BY id ASC 
	LIMIT 1) 

	UNION ALL

	(SELECT pn.id, pn.created, h.hop || '*' as hop
	FROM 
		mytable pn
		JOIN h ON pn.id = h.id - current_setting('user_vars.reporting_scan_stepsize')::int8
	WHERE 
		pn.created >= NOW() - current_setting('user_vars.reporting_window')::interval
	ORDER BY id ASC 
	LIMIT 1) 
)
SELECT 
	id, created
FROM
	mytable
WHERE id >= (select min(id) from h) - current_setting('user_vars.reporting_scan_stepsize')::int8
ORDER BY id ASC
LIMIT 1
;

-- Usage: "... WHERE id >= (select id from (SELECT id FROM (***THIS_BIG_CTE_SQL***) AS inh)"
-- Also, it's convenient to create several VIEWs with that CTE – for various time windows 
-- (e.g.: '30 days', '7 days', '1 day'), and then use them in reporting.