Tuning UPSERT in PG14 w/ 130M records table

I'm tuning the server in order to speed up several SQL queries generated by a product and that can't be modified or tuned themselves. I'm stuck on a query that creates a CTO an then an INSERT / SELECT statement w/ a ON CONFLICT clause, this is the execution plan : Insert on data_location.md_associe_interlo_coord_red t (cost=0.57..592368.31 rows=0 width=0) (actual rows=0 loops=1) Conflict Resolution: UPDATE Conflict Arbiter Indexes: pkmd_associe_interlo_coord_red Conflict Filter: (t.b_batchid Index Scan using i2mi_associe_interlo_coord_red on data_location.mi_associe_interlo_coord_red s (cost=0.57..592368.31 rows=1343311 width=1187) (actual rows=1341390 loops=1) Output: s.b_pubid, s.b_sourceid, s.b_classname, s.b_batchid, (COALESCE(s.b_credate, '2025-08-01 08:43:28.853'::timestamp without time zone))::timestamp(6) without time zone, (COALESCE(s.b_upddate, '2025-08-01 08:43:28.853'::timestamp without time zone))::timestamp(6) without time zone, COALESCE(s.b_creator, 'semadmin'::character varying), COALESCE(s.b_updator, 'semadmin'::character varying), s.b_matchgrp, s.b_xgrp, s.b_confirmationstatus, NULL::numeric, NULL::character varying, s.b_hassuggmerge, s.b_suggmergeid, s.b_suggmergeconfscore, NULL::character varying, s.b_suggmergemasterscount, s.associe_interlo_coord_red, NULL::numeric, s.b_confirmedsdpk, s.num_compte_externe, s.siret, s.siren, s.nir, NULL::date, s.type_interlocuteur, NULL::integer, s.f_redevabilite, s.fp_redevabilite, s.fs_redevabilite, s.f_interlocuteur, s.fp_interlocuteur, s.fs_interlocuteur, s.f_coordonnee, s.fp_coordonnee, s.fs_coordonnee, s.id_golden_redevabilite, NULL::integer, s.dt_maj, s.code_organisme_source Index Cond: (s.b_batchid = '52615'::numeric) Filter: ((s.b_classname)::text = 'AssocieInterloCoordRede'::text) Buffers: shared hit=9 read=239313 written=57 Settings: effective_cache_size = '43GB', effective_io_concurrency = '50', max_parallel_workers = '4', random_page_cost = '2', search_path = '"$user", public, extensions', work_mem = '1GB' Table mi_associe_interlo_coord_red has +131M records (+140GB on disk) and md_associe_interlo_coord_red +129M records has (+81GB on disk). Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression | Cible de statistiques | Description ---------------------------+--------------------------------+-----------------+-----------+------------+----------+-------------+-----------------------+------------- b_pubid | character varying(30) | | not null | | extended | | | b_sourceid | character varying(128) | | not null | | extended | | | b_classname | character varying(128) | | not null | | extended | | | b_batchid | numeric(38,0) | | not null | | main | | | b_credate | timestamp(6) without time zone | | | | plain | | | b_upddate | timestamp(6) without time zone | | | | plain | | | b_creator | character varying(128) | | | | extended | | | b_updator | character varying(128) | | | | extended | | | b_pendingactions | character varying(30) | | | | extended | | | b_matchgrp | numeric(38,0) | | | | main | | | b_oldmatchgrp | numeric(38,0) | | | | main | | | b_xgrp | uuid | | | | plain | | | b_confirmationstatus | character varying(30) | | | | extended | | | b_confscore | numeric(38,0) | | | | main | | | b_confscoretype | character varying(30) | | | | extended | | | b_hassuggmerge | boolean | | | | plain | | | b_suggmergeid | numeric(38,0) | | | | main | | | b_suggmergeconfscore | numeric(38,0) | | | | main | | | b_suggmergeconfscoretype | character varying(30) | | | | extended | | | b_suggmergemasterscount | numeric(38,0) | | | | main | | | associe_interlo_coord_red | numeric(38,0) | | | | main | | | b_oldsdpk | numeric(38,0) | | | | main | | | b_confirmedsdpk | numeric(38,0) | | | | main | | | num_compte_externe | character varying(18) | | | | extended | | | siret | character varying(14) | | | | extended | | | siren | character varying(9) | | | | extended | | | nir | character varying(13) | | | | extended | | | dtmaj | date | | | | plain | | | type_interlocuteur | character varying(128) | | | | extended | | | f_redevabilite | numeric(38,0) | | | | main | | | fp_redevabilite | character varying(30) | | | | extended | | | fs_redevabilite | character varying(128) | | | | extended | | | f_interlocuteur | numeric(38,0) | | | | main | | | fp_interlocuteur | character varying(30) | | | | extended | | | fs_interlocuteur | character varying(128) | | | | extended | | | f_coordonnee | numeric(38,0) | | | | main | | | fp_coordonnee | character varying(30) | | | | extended | | | fs_coordonnee | character varying(128) | | | | extended | | | id_golden_redevabilite | character varying(128) | | | | extended | | | dt_maj | date | | | | plain | | | code_organisme_source | character varying(3) | | | | extended | | | Index : "pkmi_associe_interlo_coord_red" PRIMARY KEY, btree (b_pubid, b_sourceid) "fkmi_associe_interlocuteur_re2" btree (fp_interlocuteur, fs_interlocuteur) "fkmi_associe_interlocuteur_re3" btree (fp_coordonnee, fs_coordonnee) "fkmi_associe_interlocuteur_red" btree (fp_redevabilite, fs_redevabilite) "i1mi_associe_interlo_coord_red" btree (associe_interlo_coord_red) "i2mi_associe_interlo_coord_red" btree (b_batchid) "i3mi_associe_interlo_coord_red" btree (b_matchgrp) "i4mi_associe_interlo_coord_red" btree (b_suggmergeid) "i5mi_associe_interlo_coord_red" btree (b_xgrp) "usr_idx_mi_associe_interlocuteur_red_b_pendingactions" btree (b_pendingactions) "usr_mi_associe_interlo_coord_red_b_batchid_idx" btree (b_batchid, b_classname, b_pubid, b_sourceid, fp_coordonnee, fs_coordonnee, fp_interlocuteur, fs_interlocuteur, fp_redevabilite, fs_redevabilite) "usr_mi_associe_interlo_coord_red_fp_fs_type" btree (fp_interlocuteur, fs_interlocuteur, type_interlocuteur) "usr_mi_associe_interlo_coord_red_id_golden_redevabilite_idx" btree (id_golden_redevabilite, type_interlocuteur) Méthode d'accès : heap Options: autovacuum_enabled=true, fillfactor=70 Colonne | Type | Collationnement | NULL-able | Par défaut | Stockage | Compression | Cible de statistiques | Description ---------------------------+--------------------------------+-----------------+-----------+------------+----------+-------------+-----------------------+------------- b_pubid | character varying(30) | | not null | | extended | | | b_sourceid | character varying(128) | | not null | | extended | | | b_classname | character varying(128) | | not null | | extended | | | b_batchid | numeric(38,0) | | not null | | main | | | b_credate | timestamp(6) without time zone | | | | plain | | | b_upddate | timestamp(6) without time zone | | | | plain | | | b_creator | character varying(128) | | | | extended | | | b_updator | character varying(128) | | | | extended | | | b_matchgrp | numeric(38,0) | | | | main | | | b_xgrp | uuid | | | | plain | | | b_confirmationstatus | character varying(30) | | | | extended | | | b_confscore | numeric(38,0) | | | | main | | | b_confscoretype | character varying(30) | | | | extended | | | b_hassuggmerge | boolean | | | | plain | | | b_suggmergeid | numeric(38,0) | | | | main | | | b_suggmergeconfscore | numeric(38,0) | | | | main | | | b_suggmergeconfscoretype | character varying(30) | | | | extended | | | b_suggmergemasterscount | numeric(38,0) | | | | main | | | associe_interlo_coord_red | numeric(38,0) | | not null | | main | | | b_oldsdpk | numeric(38,0) | | | | main | | | b_confirmedsdpk | numeric(38,0) | | | | main | | | num_compte_externe | character varying(18) | | | | extended | | | siret | character varying(14) | | | | extended | | | siren | character varying(9) | | | | extended | | | nir | character varying(13) | | | | extended | | | dtmaj | date | | | | plain | | | type_interlocuteur | character varying(128) | | | | extended | | | f_redevabilite | numeric(38,0) | | | | main | | | fp_redevabilite | character varying(30) | | | | extended | | | fs_redevabilite | character varying(128) | | | | extended | | | f_interlocuteur | numeric(38,0) | | | | main | | | fp_interlocuteur | character varying(30) | | | | extended | | | fs_interlocuteur | character varying(128) | | | | extended | | | f_coordonnee | numeric(38,0) | | | | main | | | fp_coordonnee | character varying(30) | | | | extended | | | fs_coordonnee | character varying(128) | | | | extended | | | id_golden_redevabilite | character varying(128) | | | | extended | | | dt_maj | date | | | | plain | | | code_organisme_source | character varying(3) | | | | extended | | | Index : "pkmd_associe_interlo_coord_red" PRIMARY KEY, btree (b_pubid, b_sourceid) "fkmd_associe_interlocuteur_re2" btree (fp_interlocuteur, fs_interlocuteur) "fkmd_associe_interlocuteur_re3" btree (fp_coordonnee, fs_coordonnee) "fkmd_associe_interlocuteur_red" btree (fp_redevabilite, fs_redevabilite) "i1md_associe_interlo_coord_red" btree (associe_interlo_coord_red) "i2md_associe_interlo_coord_red" btree (b_suggmergeid) "usr_idx_md_associe_interlocuteur_red_b_pendingactions" btree (b_batchid) Méthode d'accès : heap Server has 64GB RAM and 16 vCores, concurrency is low, typcally 3/4 postgres process running. Workload is mostly huge batches, and very little OLTP. Adding memory is not an option because of the company policies Create a covering index is not an option because of the limit of 27 for columns in INCLUDE clause. I tried to recreate the PK by including the b_batchid column (to avoid heap read while handling the conflict I thought) but it didn't help How does it handle the conflict ? why the cost of the upsert is 0?