Re: Very newbie question

[Ron]

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.