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 1sH695-003DO8-RE for pgsql-general@arkaria.postgresql.org; Tue, 11 Jun 2024 18:23:00 +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 1sH694-008XnL-BC for pgsql-general@arkaria.postgresql.org; Tue, 11 Jun 2024 18:22:59 +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 1sH693-008Xlv-Ti for pgsql-general@lists.postgresql.org; Tue, 11 Jun 2024 18:22:58 +0000 Received: from mail-ua1-x935.google.com ([2607:f8b0:4864:20::935]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sH692-000paX-9p for pgsql-general@lists.postgresql.org; Tue, 11 Jun 2024 18:22:57 +0000 Received: by mail-ua1-x935.google.com with SMTP id a1e0cc1a2514c-804c45a47b6so1745232241.2 for ; Tue, 11 Jun 2024 11:22:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718130175; x=1718734975; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=7joxWAGJjiTSEMlf9ePJFoUUjLDB7rb5VLvu26pdKps=; b=HEZA5MyJ/vMk58pwXQPQjOgU1sUQ4tsheWfGsYGx0xrbjXUsStItKWUd7GXfHaKq+0 CMnbYPURQpIQxbCzkcX2wrVR1DWUwvQ7QDJw31Sde5ulHakb+EvEgr2s2YL543AEzZcp 6qTTPkmqxXjdBNXczBgeAqSQGQFDABnG3mHGBGyox/VcqkdmyUg0pTs3YTUbuyVzmIET zLvPFwz5gf/SUaTKfub1EaFmHEDLref3l12UxIGU2rO494fjqoF2KUIeFD35i5hvfH8d ovmuMHjH8svqc2SavOzVj4uPSthjmiOtv+C2Yvlapw9dBTbXtYD3zLAsJ5y3Abt8B12Q HACg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718130175; x=1718734975; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=7joxWAGJjiTSEMlf9ePJFoUUjLDB7rb5VLvu26pdKps=; b=xMJ8rTTiorBNK9N96nvGX5kILyPLnfo0CXsdAytPXGwc2TkMlOe3wxq/Jfzpbt0Ag8 fKRBNZZ08ndOMl2YNLF281k5V+ukQgSzL/K3wq3ACJsgTfbKk9F/2OytALnMDlNegu+e s1ml6xU3ioYebCi1qw1qdzd0WmZGRqygpvsb3zTchGJpGtnw78djtQCObRyiFHnxZdi5 3S2t8AGKWz+gkppYb2LKye9Y3ADSqpPaBmYj2j1YKWG3ksgO42UY06XuCbErpZalpwKV mA5fk8zbCc6h4ZVXAUkLNriP/rSYq1bwHtHHveP6WxQgcHKl6D66KC0zSe32lW7r/SzA vSlg== X-Gm-Message-State: AOJu0YzNiyobP/G1LrKdqviLfLXxl9URqmXAiPqzN9Ujx64QbI8/ALlA OCjB1BRV5GecxlETL9An84hkADYHwTP2x2MjrzuAzz9o3RQbNS1Ek4qfOTyxMcqhzX1qJ2gHJIM FZeqdIEWDTPt63hi0m0QL0tsZru0= X-Google-Smtp-Source: AGHT+IF5vAqxBXw2owRQWAmUYXnV3Tt3yROfgsXydoyR4jwxvWjjW60Epfi4XLGV8+3/290l7wrBRPTctKoe01Oh0CU= X-Received: by 2002:a05:6122:6104:b0:4eb:5f7f:219f with SMTP id 71dfb90a1353d-4eb5f7f2e80mr8770681e0c.16.1718130175377; Tue, 11 Jun 2024 11:22:55 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: sud Date: Tue, 11 Jun 2024 23:52:37 +0530 Message-ID: Subject: Re: Creating big indexes To: Lok P Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000c80df7061aa15a1e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c80df7061aa15a1e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Jun 9, 2024 at 1:40=E2=80=AFPM Lok P wrote: > On Sun, Jun 9, 2024 at 10:39=E2=80=AFAM Lok P wrot= e: > >> >> >> On Sun, Jun 9, 2024 at 10:36=E2=80=AFAM sud wrote: >> >>> >>> You can first create the index on the table using the "On ONLY"keyword= , >>> something as below. >>> >>> CREATE INDEX idx ON ONLY tab(col1); >>> >>> Then create indexes on each partition in "concurrently" from multiple >>> sessions in chunks. >>> >>> CREATE INDEX CONCURRENTLY idx_1 ON tab_part1(col1); >>> CREATE INDEX CONCURRENTLY idx_2 ON tab_part2(col1); >>> >>> After this step finishes the table level index which was created in the >>> first step will be in valid state automatically. >>> >>> >> Thank you so much. >> Should we also tweak the parameters related to the parallelism and memor= y >> as I mentioned in the first post? >> > > Additionally ,is it also possible to drop the indexes also from the big > partition table efficiently? To avoid the "transaction id wrap around" or > "table bloat" when the index drop runs for longer duration? > > I have never tried , but I think you can do "drop index concurrently" from multiple sessions at same time for each of the partitions to make the drop index finish quicker, similar to the "create index" statement as mentioned above. Others may comment. --000000000000c80df7061aa15a1e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Sun, Jun 9, 2024 at 1:40=E2=80=AFP= M Lok P <loknath.73@gmail.com> wrote:

<= /div>
O= n Sun, Jun 9, 2024 at 10:36=E2=80=AFAM sud <suds1434@gmail.com> wrote:

=C2=A0You can first create t= he index on the table using the "On ONLY"keyword, something as be= low.

CREATE INDEX idx ON ONLY tab(col1);
=

Then create indexes on each partition in "concurre= ntly" from multiple sessions in chunks.

CREAT= E INDEX CONCURRENTLY idx_1=C2=A0 =C2=A0 ON tab_part1(col1);
CREATE INDEX= CONCURRENTLY idx_2=C2=A0 =C2=A0 ON tab_part2(col1);

After this step finishes the table level index which was created in = the first step will be in valid state automatically.


Thank you so much.
Sho= uld we also tweak the parameters related to the parallelism and memory as I= mentioned in the first post?=C2=A0

=
Additionally ,is it also possible to drop the indexes also from = the big partition table efficiently? To avoid the "transaction id wrap= around" or "table bloat" when the=C2=A0index drop runs for = longer duration?
=C2=A0

I have never tried , but I think you can do "drop index concu= rrently" from multiple sessions at same time for each of the partition= s to make the drop index finish quicker, similar to the "create index&= quot; statement as mentioned above. Others may comment.
--000000000000c80df7061aa15a1e--