Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1quxCj-002I2n-R3 for pgsql-general@arkaria.postgresql.org; Mon, 23 Oct 2023 15:50:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1quxCh-00BQ7p-LO for pgsql-general@arkaria.postgresql.org; Mon, 23 Oct 2023 15:50:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1quxCh-00BQ6X-9o for pgsql-general@lists.postgresql.org; Mon, 23 Oct 2023 15:50:55 +0000 Received: from mail-yw1-x1129.google.com ([2607:f8b0:4864:20::1129]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1quxCb-001vZD-Bw for pgsql-general@lists.postgresql.org; Mon, 23 Oct 2023 15:50:54 +0000 Received: by mail-yw1-x1129.google.com with SMTP id 00721157ae682-579de633419so35344277b3.3 for ; Mon, 23 Oct 2023 08:50:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1698076248; x=1698681048; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=3jj9eiH4V3enV+L3mqGalM9N8se7oMcKYNAaCSNlwCk=; b=dziT7H7q/RBpe3uMO4QN+ZDWaLTrdE5onPzenBCEcsHB6F6G3hRYMJCHNDS/ESAI0F 8rvnIDFQIf0pb293OYGJxu1OF2OWvnkCSYgbg4xHE6ZAS013yDu+PvV/tL3GQp85QyPq QD5Vq2iev48rDGAgyNhhO3VV+XgdPNCWyJXZ/hgvYVkQpSCR3rpARAaFRszwiMm/eM6E qoW8He9l1ebJ3vkW2w93Cs5+eern2UO3ittyCvzSrDyXq5DunXEN8VQTBgnQJJ8AcRfF RgptVjlfVj13V/ePeImmIr7RxlxGhiPxjAf5r0J+uc62FvpYT/lqIOwgbKjaeiqF7f3S oa2A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1698076248; x=1698681048; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=3jj9eiH4V3enV+L3mqGalM9N8se7oMcKYNAaCSNlwCk=; b=QF0mGkQ8cBzURHilL8SAy64TKxR991svf/FYS6hBWbB2ejYO4Y2lNK47gHqU9VuXLa aR3m48Dcmw2LH/8G03+9cJQMFlzyTBF5dgZhmJzhb6M+zXw+DLO8lEmBQf23A9FCxCUV rSebEnm98kN14hKkA7ynS3OIQevFYUNh5LqN7MpQ97kk8uzLDCqDsy/QKjNK7uQlQ14Q wHt7YuvwVBdBYkdv0SN/DBYCXj/um8DGbmD1PnCPf2lYAqiz+70PPnWDytDZo/Z+ZlvD VyN3ZfVLptZHoPu2PqKP+Ti6k2dRC6y3J2uTXUFlbsU5j3Zc+OyUfmHzNf8B74zox6YY AJqQ== X-Gm-Message-State: AOJu0YyDrkoIAlC72GvkSK1ehUU/Rxnp+qN8XRz1obaQA+LOJfAI3Sg8 dHVeaN3YsvNhj9SHO1LjqqpKw8BOag4= X-Google-Smtp-Source: AGHT+IEEubZoqCx7tRZlw4pw94gKbz6Dfj/hyJr1J88VHxfp842f9ZyTQRx+VnmpRMcjDb1diaA47g== X-Received: by 2002:a05:690c:d95:b0:5a7:c646:b3ed with SMTP id da21-20020a05690c0d9500b005a7c646b3edmr12607790ywb.34.1698076248212; Mon, 23 Oct 2023 08:50:48 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:e077:351f:e0e7:6965? ([2600:1700:bd2c:b810:e077:351f:e0e7:6965]) by smtp.googlemail.com with ESMTPSA id j126-20020a0df984000000b0058038e6609csm3233777ywf.74.2023.10.23.08.50.47 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 23 Oct 2023 08:50:47 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------qwUWvphdAdJPko8xDRoHtJZ4" Message-ID: Date: Mon, 23 Oct 2023 10:50:47 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Very newbie question Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------qwUWvphdAdJPko8xDRoHtJZ4 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/23/23 10:13, Олег Самойлов 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? Maybe: SELECT DISTINCT id/10000000 as partition FROM delivery WHERE max(created_at) < CURRENT_DATE - '3 month'::interval; I haven't tried it, though. -- Born in Arizona, moved to Babylonia. --------------qwUWvphdAdJPko8xDRoHtJZ4 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/23/23 10:13, Олег Самойлов 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?

Maybe:
SELECT DISTINCT id/10000000 as partition
FROM delivery
WHERE max(created_at) < CURRENT_DATE - '3 month'::interval;
I haven't tried it, though.

--
Born in Arizona, moved to Babylonia.
--------------qwUWvphdAdJPko8xDRoHtJZ4--