Re: Weird behavior of INSERT QUERY

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux