MIME-Version: 1.0
References: <CAD=mzVXZiDmAsTE1XiXnq1DFHM22uwnD8jckmBN_bTakk3u2AQ@mail.gmail.com>
 <CAApHDvq3mwhsiB69eXt6OJJL5=7UnbE74gWvWVJN57yEhrSkkg@mail.gmail.com>
In-Reply-To: <CAApHDvq3mwhsiB69eXt6OJJL5=7UnbE74gWvWVJN57yEhrSkkg@mail.gmail.com>
From: sud <suds1434@gmail.com>
Date: Tue, 20 Aug 2024 12:39:01 +0530
Message-ID: <CAD=mzVW2px2fQrtMYdDfkqgLYv-4iKHC_H1-D5UhmJ7BCBRcBg@mail.gmail.com>
Subject: Re: Insert query performance
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000581a9c0620181a77"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVW2px2fQrtMYdDfkqgLYv-4iKHC_H1-D5UhmJ7BCBRcBg%40mail.gmail.com>
Precedence: bulk

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

On Mon, Aug 19, 2024 at 4:33=E2=80=AFPM David Rowley <dgrowleyml@gmail.com>=
 wrote:

> On Mon, 19 Aug 2024 at 19:48, sud <suds1434@gmail.com> wrote:
> > In a version 15.4 postgres database, Is it possible that , if we have
> two big range partition tables with foreign key relationships between the=
m,
> insert into the child table can cause slowness if we don't have foreign k=
ey
> index present in the child table? Basically it need to make sure the new
> row already added to parent partition table or not.
>
> Having an index on the referencing columns is only useful for DELETEs
> and UPDATEs affecting the foreign key column(s).  For INSERTs to the
> referencing table, technically having indexes there would only slow
> down inserts due to the additional overhead of having to maintain the
> index, however, the overhead of having the index might be fairly
> minuscule when compared to performing a CASCADE UPDATE or DELETE to
> the referencing table when the DDL is performed on the referenced
> table.
>
> > And if there is any possible way(example query tracing etc) to get the
> underlying system queries which gets triggered as part of the main insert
> query? For example in above scenario, postgres must be executing some que=
ry
> to check if the incoming  row to the child table already exists in the
> parent table or not?
>
> EXPLAIN ANALYZE will list the time it took to execute the foreign key
> trigger in the "Trigger for constraint" section.
>
> David
>


Thank you so much David.

If I get it correct , the index on the foreign key mainly helps improve the
deletes/updates performance of the parent table , if the same FK column
gets impacted from the parent table. (This might be the reason why our
detach partition in the parent table runs long and never completes as we
have no foreign key indexed).

However, my initial understanding of "*having the FK index will improve the
insert performance in the child table*" is not accurate it seems. Rather as
you mentioned it may negatively impact the loading/insert performance
because it has to now update the additional index in each insert. In case
of insert into child table, to ensure if the child row is already present
in the parent ,  it just scans the parent by the Primary key of the parent
table (which is be default indexed) and thus it doesn't need an index in
the child table foreign keys or having an index in the foreign key in the
child table won't help the constraint validation faster. Please correct me
if my understanding is wrong here.

Additionally as you mentioned "explain analyze" will show a section on how
much time it really takes for the constraint validation , I can see that
section now. But it seems it will really need that INSERT statement to be
executed and that we can't really do in production as that will physically
insert data into the table. So do you mean to just do the "explain analyze"
for the INSERT query and capture the plan and then do the rollback?  And in
our case it's a row by row insert happening , so we will see if we can
club/sum that "constraint validation" time for a handful if insert somehow
to get a better idea on the percentage of time we really spent in the
constraint validation.

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Mon, Aug 19, 2024 at 4:33=E2=80=AFPM D=
avid Rowley &lt;<a href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.co=
m</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
>On Mon, 19 Aug 2024 at 19:48, sud &lt;<a href=3D"mailto:suds1434@gmail.com=
" target=3D"_blank">suds1434@gmail.com</a>&gt; wrote:<br>
&gt; In a version 15.4 postgres database, Is it possible that , if we have =
two big range partition tables with foreign key relationships between them,=
 insert into the child table can cause slowness if we don&#39;t have foreig=
n key index present in the child table? Basically it need to make sure the =
new row already added to parent partition table or not.<br>
<br>
Having an index on the referencing columns is only useful for DELETEs<br>
and UPDATEs affecting the foreign key column(s).=C2=A0 For INSERTs to the<b=
r>
referencing table, technically having indexes there would only slow<br>
down inserts due to the additional overhead of having to maintain the<br>
index, however, the overhead of having the index might be fairly<br>
minuscule when compared to performing a CASCADE UPDATE or DELETE to<br>
the referencing table when the DDL is performed on the referenced<br>
table.<br>
<br>
&gt; And if there is any possible way(example query tracing etc) to get the=
 underlying system queries which gets triggered as part of the main insert =
query? For example in above scenario, postgres must be executing some query=
 to check if the incoming=C2=A0 row to the child table already exists in th=
e parent table or not?<br>
<br>
EXPLAIN ANALYZE will list the time it took to execute the foreign key<br>
trigger in the &quot;Trigger for constraint&quot; section.<br>
<br>
David<br></blockquote><div><br></div><div><br></div><div>Thank you so much =
David.<br></div><br>If I get it correct , the index on the foreign key main=
ly helps improve the deletes/updates performance of the parent table , if t=
he same FK column gets impacted from the parent table. (This might be the r=
eason why our detach partition in the parent table runs long and never comp=
letes as we have no foreign key indexed).<br><br>However, my initial unders=
tanding of &quot;<i>having the FK index will improve the insert performance=
 in the child table</i>&quot; is not accurate it seems. Rather as you menti=
oned it may negatively impact the loading/insert performance because it has=
 to now update the additional index in each insert. In case of insert into =
child table, to ensure if the child row is already present in the parent , =
=C2=A0it just scans the parent by the Primary key of the parent table (whic=
h is be default indexed) and thus it doesn&#39;t need an index in the child=
 table foreign keys or having an index in the foreign key in the child tabl=
e won&#39;t help the constraint validation faster. Please correct me if my =
understanding is wrong here.<br><br><div>Additionally as you mentioned &quo=
t;explain analyze&quot; will show a section on how much time it really take=
s for the constraint validation , I can see that section now. But it seems =
it will really need that INSERT statement to be executed and that we can=
9;t really do in production as that will physically insert data into the ta=
ble. So do you mean to just do the &quot;explain analyze&quot; for the INSE=
RT query and capture the plan and then do the rollback?=C2=A0 And in our ca=
se it&#39;s a row by row insert=C2=A0happening , so we will see if we can c=
lub/sum that &quot;constraint validation&quot; time for a handful=C2=A0if i=
nsert somehow to get a better idea on the percentage of time we really spen=
t in the constraint validation.</div></div></div>

--000000000000581a9c0620181a77--