Thread: Recursive query slow on strange conditions
Hello, I have a performance/regression problem on a complicated query (placed into a function) when some tables are empty. On Pg 11.6 the query takes 121ms On Pg 12.2 it takes 11450ms I first sent a message to the pgsql-bugs mailing list : https://www.postgresql.org/message-id/16390-e9866af103d5a03a%40postgresql.org But was redirected here. I was also told to post the actual problem, not a simplified version (called "toy tables" by Tom Lane). This is the first line of the plan : Sort (cost=812647915435832343755929914826593174290432.00..812652524250886044745873982078186103504896.00 rows=1843526021485360431505148111877616697344 width=1362) (actual time=1.443..1.443 rows=0 loops=1) The database is (full) vacuumed and analyzed. Since the query plan is more than 560 lines and the query itself ~400 lines, I'm not sure it's efficient to post everything in an email. I have rather prepared a .backup of the database in custom format (made with PG 11.6), dropping all big unused tables so that it's ~500Kb. It is available here : http://freesofts.thefreecat.org/sage11demo_simple.backup In order to test the problem, you can just call : select * from findcontracts('{13}',7,true); If it is more convenient to post everything in an email, just let me know. Thanks for your help.
On Mon, Apr 27, 2020 at 07:49:50PM +0200, Jean-Christophe Boggio wrote: > I have a performance/regression problem on a complicated query (placed into > a function) when some tables are empty. > I first sent a message to the pgsql-bugs mailing list : > https://www.postgresql.org/message-id/16390-e9866af103d5a03a%40postgresql.org => BUG #16390: Regression between 12.2 and 11.6 on a recursive query : very slow and overestimation of rows The most obvious explanation is due to this change: https://www.postgresql.org/docs/12/release-12.html |Allow common table expressions (CTEs) to be inlined into the outer query (Andreas Karlsson, Andrew Gierth, David Fetter,Tom Lane) |Specifically, CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only oncein the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifyingNOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query. So you could try the query with ".. AS MATERIALIZED". > On Pg 11.6 the query takes 121ms > On Pg 12.2 it takes 11450ms > > Since the query plan is more than 560 lines and the query itself ~400 lines, > I'm not sure it's efficient to post everything in an email. 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. FYI, I had a similar issue: https://www.postgresql.org/message-id/flat/20171110204043.GS8563%40telsasoft.com And my solution was to 1) create an child table: CREATE TABLE x_child() INHERITS(x) and, 2) change the query to use select from ONLY. (1) allows the planner to believe that the table really is empty, a conclusion it otherwise avoids and (2) avoids decending into the child (for which the planner would likewise avoid the conclusion that it's actually empty). -- Justin
På mandag 27. april 2020 kl. 20:10:34, skrev Justin Pryzby <pryzby@telsasoft.com>:
On Mon, Apr 27, 2020 at 07:49:50PM +0200, Jean-Christophe Boggio wrote:
> I have a performance/regression problem on a complicated query (placed into
> a function) when some tables are empty.
> I first sent a message to the pgsql-bugs mailing list :
> https://www.postgresql.org/message-id/16390-e9866af103d5a03a%40postgresql.org
=> BUG #16390: Regression between 12.2 and 11.6 on a recursive query : very slow and overestimation of rows
The most obvious explanation is due to this change:
https://www.postgresql.org/docs/12/release-12.html
|Allow common table expressions (CTEs) to be inlined into the outer query (Andreas Karlsson, Andrew Gierth, David Fetter, Tom Lane)
|Specifically, CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query.
The OP's query is recursive, sow no inlining will take place...
--
Andreas Joseph Krogh
Andreas Joseph Krogh
> 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
Hello, I have rewritten the function/query to make it a PLPGSQL function and split the query in ~20 smaller queries. Now the problem of the JIT compiler kicking in also happens on PG 11.6 Although the 2 seconds induced delay is not a serious problem when I execute the query for thousands of items, it really becomes one when querying ONE item. Is there a way to disable JIT (I use the apt.postgresql.org repository) in both 11.6 and 12.2 ? I would have liked to disable it on this particular query but maybe I could live with disabling JIT everywhere. Thanks for your help, JC
On Mon, May 4, 2020 at 9:12 AM Jean-Christophe Boggio <postgresql@thefreecat.org> wrote:
Is there a way to disable JIT (I use the apt.postgresql.org repository)
in both 11.6 and 12.2 ? I would have liked to disable it on this
particular query but maybe I could live with disabling JIT everywhere.
David J.
> https://www.postgresql.org/docs/12/jit-decision.html Thanks a lot David, I missed that part of the doc. JC