@ypercubeᵀᴹ Indeed, he did not say that MATERIALIZED would not make a difference. Careful rereading shows that he means that using it is a sine qua non, but the focus of his answer was on how to inline the function. As to your question about multiple workers - yes, that works fine with EB's rewritten function. I'm assuming that dbfiddle isn't run on high-level hware and may be on a minimal VM somewhere with only 1 CPU, hence no workers on dbfiddle (and hence some of my confusion)...
In my reply to EB, I forgot to include this fiddle - where I test my own (silly) text returnig function against EB's - over 100M runs, there's a delta of 16/1000ths of a second.
My other question was meant to be why so small a difference - FLOAT4 is a "CPU native types" (see end of this post) and NUMERIC is not - any input appreciated...
@Vérace I didn't look at that part. I don't think using REAL or FLOAT or NUMERIC would make much difference or matter for the question but haven't done any tests. Just felt that that part of the discussion was orthogonal to the question, you could have left it out.
I guess I"ve moved on since - EB has resolved the OP's issue - this is my intellectual curiosity kicking in - and I don't like misguiding people here and I have urged CASTs to REAL before on the understanding that it was best practice and more performant.
Whereas EB doesn't see it as a big deal - great, but I'd just like to understand why?
Yes - the direct function calculation is obviously the way to go - as EB says at the end of his answer. He dealt with the situation where the OP was continuing in their pursuit of functions and explaining the ins and outs of that (language SQL vs PLpgSQL). Following EB's answer, I then concocted my own ridiculous TEXT returning function which involves CASTing to FLOAT4 instead of to NUMERIC.
I ran EB's funciton and my own 100M times - no query involved - and obtained a difference of 16/1000ths of a second over the entire run. See the last two entries in the fiddle here.
I went to my favourite MySQL "tips and tricks" site here and went to the common queries link and looked for the Top N per group section. The great thing about this site is that it tells you how to do stuff in MySQL for all versions - well, going back at least to MySQL 5.5 - and if you're still ru...
@ypercubeᵀᴹ No, I didn't try to use numbers like that - my point is that if REAL is a CPU native type, then maybe it shoiuld be faster than a (partially) s/ware based type such as NUMERIC - surely even a small overhead should become apparent over 100M runs? Re. would I care to discuss my answer there - yeah, sure... I"m pretty much up for discussing anything here - and not just databases...is there an issue with the answer that'd you'd like to pick me up on? I welcome any and all criticism :-)
The problem with using variables is that the queries work but it's a hack. There is no guarantee that the query will continue to work the same way in a version upgrade because it relies on the subquery being processed with a specific ORDER BY).