Bless me Father, for I have sinned! "What troubles you, my son?" Against my better judgement, I succumbed to the sin of posting when the OP hadn't responded to requests for more information Father! "Well, that's not too serious...". There's more father, (now tearful...), I, I (stammers...) engaged in commenting Father, multiple times...
"Oh dear! This changes matters.... You can...". (Sobs come from through the grille) There's yet more Father... "What can it be, my child?"... Father, I engaged in the shameful act of sarcasm!" (Expresses surprise) "My child!?" Yes, Father, and what's even worse is that I blasphemed!" (Coughing and spluttering comes from priest's side of grille - hoarse, but stern, voice "Go on!".
I paraphrased a koan of the infidels Father!" "Very well, as these matters are not mortal sins, but serious venial ones,
I'm giving you the following penance - you shall delete 50 comments, and clear the review queues twice a day for a week and finally, you shall write out Codd's laws 12 times, in copperplate in your copybook and show it to me next week!". Yes Father, thank you Father White... (pentient leaves, head bowed)...
I'm using postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts).
I can't figure out why Query2 below is so much slower than Query1 (they should basically be almost exactly the same, maybe a few ms difference at most)...
Query1: This quer...
I would like advice from older (well....) or wiser (certainly) heads than mine. At what point should I stop responding to an OP who seems to ignore my entreaties for more info. This guy posts PLANs with tables that he doesn't refer to in his question (I'm assuming VIEWs and ask about them), but don't get anything back... At what point should I stop responding - it's an interesting question but I think that I'm flogging a dead horse...
He then edited his post some 6 hours ago (i.e. at ~ 03:00 UTC) talking about "some kind of "Schrodinger's cat" - which is appropriate, since we haven't been given all information... I think the OP may have found a bug in PostgreSQL, but can't tell unless he provides a test case... anyway, just venting this morning - a chairde. Good morning all!
Just off to carry out my penance - by removing my comments from that question - I'll leave one just to let the OP know what I think of his responding to FoI request skills (i.e. more info - the FoI thing is to do with the Freedom of Information Act here - currently under "refinement"...)
@ypercubeᵀᴹ I'm not saying that's the bug - the optimiser is failing to recognise that the final part of the query is just one op on one result?
I appreciate that query plans can differ for all sorts of reasons (cardinalties, indexes... phases of the moon...) - but I thought that the WITH cte_name AS [ NOT ] [ MATERIALIZED ] should have put this one to bed?
The answer solves the proximal problem - the distal one is, I believe, a PostgreSQL internals thing - and maybe even out of scope here - it's down to source code...
@Vérace you can add to your answer that one difference between the 2 plans provided (what I can see, I may have missed something else) is that the first (faster) one has Parallel Seq Scan on xformula xformula_1 while the 2nd has Seq Scan on xformula xformula_1.
That may well explain the different between 42 and 269 sec.
Other suggestion they can try (feel free to add them in your answer), is to separate the 3 aggregations in 3 (MATERIALIZED) CTEs, not all in one.
@ypercubeᵀᴹ I'll put it in later - enjoying a coffee - and to be honest, I'll probably hear the whooshing sound of it going over the OP's head from where I'm sitting! BTW, what do you think of my wrapping idea? That should skirt the fence?
I was actually thinking of using the deadly generate_series to run up a test case and try it out - may ask a question of my own - though it may be an internals thing?
The problem may just go away if they separate the 3 aggregations. Or it might get worse, who knows!
Another subquery wrap of a function with a CASE on a wrap on CTEs (with OFFSET and MATERIALIZED) and inline subqueries ... It looks like they are trying their best to confuse the optimizer. And succeeding very well.
@ypercubeᵀᴹ - fiddles 1, 2 & 3 - first is tables only, no functions, 2nd is views only, no functions and the third is views only with functions.
Plans for 1, 2 and 3 are here, here and here. The major difference that I can see is that plan 3 is shorter but has no parallel seq scans and takes twice as long! Any comments or observations?
If you need any more info, let me know. Oh, the plans I've shown are from 3 different instances of PostgreSQL run wtih 10,000,000 (10M) records in test_1 - couldn't do that on dbfiddle! :-)
Looks like you were on the money - but I'm still puzzled - the Parallel stuff only appears to make the fxn query half as fast - but the OP finds it to be 20% as fast... (here )... any comments?
3 different instances so there's no caching that we don't know about going on! Could the OP have 4 (or some relatively high) number of processors and the system is being throttled by this? My system only show 2 workers - I have 2 cores with 4 processors according to the spec.
But, looking at dbfiddle - there's no Parallel seq scans - minimal hardware?
@ypercubeᵀᴹ - I looked again at the OP's plans - the huge difference is the HASH JOINs - 243s vs. 43s (6 fold diff) - is this related to the scans? I"m not expert in reading plans - (yet, it's very much on my to-do list) - but the scans don't appear to be that much longer 9s & 3s (slow) vs. 1.3s each (fast). So, we know that the OP has an MP machine - he has 7 workers (8 core machine?) - this would kinda explain the 5/6 fold diff? I wish I knew WTF the OP was doing behind the scenes!
Final post (for today) - check out the last snippet here (tables) - also here (views) - the function doens't act as an optimisation fence when you MATERIALIZE the CTE... I don't know what the OP did with [ NOT ] [ MATERIALIZED ] - but there's a problerm with his question!
