Hi Julien, Yes both in both the cases the same tables are accessed. Yes we tried indexing as well, but we have the same behaviour.
Regards, Satalabha On Sun, 4 Jun 2023 at 16:51, Julien Rouhaud <rjuju...@gmail.com> wrote: > Hi, > > On Sun, Jun 04, 2023 at 02:04:52PM +0530, Satalabaha Postgres wrote: > > > > DB : postgres 14. > > > > We are experiencing weird performance issue of one simple insert > statement > > taking several minutes to insert data. The application calls insert > > statement via stored procedure show mentioned below. > > > > The select query in the insert returns about 499 rows. However, this > insert > > statement when executed from application user i.e. schema1_u takes close > to > > 8 minutes. When the same insert statement gets executed as postgres > user > > it takes less than 280 ms. Both the executions use the same execution > plan > > with only difference that when schema1_u executes the SQL, we observe > > "Trigger for constraint fk_con_tablea: time=426499.314 calls=499" taking > > more time. Both the parent and child tables are not big in size. There is > > no table bloat etc for both of these tables. Below are the details. > > Is there any way we can identify why as postgres user the insert > statement > > works fine and why not with application user schema1_u? > > Are you sure that in both case the exact same tables are accessed? It > looks > like schema1_u is checking the rows for a way bigger table. The usual > answer > is to create a proper index for the table referenced by the FK. >
