What did I miss in EXPLAIN that hinted at server crippling loads?

In trying to address a query performance problem, I reviewed the explain analyze output and found a couple subqueries were scanning the whole table. This is done to get only the most recent record in those tables related to an appointment. After some research, I decided that it would be reasonable to use a lateral join on those subqueries to dramatically reduce the amount of data scanned. Explain analyze suggested the cost of the whole query with lateral joins would be about a quarter of the original. So we proceeded. Within two hours of deploying the query change, our DB server was maxed out at 100% and basically unresponsive. Reverting the query to use subqueries scanning the tables restored the CPU usage to something sane. Our DB is running in AWS RDS for PostgreSQL using a t2.xlarge. Performance insights showed a substantial increase in ClientWrite. See database load graph.

The query using subqueries along with the explain output: https://explain.depesz.com/s/wES6.
The query using lateral join along with the explain output: https://explain.depesz.com/s/B2vp.

So it is obvious to me I did not understand what the explain output was telling me about the queries. What did I miss that could have told me the DB load would be higher with the lateral join query despite a lower cost?

What are the circumstances of Traxigor’s transformation and do they explain how he retained his magical abilities as an otter?

Traxigor is an Archmage with a rather unique morphology, who is encountered during Baldur’s Gate: Descent into Avernus:

Traxigor was polymorphed into an otter years ago, and decided he preferred the new form to his original one (that of a wizened old man). His otter form was made permanent by a wish spell. He uses the archmage stat block, but is a Tiny beast with a Strength of 3.

What exactly happened here? Are the circumstances of this transformation recorded in any official source material (from any edition)?

This answer establishes that (in D&D 5e) the spell polymorph would not allow a spellcaster to retain their spellcasting abilities while polymorphed into a beast, and this answer establishes the same for true polymorph. On the surface, this seems like an inconsistency (which wouldn’t be surprising), but do the circumstances of Traxigor’s transformation into an otter resolve this inconsistency somehow?

Where in the rulebook does it explain how to use Wises?

In Why I Love the Burning Wheel, at about 39 minutes in the author explains that Wises can be used either to ask for information about the world or to make stuff up wholesale.

Consulting the rulebook, I can see on page 309 that Wises are described as skills through which a character can call upon their knowledge of the world, but I can’t find any reference to the skill allowing you to actually help shape that world.

Can someone direct me to the part of the rulebook that goes into that? Thanks!

How to explain to AD&D player that items that increase Armor Class in DnD5e are overpowered?

Context: I have recently started a campaign with a new player that comes from AD&D background. He wishes to convert and import his character to the campaign. We do that once, I help him with the conversion and checked his stuff, he got 2 magical items lvl3 a bit much but I didn’t mind they were not game-breaking (ring of immunity to poison and mobility boots(longer jumps and a bit of speed up)? His character died, He asked to bring another character this time I let him do, I ask him for his stuff and he says that he has a magical sword and a giant strength belt (lvl6) this is IMO very strong, but again not game-breaking I tweaked a bit the encounter and it went fine.

The thing is that I give him a full plate armour and then he says that his armour class was 23. I was surprised because Max AC without magic is 20 – 21 (with defence combat style (which he has)). So I asked how His sword is :

The Items magical sword +2 attack bonus, magical damage, +2 fire damage, +2 AC.

IMO At this level having a CA of 23 (25 with "shield of faith" he plays a paladin) is game-breaking most monster exception the strongest get attack bonus of +4 +6, Meaning that he will only get hit on crit by most monsters, or that I have to send powerful monsters (that is maybe not fine for other players). The first question is that True? is this AC game-breaking at level 6?

I started to talk about this. and it went bad. He says that it is only 8 % increase of AC and did do much and that he gains his sword on a very deadly quest etc…. Maybe it is a good computation in AD&D setting but in 5e I feel it is not. Does it exist any rational arguments about this?

For now, I come up with the following arguments:

  • AC doesn’t scale the same, and 21 is already the maximum you should expect to be. A venerable red dragon AC is 22.
  • AC increase is extremely costly, with non-magical means they are only two ways: combat style and feats (heavier armour type or the Medium Armor Master feats (+3 dex))

Does there are other arguments? Or maybe I wrong?

AWS RDS is showing very high wait/synch/mutex/sql/ values and EXPLAIN statements in performance insights

I’m running a CRON script which checks the database for work and executes anything that needs to be done. It does this across ~500 customers per minute, but we are using AWS RDS with a 16 vCPU machine which, until recently, has been plentiful to keep it happy (normally plugging along under 20%).

This weekend we updated customers to the latest version of the code and implemented some tooling, and since then we’ve started seeing these huge waits: enter image description here

Further I’m seeing that about half of our busiest queries are EXPLAIN statements, somewhere illustrated here: enter image description here

Nowhere in our code base is an "EXPLAIN" performed (though we are using AWS RDS performance insights, ProxySQL and New Relic for monitoring). I did notice that in the past week our number of DB connections was previously baselined around 10 and is now closer to 90. enter image description here

Any ideas on where I should be digging to find the cause of these waits and explain statements? And if they could justify the large number of open connections?

Explain Like I’m 5 — Why are AMD processors not, or less, vulnerable to Meltdown and Spectre?

All these answers are abstruse and complex. Can someone please explain like I’m 5 by relying on, but varietizing, u/zoox101’s excellent analogy? I copy and paste it here with some trifling corrections and modifications, like differently gendering the librarian and the diary’s owner to avoid confusing pronouns.

At its heart, your computer works just like a library. It’s constantly reading and moving information just like students read and move books. And just like any good library, your computer has a friendly librarian: Ms Kernel.

Whenever you go to check out a book, you give Ms Kernel the title and she goes to fetch it for you. However, this library may contain some pretty secret stuff, so Ms Kernel always checks to make sure you’ve got permission to read the book you’re requesting.

Back in the old days, Ms. Kernel had to do all the work herself, and it was painfully slow. Recently computers are better designed, and can do multiple operations at once, meaning that Ms. Kernel now has a bunch of assistants helping her. This is great for the library, because now it can handle more people than ever before! However, it also creates a weakness that was only just discovered. Here’s how the weakness works.

You, a mischievous ne’er do well, want to read your rival Ed’s diary, which he keeps in the library. However, Ed hasn’t shared his diary with you, so Ms Kernel won’t let you check it out. So you decide to do something rather clever…Rather than asking for the diary directly, you ask Ms. Kernel to fetch two things:

  1. Ed’s diary

  2. a book where the first word in the title is the first word in Ed’s diary.

Back in the old days, this wouldn’t have been a problem. The first thing Ms. Kernel would have done, was to ask Ed if you could read her diary. When Ed said no, Ms. Kernel would’ve stopped.

However the assistants complicate things. To save time, Ms Kernel asks one assistant to ask Ed for permission, while the other goes to find the two books you requested. When the first assistant tells Ms Kernel that Ed said no, the second one gives Ms K the books which Ms. K sets on her desk.

Ms. Kernel tells you that you can’t have the books. However, because they’re sitting on the desk, you can read the titles. The first one’s called "Ed’s Diary" and the second one’s called "The Cat in the Hat". Because you requested a book whose title that begins with the same word as the first word in Ed’s diary, you know that the first word in the diary must be "The".

If you wanted to, you could repeat this process for every word in the diary, until you could read the whole diary.

This exploit endangers all libraries because it works in every library that has assistant librarians, which covers just about every modern processor out there. The only real panacea is to force the assistants to run the check before fetching the books, which will slow down the library as a whole. The biggest vulnerability is that the parallel processing (assistants) is leaving the cache (desk) in a different state than they found it, even though the permission check failed.

Thankfully, no known malware exploits this bug, but the safest thing to do is to update your devices as soon as a fix is released, to prevent them from being infiltrated in the future. The performance shouldn’t slow down most personal devices (small library, few assistants), but will decrease performance on larger machines (i.e. university supercomputers).