pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWeird behavior of INSERT QUERY
11+ messages / 4 participants
[nested] [flat]
* Weird behavior of INSERT QUERY
@ 2023-06-04 08:34 Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 08:40 ` Re: Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 11:21 ` Re: Weird behavior of INSERT QUERY Julien Rouhaud <rjuju123@gmail.com>
2023-06-04 13:26 ` Re: Weird behavior of INSERT QUERY Tom Lane <tgl@sss.pgh.pa.us>
2023-06-04 14:16 ` Re: Weird behavior of INSERT QUERY Ranier Vilela <ranier.vf@gmail.com>
0 siblings, 4 replies; 11+ messages in thread
From: Satalabaha Postgres @ 2023-06-04 08:34 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi Listers,
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?
Stored Procedure:
====================
CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double
precision, parcreatedby text)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
insert into table_a
(
ROWVERSION,
CREATED,
ISDELETED,
ISIGNORED,
IMPORTEDACCOUNTCODE,
IMPORTEDUNITCODE,
BEGINNINGBALANCE,
ENDINGBALANCE,
CREATEDBY,
FILEID
)
select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY
HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
to_timestamp(To_char(clock_timestamp() at time zone
'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
false,
false,
IMPORTEDACCOUNTCODE,
IMPORTEDUNITCODE,
BEGINNINGBALANCE,
ENDINGBALANCE,
parCreatedBy,
FILEID
from STAGING_table_a
where FILEID = parFileId;
END;
$function$
;
Count of tables:
=================
select count(*) from schema1.table_a;
count
-------
67019
select count(*) from schema1.table_b;
count
-------
20
create trigger table_a_trigger before
insert
on
schema1.table_a for each row execute function
schema1."table_a_trigger$table_a"();
CREATE OR REPLACE FUNCTION schema1."table_a_trigger$table_a"()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF COALESCE(new.id, - 1) = - 1 THEN
SELECT
nextval('table_a_sq')
INTO STRICT new.id;
END IF;
RETURN NEW;
END;
$function$;
ALTER TABLE schema1.table_a ADD CONSTRAINT fk_con_tablea FOREIGN KEY
(fileid) REFERENCES schema1.table_b(id);
POSTGRES
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Insert on table_a (cost=0.00..431.80 rows=0 width=0) (actual
time=35.806..35.807 rows=0 loops=1)
Buffers: shared hit=9266 written=19
-> Subquery Scan on "*SELECT*" (cost=0.00..431.80 rows=499 width=771)
(actual time=0.427..5.654 rows=499 loops=1)
Buffers: shared hit=162
-> Seq Scan on staging_table_a (cost=0.00..414.33 rows=499
width=83) (actual time=0.416..4.286 rows=499 loops=1)
Filter: (fileid = 37)
Rows Removed by Filter: 18989
Buffers: shared hit=162
Settings: effective_cache_size = '266500832kB', maintenance_io_concurrency
= '1', max_parallel_workers = '24', search_path = 'schema1, public'
Planning Time: 0.092 ms
Trigger for constraint fk_con_tablea: time=6.304 calls=499
Trigger table_a_trigger: time=5.658 calls=499
Execution Time: 42.206 ms
(13 rows)
schema1_U QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Insert on table_a (cost=0.00..431.80 rows=0 width=0) (actual
time=34.806..34.806 rows=0 loops=1)
Buffers: shared hit=9247 written=9
-> Subquery Scan on "*SELECT*" (cost=0.00..431.80 rows=499 width=771)
(actual time=0.427..5.372 rows=499 loops=1)
Buffers: shared hit=162
-> Seq Scan on staging_table_a (cost=0.00..414.33 rows=499
width=83) (actual time=0.416..4.159 rows=499 loops=1)
Filter: (fileid = 37)
Rows Removed by Filter: 18989
Buffers: shared hit=162
Settings: effective_cache_size = '266500832kB', maintenance_io_concurrency
= '1', max_parallel_workers = '24', search_path = 'schema1, public'
Planning Time: 0.092 ms
Trigger for constraint fk_con_tablea: time=426499.314 calls=499
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Issue
Trigger table_a_trigger: time=5.712 calls=499
Execution Time: 426534.633 ms
(13 rows)
Regards,
Satalabha
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
@ 2023-06-04 08:40 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Satalabaha Postgres @ 2023-06-04 08:40 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Forgot to mention. If I cancel the long running insert statement from
schema1_u user query before completion, I see the below. Not sure if this
will help but just thought to mention it.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "schema1"."table_b" x WHERE
"id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
Regards,
Satalabha
On Sun, 4 Jun 2023 at 14:04, Satalabaha Postgres <
satalabaha.postgres@gmail.com> wrote:
> Hi Listers,
>
> 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?
>
> Stored Procedure:
> ====================
>
> CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double
> precision, parcreatedby text)
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> BEGIN
> insert into table_a
> (
> ROWVERSION,
> CREATED,
> ISDELETED,
> ISIGNORED,
> IMPORTEDACCOUNTCODE,
> IMPORTEDUNITCODE,
> BEGINNINGBALANCE,
> ENDINGBALANCE,
> CREATEDBY,
> FILEID
> )
> select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY
> HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
> to_timestamp(To_char(clock_timestamp() at time zone
> 'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
> false,
> false,
> IMPORTEDACCOUNTCODE,
> IMPORTEDUNITCODE,
> BEGINNINGBALANCE,
> ENDINGBALANCE,
> parCreatedBy,
> FILEID
> from STAGING_table_a
> where FILEID = parFileId;
>
> END;
> $function$
> ;
>
>
>
> Count of tables:
> =================
>
> select count(*) from schema1.table_a;
> count
> -------
> 67019
>
> select count(*) from schema1.table_b;
> count
> -------
> 20
>
>
>
> create trigger table_a_trigger before
> insert
> on
> schema1.table_a for each row execute function
> schema1."table_a_trigger$table_a"();
>
>
>
> CREATE OR REPLACE FUNCTION schema1."table_a_trigger$table_a"()
> RETURNS trigger
> LANGUAGE plpgsql
> AS $function$
> BEGIN
> IF COALESCE(new.id, - 1) = - 1 THEN
> SELECT
> nextval('table_a_sq')
> INTO STRICT new.id;
> END IF;
> RETURN NEW;
> END;
> $function$;
>
> ALTER TABLE schema1.table_a ADD CONSTRAINT fk_con_tablea FOREIGN KEY
> (fileid) REFERENCES schema1.table_b(id);
>
>
>
> POSTGRES QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------------------------------------------
> Insert on table_a (cost=0.00..431.80 rows=0 width=0) (actual
> time=35.806..35.807 rows=0 loops=1)
> Buffers: shared hit=9266 written=19
> -> Subquery Scan on "*SELECT*" (cost=0.00..431.80 rows=499 width=771)
> (actual time=0.427..5.654 rows=499 loops=1)
> Buffers: shared hit=162
> -> Seq Scan on staging_table_a (cost=0.00..414.33 rows=499
> width=83) (actual time=0.416..4.286 rows=499 loops=1)
> Filter: (fileid = 37)
> Rows Removed by Filter: 18989
> Buffers: shared hit=162
> Settings: effective_cache_size = '266500832kB',
> maintenance_io_concurrency = '1', max_parallel_workers = '24', search_path
> = 'schema1, public'
> Planning Time: 0.092 ms
> Trigger for constraint fk_con_tablea: time=6.304 calls=499
> Trigger table_a_trigger: time=5.658 calls=499
> Execution Time: 42.206 ms
> (13 rows)
>
>
>
> schema1_U QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------------------------------------------------
> Insert on table_a (cost=0.00..431.80 rows=0 width=0) (actual
> time=34.806..34.806 rows=0 loops=1)
> Buffers: shared hit=9247 written=9
> -> Subquery Scan on "*SELECT*" (cost=0.00..431.80 rows=499 width=771)
> (actual time=0.427..5.372 rows=499 loops=1)
> Buffers: shared hit=162
> -> Seq Scan on staging_table_a (cost=0.00..414.33 rows=499
> width=83) (actual time=0.416..4.159 rows=499 loops=1)
> Filter: (fileid = 37)
> Rows Removed by Filter: 18989
> Buffers: shared hit=162
> Settings: effective_cache_size = '266500832kB',
> maintenance_io_concurrency = '1', max_parallel_workers = '24', search_path
> = 'schema1, public'
> Planning Time: 0.092 ms
> Trigger for constraint fk_con_tablea: time=426499.314 calls=499
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Issue
> Trigger table_a_trigger: time=5.712 calls=499
> Execution Time: 426534.633 ms
> (13 rows)
>
> Regards,
>
> Satalabha
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
@ 2023-06-04 11:21 ` Julien Rouhaud <rjuju123@gmail.com>
2023-06-04 11:42 ` Re: Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: Julien Rouhaud @ 2023-06-04 11:21 UTC (permalink / raw)
To: Satalabaha Postgres <satalabaha.postgres@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 11:21 ` Re: Weird behavior of INSERT QUERY Julien Rouhaud <rjuju123@gmail.com>
@ 2023-06-04 11:42 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 13:21 ` Re: Weird behavior of INSERT QUERY Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Satalabaha Postgres @ 2023-06-04 11:42 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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@gmail.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.
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 11:21 ` Re: Weird behavior of INSERT QUERY Julien Rouhaud <rjuju123@gmail.com>
2023-06-04 11:42 ` Re: Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
@ 2023-06-04 13:21 ` Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Julien Rouhaud @ 2023-06-04 13:21 UTC (permalink / raw)
To: Satalabaha Postgres <satalabaha.postgres@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Please don't top post on this mailing list:
https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics
On Sun, Jun 4, 2023 at 7:42 PM Satalabaha Postgres
<satalabaha.postgres@gmail.com> wrote:
>
> Yes both in both the cases the same tables are accessed. Yes we tried indexing as well, but we have the same behaviour.
Do you reproduce the problem if you connect and execute that query
manually with the schema1_u user? Is the query always fast when
running as postgres and always slow when running as schema1_u? Is
there any special configuration for that user? \drds can tell you
that, but those should get logged with the rest of the non default
parameters already displayed in the explain plan.
If you can reproduce easily, you should be able to get the execution
plans of the underlying FK queries using auto_explain and
auto_explain.log_nested_statements = true. It might show you the
problem.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
@ 2023-06-04 13:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-04 14:58 ` Re: Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2023-06-04 13:26 UTC (permalink / raw)
To: Satalabaha Postgres <satalabaha.postgres@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 13:26 ` Re: Weird behavior of INSERT QUERY Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-06-04 14:58 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Satalabaha Postgres @ 2023-06-04 14:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
Regards,
Satalabha
On Sun, 4 Jun 2023 at 18:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
Regards, Satalabaha.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
@ 2023-06-04 14:16 ` Ranier Vilela <ranier.vf@gmail.com>
2023-06-04 14:49 ` Re: Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: Ranier Vilela @ 2023-06-04 14:16 UTC (permalink / raw)
To: Satalabaha Postgres <satalabaha.postgres@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Em dom., 4 de jun. de 2023 às 05:35, Satalabaha Postgres <
satalabaha.postgres@gmail.com> escreveu:
> Hi Listers,
>
> 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?
>
> Stored Procedure:
> ====================
>
> CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double
> precision, parcreatedby text)
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> BEGIN
> insert into table_a
> (
> ROWVERSION,
> CREATED,
> ISDELETED,
> ISIGNORED,
> IMPORTEDACCOUNTCODE,
> IMPORTEDUNITCODE,
> BEGINNINGBALANCE,
> ENDINGBALANCE,
> CREATEDBY,
> FILEID
> )
> select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY
> HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
> to_timestamp(To_char(clock_timestamp() at time zone
> 'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
> false,
> false,
> IMPORTEDACCOUNTCODE,
> IMPORTEDUNITCODE,
> BEGINNINGBALANCE,
> ENDINGBALANCE,
> parCreatedBy,
> FILEID
> from STAGING_table_a
> where FILEID = parFileId;
>
> END;
> $function$
> ;
>
Can you show what type is FILEID?
Can there be type mismatch?
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 14:16 ` Re: Weird behavior of INSERT QUERY Ranier Vilela <ranier.vf@gmail.com>
@ 2023-06-04 14:49 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 23:05 ` Re: Weird behavior of INSERT QUERY Ranier Vilela <ranier.vf@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Satalabaha Postgres @ 2023-06-04 14:49 UTC (permalink / raw)
To: Ranier Vilela <ranier.vf@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sun, 4 Jun 2023 at 19:46, Ranier Vilela <ranier.vf@gmail.com> wrote:
> Em dom., 4 de jun. de 2023 às 05:35, Satalabaha Postgres <
> satalabaha.postgres@gmail.com> escreveu:
>
>> Hi Listers,
>>
>> 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?
>>
>> Stored Procedure:
>> ====================
>>
>> CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double
>> precision, parcreatedby text)
>> RETURNS void
>> LANGUAGE plpgsql
>> AS $function$
>> BEGIN
>> insert into table_a
>> (
>> ROWVERSION,
>> CREATED,
>> ISDELETED,
>> ISIGNORED,
>> IMPORTEDACCOUNTCODE,
>> IMPORTEDUNITCODE,
>> BEGINNINGBALANCE,
>> ENDINGBALANCE,
>> CREATEDBY,
>> FILEID
>> )
>> select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY
>> HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
>> to_timestamp(To_char(clock_timestamp() at time zone
>> 'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
>> false,
>> false,
>> IMPORTEDACCOUNTCODE,
>> IMPORTEDUNITCODE,
>> BEGINNINGBALANCE,
>> ENDINGBALANCE,
>> parCreatedBy,
>> FILEID
>> from STAGING_table_a
>> where FILEID = parFileId;
>>
>> END;
>> $function$
>> ;
>>
> Can you show what type is FILEID?
>
> Can there be type mismatch?
>
>
regards,
> Ranier Vilela
>
Thanks Ranier. Please find the below.
\d+ schema1.table_a
Table "schema1.table_a"
Column | Type | Collation |
Nullable | Default | Storage | Stats target | Description
---------------------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
id | numeric(20,0) | | not
null | | main | |
rowversion | timestamp(4) without time zone | | not
null | | plain | |
created | timestamp(4) without time zone | | not
null | | plain | |
isdeleted | boolean | | not
null | | plain | |
lastupdated | timestamp(4) without time zone | |
| | plain | |
isignored | boolean | | not
null | | plain | |
importedaccountcode | character varying(255) | |
| | extended | |
importedunitcode | character varying(255) | |
| | extended | |
beginningbalance | numeric(19,5) | |
| | main | |
endingbalance | numeric(19,5) | |
| | main | |
createdbyid | numeric(20,0) | |
| | main | |
updatedbyid | numeric(20,0) | |
| | main | |
fileid | numeric(20,0) | | not
null | | main | |
previousid | numeric(20,0) | |
| | main | |
createdby | character varying(255) | |
| | extended | |
lastupdatedby | character varying(255) | |
| | extended | |
\d+ schema1.table_b
Table "schema1.table_b"
Column | Type | Collation |
Nullable | Default | Storage | Stats target | Description
--------------------------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
id | numeric(20,0) | |
not null | | main | |
rowversion | timestamp(4) without time zone | |
not null | | plain | |
created | timestamp(4) without time zone | |
not null | | plain | |
isdeleted | boolean | |
not null | | plain | |
lastupdated | timestamp(4) without time zone | |
| | plain | |
version | numeric(10,0) | |
not null | | main | |
isactive | boolean | |
not null | | plain | |
name | character varying(255) | |
not null | | extended | |
displayname | character varying(255) | |
not null | | extended | |
ispublished | boolean | |
not null | | plain | |
isretired | boolean | |
not null | | plain | |
publishdatetime | timestamp(4) without time zone | |
| | plain | |
createdbyid | numeric(20,0) | |
| | main | |
updatedbyid | numeric(20,0) | |
| | main | |
periodid | numeric(20,0) | |
not null | | main | |
uploadchartyearversionid | numeric(20,0) | |
not null | | main | |
importchartyearversionid | numeric(20,0) | |
| | main | |
initialtbadjversionid | numeric(20,0) | |
| | main | |
latesttbadjversionid | numeric(20,0) | |
| | main | |
trialbalancesourceid | numeric(20,0) | |
not null | | main | |
filedefinitionid | numeric(20,0) | |
not null | | main | |
createdby | character varying(255) | |
| | extended | |
lastupdatedby | character varying(255) | |
| | extended | |
Regards, Satalabaha
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 14:16 ` Re: Weird behavior of INSERT QUERY Ranier Vilela <ranier.vf@gmail.com>
2023-06-04 14:49 ` Re: Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
@ 2023-06-04 23:05 ` Ranier Vilela <ranier.vf@gmail.com>
2023-06-05 11:55 ` Re: Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Ranier Vilela @ 2023-06-04 23:05 UTC (permalink / raw)
To: Satalabaha Postgres <satalabaha.postgres@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Em dom., 4 de jun. de 2023 às 11:49, Satalabaha Postgres <
satalabaha.postgres@gmail.com> escreveu:
>
>
>
> On Sun, 4 Jun 2023 at 19:46, Ranier Vilela <ranier.vf@gmail.com> wrote:
>
>> Em dom., 4 de jun. de 2023 às 05:35, Satalabaha Postgres <
>> satalabaha.postgres@gmail.com> escreveu:
>>
>>> Hi Listers,
>>>
>>> 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?
>>>
>>> Stored Procedure:
>>> ====================
>>>
>>> CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double
>>> precision, parcreatedby text)
>>> RETURNS void
>>> LANGUAGE plpgsql
>>> AS $function$
>>> BEGIN
>>> insert into table_a
>>> (
>>> ROWVERSION,
>>> CREATED,
>>> ISDELETED,
>>> ISIGNORED,
>>> IMPORTEDACCOUNTCODE,
>>> IMPORTEDUNITCODE,
>>> BEGINNINGBALANCE,
>>> ENDINGBALANCE,
>>> CREATEDBY,
>>> FILEID
>>> )
>>> select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY
>>> HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
>>> to_timestamp(To_char(clock_timestamp() at time zone
>>> 'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
>>> false,
>>> false,
>>> IMPORTEDACCOUNTCODE,
>>> IMPORTEDUNITCODE,
>>> BEGINNINGBALANCE,
>>> ENDINGBALANCE,
>>> parCreatedBy,
>>> FILEID
>>> from STAGING_table_a
>>> where FILEID = parFileId;
>>>
>>> END;
>>> $function$
>>> ;
>>>
>> Can you show what type is FILEID?
>>
>> Can there be type mismatch?
>>
>>
> regards,
>> Ranier Vilela
>>
>
> Thanks Ranier. Please find the below.
>
> \d+ schema1.table_a
> Table "schema1.table_a"
> Column | Type | Collation |
> Nullable | Default | Storage | Stats target | Description
>
> ---------------------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
> id | numeric(20,0) | | not
> null | | main | |
> rowversion | timestamp(4) without time zone | | not
> null | | plain | |
> created | timestamp(4) without time zone | | not
> null | | plain | |
> isdeleted | boolean | | not
> null | | plain | |
> lastupdated | timestamp(4) without time zone | |
> | | plain | |
> isignored | boolean | | not
> null | | plain | |
> importedaccountcode | character varying(255) | |
> | | extended | |
> importedunitcode | character varying(255) | |
> | | extended | |
> beginningbalance | numeric(19,5) | |
> | | main | |
> endingbalance | numeric(19,5) | |
> | | main | |
> createdbyid | numeric(20,0) | |
> | | main | |
> updatedbyid | numeric(20,0) | |
> | | main | |
> fileid | numeric(20,0) | | not
> null | | main | |
> previousid | numeric(20,0) | |
> | | main | |
> createdby | character varying(255) | |
> | | extended | |
> lastupdatedby | character varying(255) | |
> | | extended | |
>
> \d+ schema1.table_b
> Table "schema1.table_b"
> Column | Type | Collation |
> Nullable | Default | Storage | Stats target | Description
>
> --------------------------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
> id | numeric(20,0) | |
> not null | | main | |
> rowversion | timestamp(4) without time zone | |
> not null | | plain | |
> created | timestamp(4) without time zone | |
> not null | | plain | |
> isdeleted | boolean | |
> not null | | plain | |
> lastupdated | timestamp(4) without time zone | |
> | | plain | |
> version | numeric(10,0) | |
> not null | | main | |
> isactive | boolean | |
> not null | | plain | |
> name | character varying(255) | |
> not null | | extended | |
> displayname | character varying(255) | |
> not null | | extended | |
> ispublished | boolean | |
> not null | | plain | |
> isretired | boolean | |
> not null | | plain | |
> publishdatetime | timestamp(4) without time zone | |
> | | plain | |
> createdbyid | numeric(20,0) | |
> | | main | |
> updatedbyid | numeric(20,0) | |
> | | main | |
> periodid | numeric(20,0) | |
> not null | | main | |
> uploadchartyearversionid | numeric(20,0) | |
> not null | | main | |
> importchartyearversionid | numeric(20,0) | |
> | | main | |
> initialtbadjversionid | numeric(20,0) | |
> | | main | |
> latesttbadjversionid | numeric(20,0) | |
> | | main | |
> trialbalancesourceid | numeric(20,0) | |
> not null | | main | |
> filedefinitionid | numeric(20,0) | |
> not null | | main | |
> createdby | character varying(255) | |
> | | extended | |
> lastupdatedby | character varying(255) | |
> | | extended | |
>
I think you are in trouble when comparing float8 (double precision) with
numeric.
This small example shows problems.
Postgres version 14.2:
SELECT '8217316934885843456'::float8 =
'8217316934885843456'::float8::bigint::float8,
'8217316934885843456'::float8 =
'8217316934885843456'::float8::numeric::float8;
?column? | ?column?
----------+----------
t | f
(1 row)
I suggest a study to switch to bigint.
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Weird behavior of INSERT QUERY
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 14:16 ` Re: Weird behavior of INSERT QUERY Ranier Vilela <ranier.vf@gmail.com>
2023-06-04 14:49 ` Re: Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 23:05 ` Re: Weird behavior of INSERT QUERY Ranier Vilela <ranier.vf@gmail.com>
@ 2023-06-05 11:55 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Satalabaha Postgres @ 2023-06-05 11:55 UTC (permalink / raw)
To: Ranier Vilela <ranier.vf@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, 5 Jun 2023 at 04:35, Ranier Vilela <ranier.vf@gmail.com> wrote:
> Em dom., 4 de jun. de 2023 às 11:49, Satalabaha Postgres <
> satalabaha.postgres@gmail.com> escreveu:
>
>>
>>
>>
>> On Sun, 4 Jun 2023 at 19:46, Ranier Vilela <ranier.vf@gmail.com> wrote:
>>
>>> Em dom., 4 de jun. de 2023 às 05:35, Satalabaha Postgres <
>>> satalabaha.postgres@gmail.com> escreveu:
>>>
>>>> Hi Listers,
>>>>
>>>> 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?
>>>>
>>>> Stored Procedure:
>>>> ====================
>>>>
>>>> CREATE OR REPLACE FUNCTION schema1.ins_staging_fn(parfileid double
>>>> precision, parcreatedby text)
>>>> RETURNS void
>>>> LANGUAGE plpgsql
>>>> AS $function$
>>>> BEGIN
>>>> insert into table_a
>>>> (
>>>> ROWVERSION,
>>>> CREATED,
>>>> ISDELETED,
>>>> ISIGNORED,
>>>> IMPORTEDACCOUNTCODE,
>>>> IMPORTEDUNITCODE,
>>>> BEGINNINGBALANCE,
>>>> ENDINGBALANCE,
>>>> CREATEDBY,
>>>> FILEID
>>>> )
>>>> select to_timestamp(To_char(clock_timestamp(),'DD-MON-YY
>>>> HH.MI.SS.FF4 AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
>>>> to_timestamp(To_char(clock_timestamp() at time zone
>>>> 'utc', 'DD-MON-YY HH.MI.SS.MS AM'),'DD-MON-YY HH.MI.SS.FF4 AM'),
>>>> false,
>>>> false,
>>>> IMPORTEDACCOUNTCODE,
>>>> IMPORTEDUNITCODE,
>>>> BEGINNINGBALANCE,
>>>> ENDINGBALANCE,
>>>> parCreatedBy,
>>>> FILEID
>>>> from STAGING_table_a
>>>> where FILEID = parFileId;
>>>>
>>>> END;
>>>> $function$
>>>> ;
>>>>
>>> Can you show what type is FILEID?
>>>
>>> Can there be type mismatch?
>>>
>>>
>> regards,
>>> Ranier Vilela
>>>
>>
>> Thanks Ranier. Please find the below.
>>
>> \d+ schema1.table_a
>> Table "schema1.table_a"
>> Column | Type | Collation |
>> Nullable | Default | Storage | Stats target | Description
>>
>> ---------------------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
>> id | numeric(20,0) | | not
>> null | | main | |
>> rowversion | timestamp(4) without time zone | | not
>> null | | plain | |
>> created | timestamp(4) without time zone | | not
>> null | | plain | |
>> isdeleted | boolean | | not
>> null | | plain | |
>> lastupdated | timestamp(4) without time zone | |
>> | | plain | |
>> isignored | boolean | | not
>> null | | plain | |
>> importedaccountcode | character varying(255) | |
>> | | extended | |
>> importedunitcode | character varying(255) | |
>> | | extended | |
>> beginningbalance | numeric(19,5) | |
>> | | main | |
>> endingbalance | numeric(19,5) | |
>> | | main | |
>> createdbyid | numeric(20,0) | |
>> | | main | |
>> updatedbyid | numeric(20,0) | |
>> | | main | |
>> fileid | numeric(20,0) | | not
>> null | | main | |
>> previousid | numeric(20,0) | |
>> | | main | |
>> createdby | character varying(255) | |
>> | | extended | |
>> lastupdatedby | character varying(255) | |
>> | | extended | |
>>
>> \d+ schema1.table_b
>> Table "schema1.table_b"
>> Column | Type | Collation |
>> Nullable | Default | Storage | Stats target | Description
>>
>> --------------------------+--------------------------------+-----------+----------+---------+----------+--------------+-------------
>> id | numeric(20,0) | |
>> not null | | main | |
>> rowversion | timestamp(4) without time zone | |
>> not null | | plain | |
>> created | timestamp(4) without time zone | |
>> not null | | plain | |
>> isdeleted | boolean | |
>> not null | | plain | |
>> lastupdated | timestamp(4) without time zone | |
>> | | plain | |
>> version | numeric(10,0) | |
>> not null | | main | |
>> isactive | boolean | |
>> not null | | plain | |
>> name | character varying(255) | |
>> not null | | extended | |
>> displayname | character varying(255) | |
>> not null | | extended | |
>> ispublished | boolean | |
>> not null | | plain | |
>> isretired | boolean | |
>> not null | | plain | |
>> publishdatetime | timestamp(4) without time zone | |
>> | | plain | |
>> createdbyid | numeric(20,0) | |
>> | | main | |
>> updatedbyid | numeric(20,0) | |
>> | | main | |
>> periodid | numeric(20,0) | |
>> not null | | main | |
>> uploadchartyearversionid | numeric(20,0) | |
>> not null | | main | |
>> importchartyearversionid | numeric(20,0) | |
>> | | main | |
>> initialtbadjversionid | numeric(20,0) | |
>> | | main | |
>> latesttbadjversionid | numeric(20,0) | |
>> | | main | |
>> trialbalancesourceid | numeric(20,0) | |
>> not null | | main | |
>> filedefinitionid | numeric(20,0) | |
>> not null | | main | |
>> createdby | character varying(255) | |
>> | | extended | |
>> lastupdatedby | character varying(255) | |
>> | | extended | |
>>
> I think you are in trouble when comparing float8 (double precision) with
> numeric.
> This small example shows problems.
>
> Postgres version 14.2:
> SELECT '8217316934885843456'::float8 =
> '8217316934885843456'::float8::bigint::float8,
> '8217316934885843456'::float8 =
> '8217316934885843456'::float8::numeric::float8;
> ?column? | ?column?
> ----------+----------
> t | f
> (1 row)
>
> I suggest a study to switch to bigint.
>
>
> regards,
> Ranier Vilela
>
Hi Ranier / All,
Any idea if that is the case why as postgres user the query just works
fine? Also I enabled all parameters for auto_explain and couldn't find any
SQL that is taking more time. At last it just showed the insert statement
and its execution plan which I have mentioned in the beginning of the email
stating "trigger for constraints " taking more time.
One thing that was observed is that, when as postgres user I ran the query,
it was not taking rowshare locks on the parent table (table_b) whereas as
when I ran the same SQL as schema1_u user, I saw the Row Share locks
acquired on the parent table and its FK's and indexes etc. Not sure if I am
missing something here.
As postgres user:
clock_timestamp | relname | locktype |
database | relation | page | tuple | virtualtransaction | pid |
mode | granted
-------------------------------+----------------------+----------+----------+----------+------+-------+--------------------+-------+------------------+---------
2023-06-05 08:57:38.596859+00 | table_a_sq | relation | 16400 |
12826203 | | | 11/14697 | 17833 | RowExclusiveLock | t
2023-06-05 08:57:38.596877+00 | idx1_table_a | relation | 16400
| 28894204 | | | 11/14697 | 17833 | RowExclusiveLock |
t
2023-06-05 08:57:38.596884+00 | idx2_table_a | relation | 16400 |
28894201 | | | 11/14697 | 17833 | RowExclusiveLock | t
2023-06-05 08:57:38.59689+00 | idx3_table_a | relation | 16400
| 28894199 | | | 11/14697 | 17833 | RowExclusiveLock |
t
2023-06-05 08:57:38.596896+00 | idx4_table_a | relation | 16400 |
28894197 | | | 11/14697 | 17833 | RowExclusiveLock | t
2023-06-05 08:57:38.596902+00 | idx5_table_a | relation | 16400
| 28894195 | | | 11/14697 | 17833 | RowExclusiveLock |
t
2023-06-05 08:57:38.596909+00 | fk1_table_a | relation | 16400 |
28894193 | | | 11/14697 | 17833 | RowExclusiveLock | t
2023-06-05 08:57:38.596915+00 | fk2_table_a | relation | 16400 |
28894191 | | | 11/14697 | 17833 | RowExclusiveLock | t
2023-06-05 08:57:38.596923+00 | table_a_pkey | relation | 16400 |
12826690 | | | 11/14697 | 17833 | RowExclusiveLock | t
2023-06-05 08:57:38.596932+00 | staging_table_a | relation | 16400 |
12826482 | | | 11/14697 | 17833 | AccessShareLock | t
2023-06-05 08:57:38.596939+00 | table_a | relation | 16400 |
12826497 | | | 11/14697 | 17833 | RowExclusiveLock | t
(11 rows)
As schema1_u user:
===========================
clock_timestamp | relname | locktype |
database | relation | page | tuple | virtualtransaction | pid |
mode | granted
-------------------------------+------------------------+----------+----------+----------+------+-------+--------------------+-------+------------------+---------
2023-06-05 09:16:24.097184+00 | fk1_table_b | relation | 16400 |
28894114 | | | 13/18586 | 21032 | RowShareLock | t
2023-06-05 09:16:24.097203+00 | fk2_table_b | relation | 16400 |
28894112 | | | 13/18586 | 21032 | RowShareLock | t
2023-06-05 09:16:24.09721+00 | fk3_table_b | relation | 16400 |
28894110 | | | 13/18586 | 21032 | RowShareLock | t
2023-06-05 09:16:24.097221+00 | table_b_pkey | relation | 16400 |
12826648 | | | 13/18586 | 21032 | RowShareLock | t
2023-06-05 09:16:24.097229+00 | table_b | relation | 16400 |
12826410 | | | 13/18586 | 21032 | RowShareLock | t
2023-06-05 09:16:24.097238+00 | table_a_sq | relation | 16400 |
12826203 | | | 13/18586 | 21032 | RowExclusiveLock | t
2023-06-05 09:16:24.097246+00 | idx1_table_a | relation |
16400 | 28894204 | | | 13/18586 | 21032 |
RowExclusiveLock | t
2023-06-05 09:16:24.097252+00 | idx2_table_a | relation | 16400
| 28894201 | | | 13/18586 | 21032 | RowExclusiveLock |
t
2023-06-05 09:16:24.097258+00 | idx3_table_a | relation |
16400 | 28894199 | | | 13/18586 | 21032 |
RowExclusiveLock | t
2023-06-05 09:16:24.097264+00 | idx4_table_a | relation | 16400
| 28894197 | | | 13/18586 | 21032 | RowExclusiveLock |
t
2023-06-05 09:16:24.097271+00 | idx5_table_a | relation |
16400 | 28894195 | | | 13/18586 | 21032 |
RowExclusiveLock | t
2023-06-05 09:16:24.097277+00 | fk1_table_a | relation | 16400
| 28894193 | | | 13/18586 | 21032 | RowExclusiveLock |
t
2023-06-05 09:16:24.097283+00 | fk2_table_a | relation | 16400
| 28894191 | | | 13/18586 | 21032 | RowExclusiveLock |
t
2023-06-05 09:16:24.09729+00 | table_a_pkey | relation | 16400 |
12826690 | | | 13/18586 | 21032 | RowExclusiveLock | t
2023-06-05 09:16:24.097298+00 | staging_table_a | relation | 16400 |
12826482 | | | 13/18586 | 21032 | AccessShareLock | t
2023-06-05 09:16:24.097305+00 | table_a | relation | 16400 |
12826497 | | | 13/18586 | 21032 | RowExclusiveLock | t
2023-06-05 09:16:24.097318+00 | fk4_table_b | relation | 16400 |
28894116 | | | 13/18586 | 21032 | RowShareLock | t
2023-06-05 09:16:24.097324+00 | fk5_table_b | relation | 16400 |
28894120 | | | 13/18586 | 21032 | RowShareLock | t
2023-06-05 09:16:24.09733+00 | fk6_table_b | relation | 16400 |
28894122 | | | 13/18586 | 21032 | RowShareLock | t
2023-06-05 09:16:24.097336+00 | fk7_table_b | relation | 16400
| 28894118 | | | 13/18586 | 21032 | RowShareLock |
t
2023-06-05 09:16:24.097344+00 | fk8_table_b | relation | 16400
| 29343754 | | | 13/18586 | 21032 | RowShareLock |
t
(21 rows)
Regards, Satalabaha
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2023-06-05 11:55 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-04 08:34 Weird behavior of INSERT QUERY Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 08:40 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 11:21 ` Julien Rouhaud <rjuju123@gmail.com>
2023-06-04 11:42 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 13:21 ` Julien Rouhaud <rjuju123@gmail.com>
2023-06-04 13:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-06-04 14:58 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 14:16 ` Ranier Vilela <ranier.vf@gmail.com>
2023-06-04 14:49 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
2023-06-04 23:05 ` Ranier Vilela <ranier.vf@gmail.com>
2023-06-05 11:55 ` Satalabaha Postgres <satalabaha.postgres@gmail.com>
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