Okey, I see no one was be able to solve this problem. But I could. May be for
someone this will be useful too. There is solution.
Original query was:
> 23 окт. 2023 г., в 18:13, Олег Самойлов <spl...@ya.ru> написал(а):
>
> SELECT id/10000000 as partition
> FROM delivery
> GROUP BY partition
> HAVING max(created_at) < CURRENT_DATE - '3 month'::interval;
And I was not able to accelerate it by any index, works 5 minutes. Now query is:
SELECT generate_series(min(id)/10000000, max(id)/10000000) AS n FROM delivery)
as part_numbers
WHERE (SELECT max(created_at) from delivery where n*10000000 <=id and
id < (n+1)*10000000)
< CURRENT_DATE-'3 month'::interval;
Return the same (number of partition need to archive), accelerated by two btree
index: on id and created_at. Works very quick, less then second.
