Satalabaha Postgres <satalabaha.postgres@gmail.com> writes: > 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.
So you need to find out what's happening in the trigger. Perhaps auto_explain with auto_explain.log_nested_statements enabled would give some insight.
I suspect there might be a permissions problem causing schema1_u to not be allowed to "see" the statistics for table_b, resulting in a bad plan choice for the FK enforcement query; but that's just a guess.
regards, tom lane
Hi Tom,
We did enable auto_explain and auto_explain.log_nested_statements and apart from the insert statement we couldn't find any other SQL's causing that was taking more time.