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 <fola...@peoplecall.com> > написал(а): > > On Mon, 23 Oct 2023 at 17:14, Олег Самойлов <spl...@ya.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.
