MIME-Version: 1.0
References: 
 <CADzfLwW9QczZW-E=McxcjUv0e5VMDctQNETbgao0K-SimVhFPA@mail.gmail.com>
 <202505181556.3n6oiowvntyr@alvherre.pgsql>
 <CADzfLwXKtriMnfCNVGNH2ahwXaByjo-QOMWiDTU-9WZqh+zQ5g@mail.gmail.com>
 <CADzfLwW5bDWSxjHK7mqX8Lewki3+5FBydBC+nVcxg4xMGKscyw@mail.gmail.com>
 <CAMAof6-4xaV3QE2ErYJaJhu6qjFn99sWyo_HQeBhHikZM3GexA@mail.gmail.com>
 <CADzfLwXocKhpW3eFP1oScz+m+1XJ3bpi9QmVpoqC9RX9oyX=UA@mail.gmail.com>
In-Reply-To: 
 <CADzfLwXocKhpW3eFP1oScz+m+1XJ3bpi9QmVpoqC9RX9oyX=UA@mail.gmail.com>
From: Sergey Sargsyan <sergey.sargsyan.2001@gmail.com>
Date: Mon, 16 Jun 2025 23:21:47 +0300
Message-ID: 
 <CAMAof695VA+mbVRhWCTus=E0WnsMAQyqXxfOTohbcb7VUHSP4g@mail.gmail.com>
Subject: Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements
To: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Cc: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>,
	Andres Freund <andres@anarazel.de>, Michael Paquier <michael@paquier.xyz>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Andrey Borodin <amborodin86@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
	Matthias van de Meent <boekewurm+postgres@gmail.com>
Content-Type: multipart/alternative; boundary="000000000000db74740637b62529"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMAof695VA%2BmbVRhWCTus%3DE0WnsMAQyqXxfOTohbcb7VUHSP4g%40mail.gmail.com>
Precedence: bulk

--000000000000db74740637b62529
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thank you for the information. Tomorrow, I will also run a few tests to
measure the time required to collect tids from the index; however, since I
do not work with vanilla postgres, the results may vary.

If the results indicate that this procedure is time-consuming, I maybe will
develop an additional patch specifically for b-tree indexes, as they are
the default and most commonly used type.

Best regards,
Sergey


On Mon, Jun 16, 2025, 11:01=E2=80=AFPM Mihail Nikalayeu <mihailnikalayeu@gm=
ail.com>
wrote:

> Hello, Sergey!
>
> > I think it's to avoid duplicate errors when adding tuples from STIP to
> the main index,
> > but couldn't we just suppress that error during validation and skip the
> new tuple insertion if it already exists?
>
> In some cases, it is not possible:
> =E2=80=93 Some index types (GiST, GIN, BRIN) do not provide an easy way t=
o
> detect such duplicates.
> =E2=80=93 When we are building a unique index, we cannot simply skip
> duplicates, because doing so would also skip the rows that should
> prevent the unique index from being created (unless we add extra logic
> for B-tree indexes to compare TIDs as well).
>
> > The main index may get huge after building, and iterating over it in a
> single thread and then sorting tids can be time consuming.
> My tests indicate that the overhead is minor compared with the time
> spent scanning the heap and building the index itself.
>
> > At least I guess one can skip it when STIP is empty.
> Yes, that=E2=80=99s a good idea; I=E2=80=99ll add it later.
>
> > p.s. I noticed that `stip.c` has a lot of functions that don't follow
> the Postgres coding style of return type on separate line.
> Hmm... I=E2=80=99ll fix that as well.
>
> Best regards,
> Mikhail.
>

--000000000000db74740637b62529
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div>Thank you for the information.  Tomorrow, I will als=
o run a few tests to measure the time required to collect tids from the ind=
ex; however, since I do not work with vanilla postgres, the results may var=
y.<div dir=3D"auto"></div><div dir=3D"auto"><br></div><div dir=3D"auto">If =
the results indicate that this procedure is time-consuming, I maybe will de=
velop an additional patch specifically for b-tree indexes, as they are the =
default and most commonly used type.</div><div dir=3D"auto"><br></div><div =
dir=3D"auto">Best regards,=C2=A0</div><div dir=3D"auto">Sergey</div><br><br=
><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D=
"gmail_attr">On Mon, Jun 16, 2025, 11:01=E2=80=AFPM Mihail Nikalayeu &lt;<a=
 href=3D"mailto:mihailnikalayeu@gmail.com">mihailnikalayeu@gmail.com</a>&gt=
; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .=
8ex;border-left:1px #ccc solid;padding-left:1ex">Hello, Sergey!<br>
<br>
&gt; I think it&#39;s to avoid duplicate errors when adding tuples from STI=
P to the main index,<br>
&gt; but couldn&#39;t we just suppress that error during validation and ski=
p the new tuple insertion if it already exists?<br>
<br>
In some cases, it is not possible:<br>
=E2=80=93 Some index types (GiST, GIN, BRIN) do not provide an easy way to<=
br>
detect such duplicates.<br>
=E2=80=93 When we are building a unique index, we cannot simply skip<br>
duplicates, because doing so would also skip the rows that should<br>
prevent the unique index from being created (unless we add extra logic<br>
for B-tree indexes to compare TIDs as well).<br>
<br>
&gt; The main index may get huge after building, and iterating over it in a=
 single thread and then sorting tids can be time consuming.<br>
My tests indicate that the overhead is minor compared with the time<br>
spent scanning the heap and building the index itself.<br>
<br>
&gt; At least I guess one can skip it when STIP is empty.<br>
Yes, that=E2=80=99s a good idea; I=E2=80=99ll add it later.<br>
<br>
&gt; p.s. I noticed that `stip.c` has a lot of functions that don&#39;t fol=
low the Postgres coding style of return type on separate line.<br>
Hmm... I=E2=80=99ll fix that as well.<br>
<br>
Best regards,<br>
Mikhail.<br>
</blockquote></div></div></div>

--000000000000db74740637b62529--