Recursive query slow on strange conditions

[Jean-Christophe Boggio]

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.

Re: Recursive query slow on strange conditions

[Justin Pryzby]

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

Re: Recursive query slow on strange conditions

[Andreas Joseph Krogh]

På mandag 27. april 2020 kl. 20:10:34, skrev Justin Pryzby <
pry...@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 

Re: Recursive query slow on strange conditions

[Jean-Christophe Boggio]

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   |