From: | Satalabaha Postgres <satalabaha(dot)postgres(at)gmail(dot)com> |
---|---|
To: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Weird behavior of INSERT QUERY |
Date: | 2023-06-04 11:42:27 |
Message-ID: | CAJ_W8naKBz4LtT1KvvQUVmvVTVuqN3wUHWBDnOvyX+VOS+W+jw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2023-06-04 13:21:37 | Re: Weird behavior of INSERT QUERY |
Previous Message | Julien Rouhaud | 2023-06-04 11:21:08 | Re: Weird behavior of INSERT QUERY |