Re: Very newbie question - Mailing list pgsql-general

From Toomas
Subject Re: Very newbie question
Date
Msg-id AC086CDE-EA2A-41E3-A5BA-9D858BE5783B@gmail.com
Whole thread Raw
In response to Very newbie question  (Олег Самойлов <splarv@ya.ru>)
Responses Re: Very newbie question
List pgsql-general
There is no reason to use index. The query has neither WHERE nor ORDER BY clause.

Toomas

> On 23. Oct 2023, at 18:13, Олег Самойлов <splarv@ya.ru> wrote:
>
> Back pardon, but I have a very newbie question. I have a partitioned table, partitioned by primary bigint key, size
ofpartition 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?
>




pgsql-general by date:

Previous
From: Олег Самойлов
Date:
Subject: Very newbie question
Next
From: Francisco Olarte
Date:
Subject: Re: Very newbie question