From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Very newbie question |
Date: | 2023-10-23 15:50:47 |
Message-ID: | a3f8878b-6ba2-4754-8639-50a3d2ec2b44@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/23/23 10:13, Олег Самойлов wrote:
> Back pardon, but I have a very newbie question. I have a partitioned table, partitioned by primary bigint key, size of partition 10000000. I need to get the number of partition which need to archive, which has all rows are olden then 3 month. Here is query:
>
> SELECT id/10000000 as partition
> FROM delivery
> GROUP BY partition
> HAVING max(created_at) < CURRENT_DATE - '3 month'::interval;
>
> The 'id/10000000 as partition' is a number of the partition, it later will be used inside the partition name.
> The query runs long by sequence scan. Has anyone any ideas how to rewrite query so it will use any index?
Maybe:
SELECT DISTINCT id/10000000 as partition
FROM delivery
WHERE max(created_at) < CURRENT_DATE - '3 month'::interval;
I haven't tried it, though.
--
Born in Arizona, moved to Babylonia.
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2023-10-23 16:49:04 | Re: PostgreSQL inheritance vs https://wiki.postgresql.org/wiki/Don%27t_Do_This |
Previous Message | Олег Самойлов | 2023-10-23 15:45:10 | Re: Very newbie question |