pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRecursive query slow on strange conditions
7+ messages / 4 participants
[nested] [flat]
* Recursive query slow on strange conditions
@ 2020-04-27 17:49 Jean-Christophe Boggio <postgresql@thefreecat.org>
0 siblings, 2 replies; 7+ messages in thread
From: Jean-Christophe Boggio @ 2020-04-27 17:49 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Recursive query slow on strange conditions
@ 2020-04-27 18:10 Justin Pryzby <pryzby@telsasoft.com>
parent: Jean-Christophe Boggio <postgresql@thefreecat.org>
1 sibling, 2 replies; 7+ messages in thread
From: Justin Pryzby @ 2020-04-27 18:10 UTC (permalink / raw)
To: Jean-Christophe Boggio <postgresql@thefreecat.org>; +Cc: pgsql-performance@lists.postgresql.org
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.
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Recursive query slow on strange conditions
@ 2020-04-27 19:37 Andreas Joseph Krogh <andreas@visena.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 7+ messages in thread
From: Andreas Joseph Krogh @ 2020-04-27 19:37 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Jean-Christophe Boggio <postgresql@thefreecat.org>; pgsql-performance@lists.postgresql.org
På mandag 27. april 2020 kl. 20:10:34, skrev Justin Pryzby <
pryzby@telsasoft.com <mailto: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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Recursive query slow on strange conditions
@ 2020-04-27 20:22 Jean-Christophe Boggio <postgresql@thefreecat.org>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 7+ messages in thread
From: Jean-Christophe Boggio @ 2020-04-27 20:22 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Recursive query slow on strange conditions
@ 2020-05-04 16:12 Jean-Christophe Boggio <postgresql@thefreecat.org>
parent: Jean-Christophe Boggio <postgresql@thefreecat.org>
1 sibling, 1 reply; 7+ messages in thread
From: Jean-Christophe Boggio @ 2020-05-04 16:12 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Recursive query slow on strange conditions
@ 2020-05-04 16:20 David G. Johnston <david.g.johnston@gmail.com>
parent: Jean-Christophe Boggio <postgresql@thefreecat.org>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2020-05-04 16:20 UTC (permalink / raw)
To: Jean-Christophe Boggio <postgresql@thefreecat.org>; +Cc: pgsql-performance@lists.postgresql.org
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.
>
>
https://www.postgresql.org/docs/12/jit-decision.html
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Recursive query slow on strange conditions
@ 2020-05-04 16:25 Jean-Christophe Boggio <postgresql@thefreecat.org>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jean-Christophe Boggio @ 2020-05-04 16:25 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
> https://www.postgresql.org/docs/12/jit-decision.html
Thanks a lot David, I missed that part of the doc.
JC
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-05-04 16:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-27 17:49 Recursive query slow on strange conditions Jean-Christophe Boggio <postgresql@thefreecat.org>
2020-04-27 18:10 ` Justin Pryzby <pryzby@telsasoft.com>
2020-04-27 19:37 ` Andreas Joseph Krogh <andreas@visena.com>
2020-04-27 20:22 ` Jean-Christophe Boggio <postgresql@thefreecat.org>
2020-05-04 16:12 ` Jean-Christophe Boggio <postgresql@thefreecat.org>
2020-05-04 16:20 ` David G. Johnston <david.g.johnston@gmail.com>
2020-05-04 16:25 ` Jean-Christophe Boggio <postgresql@thefreecat.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox