Re: Recursive query slow on strange conditions - Mailing list pgsql-performance
| From | Jean-Christophe Boggio |
|---|---|
| Subject | Re: Recursive query slow on strange conditions |
| Date | |
| Msg-id | 5571421b-474f-c9de-2acc-ccd774ad9780@thefreecat.org Whole thread Raw |
| In response to | Re: Recursive query slow on strange conditions (Justin Pryzby <pryzby@telsasoft.com>) |
| List | pgsql-performance |
> 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
pgsql-performance by date: