From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Very newbie question |
Date: | 2023-10-26 09:15:00 |
Message-ID: | 20231026091500.5g5iwjadoljcxbot@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2023-10-25 17:48:46 +0200, Olivier Gautherot wrote:
> El mié, 25 oct 2023 16:58, Олег Самойлов <splarv(at)ya(dot)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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2023-10-26 09:50:00 | Re: Disk wait problem... |
Previous Message | Adrian Klaver | 2023-10-26 00:59:41 | Re: Problem with CAST-ing - am I missing something? |