On Mon, 23 Oct 2023 at 17:14, Олег Самойлов <splarv@xxxxx> 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.
