Morning

A chairde - Morning all!

@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?

@Vérace you have this in the first query of your answer, right?

ELSE ((t1.todo_count::REAL/t1.allcount::REAL) * 100)::TEXT

but t1 there is a single row result table, so no difference if we use REAL or NUMERIC or even a 1000 digits imaginary datatype

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.

@Vérace did you try with various or some absurdly huge precision as well, eg, numeric(1000)?

Would you care to to discuss this answer, @Vérace?

@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 :-)

Off for walk down canal - be back in ~ 1hr...

@Vérace the answer is ok. This use of variables is common in MySQL, as window functions were not available.

See another method here: dba.stackexchange.com/questions/198697/…

No variables, no window functions, no CTEs, no LATERAL joins. Plain SQL, works in any version.

First saw this method from Quassnoi (in SO or his blog, can't remember): stackoverflow.com/questions/1201296/…

His answer is for TOP 1. Mine in dba is a modified version for TOP 2.

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).

See what happened (long ago) in the next to mysql mariadbland: mariadb.com/kb/en/group-by-trick-has-been-optimized-away

Morning

morning

who names their table ['20210617145928-Exception - No C$'] ...

Every table and column was named George-X. His boss name was George.

@JohnK.N. and they probably had another table where the actual customer info resided lol

Every table tells a story, don't it

Whoo