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, Олег Самойлов <splarv@xxxxx> написал(а):
>
> 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.
