Well, get list of partitions and later scan one by one all 100 partitions is too simple. :) I am interesting is here
moreelegant way? Any rewriting the query, any creating an index are permitted.
> 23 окт. 2023 г., в 18:25, Francisco Olarte <folarte@peoplecall.com> написал(а):
>
> On Mon, 23 Oct 2023 at 17:14, Олег Самойлов <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?
>
> You should send an explain of your query, and your table and index definition.
>
> Unless you are tied to do this in one query, and assuming you have an
> index by "created_at", I normally do these kind of things by:
> 1.- Get list of partitions, sort oldest first.
> 2.- do "select created_at from $partition order by created at desc
> limit 1", which normally is just an index lookup, and compare
> client-side.
> You can do the date math in the database too. Also, rhs of the
> comparison seems to be date, if created_at is timestamp you may be
> blocking the optimizer for some things.
>
> Francisco Olarte.