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.