From: | Олег Самойлов <splarv(at)ya(dot)ru> |
---|---|
To: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Very newbie question |
Date: | 2023-10-23 15:42:05 |
Message-ID: | E3DE0722-C26F-4BC4-A7EB-7387B56EFAAF@ya.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Well, get list of partitions and later scan one by one all 100 partitions is too simple. :) I am interesting is here more elegant way? Any rewriting the query, any creating an index are permitted.
> 23 окт. 2023 г., в 18:25, Francisco Olarte <folarte(at)peoplecall(dot)com> написал(а):
>
> On Mon, 23 Oct 2023 at 17:14, Олег Самойлов <splarv(at)ya(dot)ru> 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?
>
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Олег Самойлов | 2023-10-23 15:45:10 | Re: Very newbie question |
Previous Message | Tom Browder | 2023-10-23 15:41:07 | Re: Presentation tools used ? |