| From: | Олег Самойлов <splarv(at)ya(dot)ru> |
|---|---|
| To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Very newbie question |
| Date: | 2023-10-23 15:13:58 |
| Message-ID: | B3F74121-CECE-42D2-B0AB-5D7E6F3A466A@ya.ru |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Toomas | 2023-10-23 15:23:33 | Re: Very newbie question |
| Previous Message | Bruce Momjian | 2023-10-23 14:40:10 | Re: Presentation tools used ? |