« first day (3982 days earlier)      last day (60 days later) » 

8:01 AM
3 hours later…
11:01 AM
A chairde - Morning all!
2 hours later…
1:06 PM
Guys, I'm in a quandary - I've been on a bit of a benchmarking fetish recently - c.f. the long-running saga with Evan and myself. That aside for the moment (tests ongoing...), I answered a question this morning and used a REGEXP_REPLACE (PostgreSQL) to answer the question.
Being a curious chappie, I just took a quick look at how I'd solve this using non-regexp functionality. I did come up with a method, and it's as convoluted as many a regex. So, I decided to benchmark both solutions and to my astonishment, my convoluted solution is consistently 10% (1M runs of each, several times on db<>fiddle) faster than the simple regex one.
Now, I know that regexes are expensive, but I didn't expect this. I'm all the more surprised because I ran similar benchmarks 1M runs, here related to this question and the rexexp was <10% of the convoluted solution.
Just wondering if my benchmarking is off - is the code OK (think it is...) or are there any problems with what I've done? Any input/suggestions, URLs &c. welcome.
@Vérace-getVACCINATEDNOW what was the question that E sent you a C solution?
I think I tried something on some question but can't remember which one
@ypercubeᵀᴹ - it was this one. I've pretty much done with the benchmarking. Re. ping - yes, hit return key too soon!
did you try to send me half a ping? ;)
will you do on this one as well?
Q: How can I calculate all grouping permutations of an input string in SQL?

Evan CarrollGiven an input like "ABC" generate a query that calculates all potential splits of 0 or more of the given string, Desired output, A B C A BC AB C ABC Given an input like "ABCD" A B C D A BC D A B CD AB C D A BCD AB CD ABC D ABCD Not all that concerned with how ...

I think I have an improvement
Regex. Now you have two problems.
Jan 17 '20 at 12:56, by Tom V - Team Monica
regex sucks in any language
@PaulWhite I fail to understand these knee-jerk anti-regex reactions. They, like any other arrow in the programmer's quiver, have their place. Take a look at the code I had to resort to without REGEXP_REPLACE!
1:15 PM
Mar 13 '13 at 8:20, by Remus Rusanu
@ypercube the guy is already asking about WaitforMultipleObjects... Just add regex use for a perfect storm
SQL is like poetry - one is only limited by imagination!
May 6 at 19:04, by ypercubeᵀᴹ
@Vérace the regex in combination with a malformed post resulted in a 34 minute outage of SO
I could go on, but it was only a small joke
Things that are very powerful don't always perform well in simple cases
@Vérace-getVACCINATEDNOW they have their place, sure. In the never-use-this toolcase ;)
Dec 27 '20 at 11:14, by ypercubeᵀᴹ
regex is one of the things I avoid
@PaulWhite ha! nice one. almost identical to my recent comment
1:28 PM
@ypercubeᵀᴹ Take a look at the last two snippets here (30 lines of code becomes 3) - how much time does code spend in maintenance?
Unless you can improve my code?
@ypercubeᵀᴹ Is the improvement in yet? It already looks good! The word_permutations_yper(_word text) function snippet at the bottom of the community wiki is pretty good! We could add a GENERATE_SERIES(1, 1000...) to test?
Single runs aren't reliable!
@ypercubeᵀᴹ - any comments on my benchmarking here?
2:34 PM
Hi @Vérace-getVACCINATEDNOW - I got your flag, but I don't think there's an urgent need to purge any messages from the record. A follow-up message saying "Oops, that was incorrect" suffices, and avoids leaving a mysterious hole in the transcript.
@DMGregory - I actually made an error about making an error! :-) Brain fried today for some reason! Anyway, it's all good - the original actually stands! Thanks for your time and input!
flag? here?
Oh I see
2:56 PM
@Vérace-getVACCINATEDNOW Maybe your brain is using regexes today
@PaulWhite Excellent - 10/10! :-)
3:13 PM
But yes, removing messages you regard as erroneous isn't a good use of network-wide chat moderators' time. Accidentally posting passwords or PII? Sure, flag away. Room owners can also assist with trashing unnecessary messages, but removing stuff that would invalidate following discussion isn't something to do lightly.
In other news, I see Jack Dorsey is stepping down.
Perhaps Twitter will become suddenly less awful?
The share price has reacted positively so I guess there must be something to it.
Beyond the possibility of an edit button I mean.
Sorry, I was wrongly banned again.
Oh hai Evan
Apparently, I can't even bounty questions and give away EXP.
Say WHAT again
I mean the bar to get wrongly banned on dba.stackexchange.com is so low now all you have to do is find a good question and reward an answer EXP.
that's literally all you have to do to get banned.
3:24 PM
@EvanCarroll All is forgiven Oh Prodigal (or should I say Prodigious?) Son! I've been benchmarking - I'm in the middle of something at the minute, but I hope to have the results posted by this evening - maybe 13/14:00 your time. Unfortunately, the results are ambiguous, but in the interests of fairness, I'm going to publish the lot.
It wouldn't be honourable to just publish those that show my answer to be the winner... I've suddenly become very interested in actually writing the sort of code that you used in your extension. Anway, I'll be in touch later...
@EvanCarroll Don't be ridiculous. No one ever got banned for that.
@PaulWhite I literally rewarded @TomV's answer on its merits and got banned.
Unless you're saying TomV's answer sucked and I was lying.. in which case you two probably need to fight it out.
I'll lend both of you an AR-15 if you come to Texas.
I <3 both.
@EvanCarroll This has all been explained to you. It's right here in the transcript. You objected to having to double a prior 250 bounty on a question and asked for someone else to place a 250 point bounty for you. When Tom did that in good faith, without understanding the context, you repaid the 250. Despite warnings not to. That's circumventing a system-imposed restriction. When I cancelled the bounty, you immediately reissued it. That's it.
So what you're saying is Tom with no knowledge of a future 250 EXP bounty (ie., in good faith) bountied a question that I had mentioned needs a bounty and awarded it to the wrong answer! And then when I found a question of his that I wished to incentivize in kind I was banned for the same thing. That exactly what I said.
@PaulWhite let's just be clear, do I need you approval to award a bounty on another one of TomV's answers?
"So what you're saying is..." 🙄
3:35 PM
Like what if I find one of his contributions right now that I wish to award? Can I do that?
@EvanCarroll I would heartily recommend you stay away from giving the impression you are deliberately misusing the bounty system, yes.
Yeah, but aside from that "hearty recommendation" you would ban me for using the bounty system to award one of TomV's amazing contributions?
I think TomV needs a safe space on this network.
@EvanCarroll If you misuse the bounty system you will be suspended yes.
I don't believe I am being unclear about this.
Do you have any evidence that if I award a bounty right now to TomV that I'm abusing the bounty system. It can't be just an abuse because I do it. Is there even a rule against this?
I think TomV is being oppressed. He's being denied the benedictions and blessings of Evan.
Nov 22 at 1:48, by Paul White
Sure. Several I should think. We don't allow gaming or misusing system features or organising with others to circumvent restrictions. You'll be asking if it's ok to serial up vote someone to repay their bounty next?
This is going in circles. You've been told what not to do. Up to you if you test that.
3:52 PM
Well I found another answer I wanted to reward based on the merits. Let's see what happens.
It already happened. Don't take the piss, Evan. Be a model citizen for a while.
=/ booo well I gave you an upvote anyway.
Richly deserved, I'm sure.
No one ever got banned for giving an upvote, amirite? ;)
Not one up vote, no.
I suppose there's a first time for everything.
3:56 PM
Well, I won't try it on Tom. Don't worry. I'm never upvoting him again.
Lesson learned.
Quite right too. His answers are all terrible.
4:15 PM
@PaulWhite Terribel Tom they call him ;)
typo unintended but spotted, left uncorrected anyway
You'll always be TypoCube to me
Jan 5 '17 at 10:54, by TypoCubeᵀᴹ
I think Erwin has an answer which compares the 3 alternatives (LIKE, SIMILAR, regex).
Continuing a theme
5:06 PM
@EvanCarroll - I'm writing up the benchmarks as we speak (or type...). I'd be interested in any opinion(s) you might have about this answer - the method used is essentially the one I'm using for our joust! :-) I've used 4 different methods to answer the same question!
I'm also very puzzled as to how an answer that uses UNNEST WITH ORDINALITY and STRING_AGG is faster than a simple REGEXP_REPLACE or a pure string function solution. Any input (ideas, changes, suggestions, URLs) you may have, gratefully appreciated! Right back at you with up_down_series benchmarks...
@PaulWhite Stepping down "immediately"? Was he caught with his hand in the cookie-jar? Or somewhere else it shouldn't have been?
@Vérace-getVACCINATEDNOW what happens probably is that the fast method doesn't execute the function call a million times but only once.
The table in your tests has one row only, correct?
@Vérace-getVACCINATEDNOW you know you can answer a question MORE THAN ONCE ;)
@ypercubeᵀᴹ But I'm comparing like with like?
@EvanCarroll Indeed yes - I generally only do this if I'm writing a Community Wiki answer which I've received from someone else and don't want to claim as my own!
try a different test with a table with a few thousand strings. without any generate_series
@ypercubeᵀᴹ Yes - I'll have to generate random strings with dollar signs... sigh... any chance you could write a quick fiddle? I'm doing Evan's benchmark at the moment...
@ypercubeᵀᴹ plus, the results are consistent over many runs - at least 12/15 times!
5:19 PM
@Vérace-getVACCINATEDNOW my opinion is that a regexp_replace done right would be faster.
@Vérace-getVACCINATEDNOW IE, without the reverse.
@EvanCarroll code?
@Vérace-getVACCINATEDNOW ah, it is only slightly faster. I was looking at the planning time before
here's an even faster one: dbfiddle.uk/…
    t_field placing '-'
    from LENGTH(t_field) + 1 - STRPOS(REVERSE(t_field), '$')
) AS result
SELECT regexp_replace(t.x, '(.*)\$', '\1-' )
FROM ( VALUES ('bar$foo$john$doe$xxx') ) AS t(x);
That will replace the last "$" with "-"
@Vérace-getVACCINATEDNOW pasted as an answer
A: PostgreSQL: How to replace the last occurrence of a character in a string?

Evan CarrollYou can do this with a regex and a capture paren using regexp_replace SELECT regexp_replace(t.x, '(.*)\$', '\1-' ) FROM ( VALUES ('bar$foo$john$doe$xxx') ) AS t(x); Will replace the last $ with -. The end result is, bar$foo$john$doe-xxx Here is how it works, captures everything before the last...

@EvanCarroll - upvoted! (Oooops... am I allowed to say/do that?) :-)
Only if you ask Paul and get explicit written permission.
That's the only way you can be sure it's allowed.
5:30 PM
@Vérace-getVACCINATEDNOW you are skating on thin ice
@EvanCarroll - bad news I'm afraid - My VM (ran a bm last night) crashed and I lost the results - am going to rerun this evening with tee... sorry about that! Just to be clear about your regex - say I wanted to delete the second last $ sign, I could put in '\2-'?
you could do something like '(.*)$(.*$.*)' '\1-\2'
so long as .* is backtracking like a PCRE that should work.
@Vérace-getVACCINATEDNOW is that regexp_replace faster?
@EvanCarroll - I'll run a bm this evening on all solutions on home machine - no confounding factors with not knowing what's going on with dbfiddle. Could you explain (from here) what this means:**Lookahead and lookbehind constraints cannot contain back references (see Section, and all parentheses within them are considered non-capturing.**?
You know how PostgreSQL has double dollar quote strings. $DOGZ$verace$DOGZ$
You can't do (\$[A-Za-z]+\$)(?=\1) to match them.
That would be using a back-reference in a look ahead.
> all parentheses within them are considered non-capturing.
That means that (?=(foo)) doesn't capture foo and store it so you can do \1 and figure out what it captured. That (foo) is effectively (?:foo) ie., non-capturing
5:52 PM
@Vérace-getVACCINATEDNOW when you finish discussing the horrible regexes with awesome E, please award my points for providing the faster solution, and fastest gun in the west ;)
You'll have to ask on meta.dba.se first to see if that's an approved distribution
@ypercubeᵀᴹ In what is now becoming a tradition, I will put your answer in as a Community Wiki - and thanks for expanding my string manipulation horizons - I thought my answer had the question pretty well-covered and then whammo - you and Evan (peace be upon Him) come back with lovely answers which blow my humble efforts out of the water... Nonetheless, I'm delighted because I'm learning heaps...
no please
@ypercubeᵀᴹ - I'll add yours to mine but make it clear that I was not the originator of the solution?
that's fine
ha, that still works
2 hours later…
7:41 PM
@EvanCarroll - you've knocked it out of the park! Approx. 7.5% of the next best solution which is (surprise, surprise) @ypercubeᵀᴹ 's! I come in a sorry third. Why is it so quick - what about the notorious regex penalty? The speed of regexes is borne out by a test that I ran here - different question...
Final answer here and if some b**£$!ard comes up with another method, I swear that I'll haunt them from beyond the grave! :-)
@Vérace-getVACCINATEDNOW your benchmarks are borked.
@bbaird - you may remember that we had a discussion about COVID vaccination in the DRC? Listening to the news this lunchtime, the guy (can't remember who...) said that in the DRC, only 1/1000 people were vaccinated!
@EvanCarroll why the borking hell are they borked?
@Vérace-getVACCINATEDNOW dbfiddle.uk/…
7:56 PM
@EvanCarroll OK - I see your point, but WTF is going on?
Not exactly sure to be honest.
However, this is very frequently the problem with the microbenchmarking.
Even on that other problem you suggested. You can get VASTLY different results based on how you query that as a value-per-call function or a materialized function
@Vérace-getVACCINATEDNOW you may want to take it to IRC and ask.
RhodiumToad would be able to explain it to you, I'm sure.
and if you figure it out, it'd be a great self-answered question
8:28 PM
@Vérace-getVACCINATEDNOW I see what's happening
@EvanCarroll Then kindly enlighten us O Wise One!
9:05 PM
@Vérace-getVACCINATEDNOW no idea actually dba.stackexchange.com/questions/303274/…
9:33 PM
@Vérace-getVACCINATEDNOW I even create a tool to help me debug this stuff before, and it shows nothing interesting.
@EvanCarroll Even though it's only microbenchmarking, I'd still like to do it properly. I might take a look at generating random strings with $ signs interspersed in them and look at it that way. But the advantage of db<>fiddle is that everyone can see the results, so I'm very much vested in having proper reproducible results - if they're not, then they're worthless.
This is why I'm spending so much time over the bms that I'm doing of your extension - at the moment it's too tight to call! Not sure if it's a RAM thing - suffice to say that the extension is, at best, ~0.2% better than one of my SQL solutions - I don't think I've voted on your answer yet... (ssshh...) :-)
I'm taking a bit of a break - the VM crashing and losing results has pissed me off, plus I'm tired... talk soon, take care...
Hopefully results ~ 20:00 UTC Wed.
9:58 PM
@Vérace-getVACCINATEDNOW The problem with microbenchmarking is any slight variation -- like reading from a table or a param -- could result in a massively different query plan
In fact, even putting something in select clause vs a from-clause will result in a massively different query plan.
@EvanCarroll - yes - it's just that sort of swing that I (we) would like to avoid! Is it OK if I fire a couple of push requests to your github page - basically just suggestions about phrasing for the moment - I want to get the hang of Github - although I might go with GitLab - it seems to me that they have more respect for F/LOSS?
Not sure on that, I use gitlab at work, but most open source projects are on github so that's my goto. Certainly GitLab has more a better service.
yeah, feel free to send PRs to any of my stuff
10:35 PM
@Vérace-getVACCINATEDNOW see the answer

« first day (3982 days earlier)      last day (60 days later) »