On 2023-10-25 17:48:46 +0200, Olivier Gautherot wrote:
> El mié, 25 oct 2023 16:58, Олег Самойлов <splarv@ya.ru> escribió:
> 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.
[...]
> 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.
[...]
> Your fast solution will work as long as you don't have missing sequences (like
> deleted rows).
Why do you think this would break with missing sequence numbers?
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"