public inbox for [email protected]  

help / color / mirror / Atom feed

Insert query performance
2+ messages / 2 participants
[nested] [flat]

* Insert query performance
@ 2024-08-19 07:48  sud <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: sud @ 2024-08-19 07:48 UTC (permalink / raw)
  To: pgsql-general <[email protected]>

Hello All,

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't have foreign key
index present in the child table? Basically it need to make sure the new
row already added to parent partition table or not.


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  row to the child table already exists in the
parent table or not?


Regards

Sud


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Insert query performance
@ 2024-08-19 11:03  David Rowley <[email protected]>
  parent: sud <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: David Rowley @ 2024-08-19 11:03 UTC (permalink / raw)
  To: sud <[email protected]>; +Cc: pgsql-general <[email protected]>

On Mon, 19 Aug 2024 at 19:48, sud <[email protected]> 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 them, insert into the child table can cause slowness if we don't have foreign key 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 query 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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-08-19 11:03 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-19 07:48 Insert query performance sud <[email protected]>
2024-08-19 11:03 ` David Rowley <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox