From: Tom Lane <tgl@sss.pgh.pa.us>
To: Satalabaha Postgres <satalabaha.postgres@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Weird behavior of INSERT QUERY
Date: Sun, 04 Jun 2023 09:26:44 -0400
Message-ID: <3726928.1685885204@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJ_W8nbj4KWOB-N5Y41VoaEkUs9PCEezNK8t+fEE5UJFsACJAQ@mail.gmail.com>
References: <CAJ_W8nbj4KWOB-N5Y41VoaEkUs9PCEezNK8t+fEE5UJFsACJAQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, satalabaha.postgres@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Weird behavior of INSERT QUERY
In-Reply-To: <3726928.1685885204@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox