Running 2 inserts in one transaction is executing unacceptably long time. Running same DML Separately works fine

Question

Decided to see what DBAs have to say about this because developers have no idea. I need to execute 2 DMLs in one transaction. First DML takes records from Table1, changes some values and inserts back into the same table a subset of the originals. Second DML selects the newly inserted records, joins them with the original records that were used to generate the subset, and inserts some values into table2

When I ran this from c# code under one transaction, first statement executed fine and then second kept going forever. So, I decided to check directly in the DB. - same thing.

Using Postgre SQL, if I run the block below, it just keeps running but not completing.. for hours. But the execution shows as active

If I Run these 2 statement separately, in separate transactions - takes few seconds. What do I miss here? I am not very experienced with Postgres.

BTW, if I add SELECT after first statement and just run INSERT and SELECT under transaction, select runs fine and then if there is no commit, records are gone, as expected. It is running that second INSERT in the same transaction that is troublesome. Thanks

begin;
INSERT INTO table1
    (fields...)  
SELECT <some fields and couple hard-coded values>
FROM table1
WHERE 
  <few conditions>
GROUP BY 
  <same fields as SELECT>;

INSERT INTO table2 (f1, f2) -- INSERT SUBSET based on uncommitted data from ^^^
select c.id, a.id
FROM 
   table1 t1 Inner JOIN -- SELF JOIN
   table1 t2 a On <condition> --(uuid, int, int)
where 
    <where conditions>;

COMMIT;

-- Explain plan

Insert on table2  (cost=220982.08..222767.83 rows=0 width=0) (actual time=1497.900..1497.902 rows=0 loops=1)
  ->  Subquery Scan on "*SELECT*"  (cost=220982.08..222767.83 rows=28181 width=40) (actual time=770.711..986.875 rows=94731 loops=1)
        ->  Merge Join  (cost=220982.08..222415.56 rows=28181 width=70) (actual time=770.635..934.304 rows=94731 loops=1)
              Merge Cond: (((a.someid)::text = (c.someid)::text) AND ((a.type)::text = (c.type)::text))
              ->  Sort  (cost=127241.69..127555.13 rows=125375 width=46) (actual time=673.203..737.628 rows=94731 loops=1)
                    Sort Key: a.someid, a.type
                    Sort Method: external merge  Disk: 5208kB
                    ->  Seq Scan on table1 a  (cost=0.00..112768.01 rows=125375 width=46) (actual time=0.006..258.759 rows=94731 loops=1)
                          Filter: ((MustDoSome IS TRUE) AND (batchnumber = '2b86d81c-d76a-4184-b04f-6bdbeb78b51b'::uuid) AND (keytype = 1) AND (datarecordtype = 2))
                          Rows Removed by Filter: 588905
              ->  Sort  (cost=93740.39..93787.02 rows=18650 width=46) (actual time=97.419..112.265 rows=94731 loops=1)
                    Sort Key: c.someid, c.type
                    Sort Method: external sort  Disk: 3448kB
                    ->  Bitmap Heap Scan on statementdata c  (cost=727.30..92417.46 rows=18650 width=46) (actual time=8.072..44.686 rows=58677 loops=1)
                          Recheck Cond: (stmtkeytype = 2)
                          Filter: ((batchnumber = '2b86d81c-d76a-4184-b04f-6bdbeb78b51b'::uuid) AND (datarecordtype = 2))
                          Rows Removed by Filter: 58677
                          Heap Blocks: exact=16204
                          ->  Bitmap Index Scan on fki_fk_table1_stmt_key_type  (cost=0.00..722.63 rows=58428 width=0) (actual time=5.263..5.263 rows=117354 loops=1)
                                Index Cond: (stmtkeytype = 2)
Planning Time: 0.557 ms
Trigger for constraint fk_xxx_zz_yyy_id: time=899.588 calls=94731
Trigger for constraint fk_xxx_yyy_id: time=837.080 calls=94731
Trigger for constraint fk_RRRR_yyyt_id: time=1001.268 calls=94731

Answer 1

There is an optimization, postgresql can skip checking foreign keys in some cases, but only if it's the first command in a transaction (i.e. previous commands in a transaction certainly couldn't affect the associated data). Unfortunately, I did not immediately find a check for the conditions of this optimization in the database code.

Trigger for constraint fk_xxx_zz_yyy_id - this is exactly the call of the foreign key check system trigger.