Re: Weird behavior of INSERT QUERY

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 <rjuju123(at)gmail(dot)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.
>