Morning

Morning

Seems like an interesting question

Well that didn't take long.

This one is up for deletion, due to accepted crosspost on Stackoveflow

A chairde - Morning all!

Morning

@Vérace I see you posted an answer with some benchmarks

oh gawd...

Can you imagine being a moderator for a Skeptics and downvoting every question that expresses skepticism about products produced by for-profit companies (the dominant mode of production in our society).

The fact that moderator do not stand for re-election is such a blight on the network. How much damage does a moderator do that downvotes every question that serves the explicit goal of the site.

@ypercube - yes, the numbers are merely indicative - I'll stress that in the text - I tried with numbers in the low 100's and the runs kept failing. As I said in the answer, I did run several runs of each benchmark and they were remarkably consistent! I'll try a few with 37 & 54 and see if they're consistent (relative magnitude) - I'll mention that in text! Thanks for the interest - any other thoughts/views/considered opinions?

@McNets - in the answer referred to above, I changed your code to be IMMUTABLE STRICT and obtained a ~ 15% improvement (several runs!).

@Vérace thanks, I'll edit the answer.

@EvanCarroll The breadth of topics on which you are able to pick fights is truly astounding.

Plumbing a niche industry?

Only some people have to take a dump?

@Vérace were my queries included in the tests?

@mustaccio s/pick fights/shine at/

Depends on your definition of shining I guess. Certainly doesn't make you a dull boy.

@ypercubeᵀᴹ - OMG - I'm so sorry - I mixed you up with a_horse_with_no_name... corrected - your solution appears to be going in the right direction - I'll run a couple of tests on my local machine and report back.

@HannahVernon Would be fairly easy to answer if string_split maintained order

@EvanCarroll - you've moved into the lead in my benchmarks!

My solution involves turning the database upside down then back really fast

In most typefaces, Hebrew letters aren't as thick so I think it will work.

😮

@ypercubeᵀᴹ - I'm surprised that EB's solution is so poor?

@Vérace remind me about that in a month. I'll go for 100x performance on the best benchmark for fun. ;)

That's some really awesome work you did. ;)

Feel free to upvote! :-) I did this because I wanted to get to grips with ARRAYs and UNNEST and all of those goodies...

Or can you not vote because you're suspended? :-) I got a suspension from meta... I was somewhat irate about my reviews in the first post queue (now first question/first answer) being zapped - since reversed. I was forthright but I didn't use any of the B, C, S or F words! Still got a suspension...

@Vérace I bountied it.

@Vérace I'm not suspended on dba.se.

@Vérace I've only been suspended on dba.se like a dozen times?

nm

Once every year the mods move the line on me by a few nano-meters. They're entirely unjustified every time.

@EvanCarroll It's a 2017 question

OP still active

He lives really close to me actually.

@Vérace thnx but why does the link to my fiddle has RECURSIVE in 2 places? My queries have no recursive use at all.

Foul play is afoot!

Not that I'd expect them to be the fastest. They were as simple, to demonstrate how to solve the problem.

@ypercubeᵀᴹ OK - can you show me what it should be? I may have been rushing? I'm pretty sure that for you I benchmarked the parameters p, GEN_SER, GEN_SER, GEN_SER fiddle - please let me know what you'd like me to benchmark? Oh, yeah, EB's solution wasn't so poor after all - in the more extensive tests (home), his solution starts to shine! Quelle surprise!

@EvanCarroll That should get the interest up! On what trump-ed-up (note the pun on the world's fave pres...) charges were you suspended?

@Vérace You mean my suspension to StackOverflow?

I'm not suspended on dba.se currently

My SO suspension expires today

@EvanCarroll Just curious as to what leads to suspension - expressing oneself in direct, no-nonsense manner obviously counts (me!) - what else earns one battle scars? Not being a fervent admirer of the Israeli state?

Yeah.. I'm suspended on Information Security for calling Israel an Apartheid State, which they considered to be "genocidal"

But my latest suspension on StackOverflow had nothing to do with that.

@ypercubeᵀᴹ - I know why your fiddle has a RECURSIVE CTE. You had a numbers table (1... large number) whereas I used an RCTE - so to level the playing field, I used it - even though I didn't call it - couldn't get your SQL to work. The only thing that was allowed was a parameter table containing (3, 5) or (34, 57)/(340,570) since that was all I allowed myself and the others - trying to play fair by everybody! Sorry for the confusion! :-)

so "level the field" means my query was so fast that you had to tune it down, I see ;)

@Vérace direct no-nonsense and direct nonsense may have the same effect ;)

I mean being direct can lead to problems. Whether it's nonsense or not makes little difference.

@Vérace something like this: dbfiddle.uk/…

I really don't see why you mixed a recursive CTE with my solutions. My first one used generate_series(), the second a numbers table.

dbfiddle has not problem running it with 340, 570 parameters: dbfiddle.uk/…

260 - 520 ms

@ypercubeᵀᴹ OK - then I'll have to run all of the ones that rely on a numbers table with a numbers table! Also, hot off the presses, EB's query only returns 1/100 of the number of rows that it's supposed to - plus, somehow, negative numbers get in there!!! Run it and see - there's only 3,000 odd in his query! Since @EvanCarroll has bountied the question, I'm altering him also! The correct number of records is 570 * 340 * 2 = 387,600!

@EvanCarroll - make that "alerting" him also! :-)

@Vérace link?

@ypercubeᵀᴹ - false alarm - my fault - hadn't given him enough room in the numbers table (i.e. my RCTE)!... have to watch that... link here. Should have known better than to doubt EB! You'll be glad to know that I'm clubhouse leader - got it down to 22/23ms for the home benchmark - that's even better than the PL/pgSQL functions.

I'll create a numbers table tomorrow and I'll remove the ORDER BY which he says is unnecessary - I don't have one - these ARRAY and UNNESTs preserve order! @EvanCarroll

Can't get it to run with 340/570 on dbfiddle though - not sure why?

I know what happened - EB has an RCTE for creating numbers with 20,000 as the limit - I think that's where that came from!

@Verace new donkey in the race: dbfiddle.uk/…

select
  gn.i
from
  ( with g(i) as
    ( select * from generate_series(1, 3) )
    (select i from g)
    union all
    (select i from g order by i desc)
  ) as gn,
  generate_series(1, 5) as gm (j) ;

The UNNESTs and ARRAYs have it by a country mile - it's a donkey alright compared to my thoroughbred! :-) ~ 40ms vs. 22ms on the home (340/570) benchmark!

You're in 2nd place - leading @EvanCarroll by a nose - ~ 40ms vs. ~ 45ms - and he's the man attributing the bounty! :-)

which version of Postgres are you testing on?

just for educational purposes

I know, the dreaded Windows - had a coffee spill in my backpack... currently drying out my lovely 32GB RAM laptop... had to buy this machine and haven't had a chance to repartition yet...