Re: Recursive query slow on strange conditions

> You can also send a link to the plan on https://explain.depesz.com/
> Which maybe more people will look at than if it requires downloading and
> restoring a DB.
Thanks for the advice.

Here is the plan for PG 11.6 : https://explain.depesz.com/s/Ewt8

And the one for PG 12.2 : https://explain.depesz.com/s/oPAu

Now for the schemas.

CREATE OR REPLACE FUNCTION tisnofcountrygroup(p_idcountrygroup INT)
RETURNS INT[] AS ...

simple function that does a SELECT ARRAY_AGG(INT) on table countrygroups

\d countrygroups (table has 0 row)
Table «
public.countrygroups »
Colonne | Type | Collationnement | NULL-able
| Par défaut
----------------+------------------------+-----------------+-----------+-------------------------------------------------------
idcountrygroup | integer | | not null
| nextval('countrygroups_idcountrygroup_seq'::regclass)
name | character varying(150) | | |
Index :
"countrygroups_pkey" PRIMARY KEY, btree (idcountrygroup)
Référencé par :
TABLE "contrats" CONSTRAINT "contrats_idcountrygroup_fkey" FOREIGN
KEY (idcountrygroup) REFERENCES countrygroups(idcountrygroup)
TABLE "thirdparty" CONSTRAINT "thirdparty_idcountrygroup_fkey"
FOREIGN KEY (idcountrygroup) REFERENCES countrygroups(idcountrygroup)
TABLE "tisningroups" CONSTRAINT "tisningroups_idcountrygroup_fkey"
FOREIGN KEY (idcountrygroup) REFERENCES countrygroups(idcountrygroup) ON
DELETE CASCADE

\d thirdparty (7 rows)
Table « public.thirdparty »
Colonne | Type | Collationnement |
NULL-able | Par défaut
-------------------------+------------------------+-----------------+-----------+---------------------------------------
idthirdparty | integer | |
not null | nextval('providers_id_seq'::regclass)
nom | character varying(50) | |
not null |
idterritoire | integer | |
|
pcttokeep | double precision | |
| 100.0
devise | character varying(3) | |
|
variante | character varying(100) | |
|
canreceivecatalogues | boolean | |
| false
idcountrygroup | integer | |
|
viewsubpublishers | boolean | |
| false
catexpchrono | boolean | |
| false
catexpcwr | boolean | |
| false
catexpcwr_receiver | character varying(5) | |
|
catexpcs | boolean | |
| false
catexptsul | boolean | |
| false
catexpboem | boolean | |
| false
categories | character varying(100) | |
|
catexpignoreterritories | boolean | |
| false
Index :
"providers_pkey" PRIMARY KEY, btree (idthirdparty)

\d territoires (268 rows)
Table « public.territoires »
Colonne | Type | Collationnement |
NULL-able | Par défaut
-----------------------+------------------------+-----------------+-----------+-----------------------------------------
idterritoire | integer | | not
null | nextval('territoires_id_seq'::regclass)
tisn | integer | |
|
nom | character varying(50) | |
|
smallcode | character varying(3) | |
|
longcode | character varying(8) | |
|
nom_en | character varying(100) | |
|
frenchsocialsecurity | boolean | |
| false
frenchvat | boolean | |
| false
frenchbroadcastagessa | boolean | |
| false
withtaxdep | double precision | |
| 0.0
withtaxdrm | double precision | |
| 0.0
stmtinenglish | boolean | |
| true
Index :
"territoires_pkey" PRIMARY KEY, btree (idterritoire)
"ix_tisn" UNIQUE, btree (tisn)

\d copyrightad (280 rows)
Table « public.copyrightad »
Colonne | Type | Collationnement |
NULL-able | Par défaut
--------------------+-----------------------------+-----------------+-----------+-----------------------------------------
idcopyright | integer | |
not null | nextval('copyrightad_id_seq'::regclass)
idoeu | integer | |
not null |
idad | integer | |
|
parent | integer | |
|
idimport | integer | |
|
role | character varying(3) | |
|
qpdepsacem | double precision | |
|
qpdrmsacem | double precision | |
|
qpphonosacem | double precision | |
|
mechowned | double precision | |
|
perfowned | double precision | |
|
syncowned | double precision | |
|
mechcoll | double precision | |
|
perfcoll | double precision | |
|
synccoll | double precision | |
|
idterritoire | integer | |
|
lettrage | character varying(1) | |
|
droitsreserves | boolean | |
|
avanceinitiale | double precision | |
|
ediacompteauteur | boolean | |
|
iscontrolled | boolean | |
| false
idcg | integer | |
|
idthirdparty | integer | |
|
qpspecialsplitrate | double precision | |
|
tisn | integer | |
|
tmpmatchparent | character varying(50) | |
|
creator | text | |
| SESSION_USER
created | timestamp without time zone | |
| now()
iscoedmanager | boolean | |
| false
Index :
"copyrightad_pkey" PRIMARY KEY, btree (idcopyright)
"copyrightad_idad" btree (idad)
"copyrightad_idimport" btree (idimport)
"copyrightad_idoeu" btree (idoeu)
"copyrightad_parent" btree (parent)
"ix_copyright_idad" btree (idad)
"ix_copyright_idoeu" btree (idoeu)

\d contrats (2 rows, none satisfying the condition in the query)
Table « public.contrats »
Colonne | Type | Collationnement | NULL-able
| Par défaut
----------------+------------------------+-----------------+-----------+---------------------------------------------
idcontrat | integer | | not null
| nextval('contrats_idcontrat_seq'::regclass)
idsociete | integer | | |
libelle | character varying(100) | | |
territoire | character varying(255) | | |
notes | text | | |
datedebut | date | | |
datefin | date | | |
codeclegest | character varying(10) | | |
idadgest | integer | | |
codezp | character varying(20) | | |
nivdec | integer | | |
etage | integer | | not null | 1
idtypecontrat | integer | | not null |
idcountrygroup | integer | | |
alsoglobal | boolean | |
| false
Index :
"contrats_pkey" PRIMARY KEY, btree (idcontrat)

\d ctract (0 row)
Table « public.ctract »
Colonne | Type | Collationnement | NULL-able |
Par défaut
------------+------------------+-----------------+-----------+------------------------------------------
idctract | integer | | not null |
nextval('ctract_idctract_seq'::regclass)
idcontrat | integer | | not null |
idad | integer | | |
isassignor | boolean | | not null |
copubshare | double precision | | |
idclient | integer | | |
Index :
"ctract_pkey" PRIMARY KEY, btree (idctract)

\d roles (19 rows)
Table « public.roles »
Colonne | Type | Collationnement | NULL-able | Par
défaut
------------+-----------------------+-----------------+-----------+------------
role | character varying(3) | | not null |
libelle | character varying(50) | | |
type | character varying(1) | | not null |
libelle_en | character varying(50) | | |
Index :
"roles_pkey" PRIMARY KEY, btree (role)

\d ad (55 rows, many fields removed for readability)
Table « public.ad »
Colonne | Type |
Collationnement | NULL-able | Par défaut
------------------------------+-----------------------------+-----------------+-----------+--------------------------------
idad | integer |
| not null | nextval('ad_id_seq'::regclass)
codecle | character varying(20) |
| |
nom | character varying(100) |
| |
idclient | integer |
| |
Index :
"ad_pkey" PRIMARY KEY, btree (idad)
"i_ad_codecle" btree (codecle)
Contraintes de clés étrangères :
"ad_idclient_fkey" FOREIGN KEY (idclient) REFERENCES
clients(idclient) ON DELETE SET NULL

\d clients (0 row)
Table « public.clients »
Colonne | Type | Collationnement | NULL-able |
Par défaut
-----------+------------------------+-----------------+-----------+-------------------------------------------
idclient | integer | | not null |
nextval('clients_idclient_seq'::regclass)
name | character varying(200) | | not null |
idsociete | integer | | |
is_us | boolean | | | false
Index :
"clients_pkey" PRIMARY KEY, btree (idclient)

\d sprd (249 rows)
Table « public.sprd »
Colonne | Type | Collationnement |
NULL-able | Par défaut
------------------+------------------------+-----------------+-----------+------------
idsprd | integer | | not null |
name | character varying(30) | | not null |
doesperf | boolean | | not null |
doesmech | boolean | | not null |
country | character varying(100) | | |
perflocalclaim | double precision | | |
mechlocalclaim | double precision | | |
perfforeignclaim | double precision | | |
mechforeignclaim | double precision | | |
tisn | integer | | |
wantsagreement | boolean | |
| false
Index :
"sprd_pkey" PRIMARY KEY, btree (idsprd)

JC