One-row postgres query as CTE/subquery much slower when passing subquery field into function / maybe related to inlining?

I’m using postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts).

I can’t figure out why Query2 below is so much slower than Query1 (they should basically be almost exactly the same, maybe a few ms difference at most)…

Query1: This query takes 49 seconds:

WITH t1 AS (         SELECT             (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,             (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,             (SELECT COUNT(*) FROM racing.xday) AS xday_row_count         OFFSET 0 -- this is to prevent inlining )  SELECT             t1.all_count,             t1.all_count-t1.todo_count AS done_count,             t1.todo_count,             t1.xday_row_count FROM t1 

Query2: This query takes 4 minutes and 30 seconds (only one line difference):

WITH t1 AS (         SELECT             (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,             (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,             (SELECT COUNT(*) FROM racing.xday) AS xday_row_count         OFFSET 0 -- this is to prevent inlining )  SELECT             t1.all_count,             t1.all_count-t1.todo_count AS done_count,             t1.todo_count,             t1.xday_row_count,             -- the line below is the only difference to Query1:             util.divide_ints_and_get_percentage_string(todo_count, all_count) AS todo_percentage FROM t1 
  • Before this point, and with some extra columns in the outer the query (which should have made almost zero difference), the whole query was insanely slow, like 25 minutes, which I think was due to inlining maybe? Hence the OFFSET 0 being added into both queries (which does help a lot).
  • I’ve also been swapping between using the above CTEs vs subqueries, but with the OFFSET 0 included it doesn’t seem to make any difference.

Here’s the definitions of the functions being called in Query2:

CREATE OR REPLACE FUNCTION util.ratio_to_percentage_string(FLOAT, INTEGER) RETURNS TEXT AS $  $   BEGIN     RETURN ROUND($  1::NUMERIC * 100, $  2)::TEXT || '%'; END; $  $   LANGUAGE plpgsql IMMUTABLE;   CREATE OR REPLACE FUNCTION util.divide_ints_and_get_percentage_string(BIGINT, BIGINT) RETURNS TEXT AS $  $   BEGIN          RETURN CASE          WHEN $  2 > 0 THEN util.ratio_to_percentage_string($  1::FLOAT / $  2::FLOAT, 2)         ELSE 'divide_by_zero'          END         ;  END; $  $   LANGUAGE plpgsql IMMUTABLE; 
  • As you can see it’s a very simple function, which is only being called once, from the single row the whole thing produces… how can this cause such a massive slowdown? And why is it affecting whether postgres inlines the initial subquery/WITH (or whatever else might be going on here)

EXPLAIN ANALYZE outputs:

  • Query1: https://explain.depesz.com/s/bq7u
  • Query2: https://explain.depesz.com/s/9w3rY

Creating a online game with a max $20 investment to potentially win much more. Want as little regulations

Creating an online game where maximum entry is $ 20, which is pooled amongst players and, in a series of stages, the players will lose until there is 1 person left who takes most of the pot. The T&C of the game will be that the most the creators (us) are liable for is your initial investment. I guess it’s analogous to a poker tournament.

I want as little regulations as possible. Of course the game will be above-board and fair but I want the government out of it as much as possible.

Is there a particular jurisdiction/country that would be best for incorporating such a business and hosting these type of games?

Securing a replay-based leaderboard system (as much as possible)

A few years ago, I implemented a basic online leaderboard system in one of my games that sent encrypted score data over the wire. The encryption keys were stored in the game client’s binary. Of course, the leaderboards got hacked the same day they were released.

I have now released a new game on Steam, and I’ve worked hard to implement fully-deterministic game logic and a replay system.

The replay system works by simply replaying the user’s input that was recorded in a previous playthrough. The fully-deterministic game logic ensures that the replay will work on any machine, independently of the game’s FPS.

Now, it is time for me to implement online leaderboards.

My idea is to create a server version of the game executable which receives replay files over the network, replays them on some remote machine, and adds the score to a database if the replay data is valid.

While this prevents cheaters from simply sending a fake score to the server, it opens up many other cheating avenues, including:

  • Tool assisted creation of replays, either by slowing down the game speed or by manually crafting a replay file after reverse-engineering its format (the game is open-source).

  • Taking someone else’s replay and sending it over the network, changing data regarding who the replay belongs to.

I could somehow encrypt and compress the replay data before sending it over (or saving it on the user’s local machine), but since the game is open-source, it would be easy to reverse-engineer the encrypted replay format.

What is a good way of securing a replay-based online leaderboard system?

One possible idea I had is to have the server generate an encryption key / token for a specific user which is only valid for a small amount of time, send it over to the client, and only accept replays that are encrypted with that key. This would prevent users from uploading older replays to the server, but in theory it should work — am I missing something?

How much do you have in your Paypal currently?

Just curious to see how much people have in their Paypal. Only the willing ones. This doesn't mean you are poor or rich because there are several payment processors in addition to a bank account. So as you know it, there is a tool in Digitalpoint forums to show your PP balance and Adsense earnings. So connect it to your PP and take snapshot on the top right side of DP forum. If it's OK, let's go…..

Here is mine:

How much damage does a creature take if critically failing a Reflex Save with Improved Evasion?

Improved Evasion (available on at least Rogues and Swashbucklers) provides

You elude danger to a degree that few can match. Your proficiency rank for Reflex saves increases to legendary. When you roll a critical failure on a Reflex save, you get a failure instead. When you roll a failure on a Reflex save against a damaging effect, you take half damage.

How much damage does such a creature take on a critical failure? Full or half?

Is it valid to enjoy RPG without much roleplaying?

When I was much younger than I am now, I used to really enjoy RPGs. Things I remember liking about them include:

  • beating the challenges presented by combat encounters;
  • creative problem-solving and investigative work in non-combat situations;
  • sense of progression as my character levels up and becomes more powerful;
  • (as a secondary consideration) witnessing the story-telling and the world-building done by the GM, and possibly doing some of that myself at my own pace.

At the same time, I specifically did not (and do not now) enjoy role-playing and (for lack of a better term) being charismatic. I prefer to have conversations out-of-character, at least at my end (e.g. I’d prefer to say "I greet the NPC" as opposed to saying "I turn to NPC and say ‘Hello!’"; I don’t have a strong preference as to whether GM replies "The NPC greets you back" or "The NPC says ‘Hello, traveller!’".). I like the fact that I can in principle have an elaborate back-story, but it’s important to me that I’m allowed to keep it to a bare minimum ("I was orphaned when I was a child, a wizard took me in as his apprentice, now I’m ready to go adventuring."). I get mildly uncomfortable when GM asks me to describe in detail what my outfit looks like or how I strike the finishing blow in a combat situation, especially when this has no effect on the mechanics of the game.

I’ve recently been trying to get back into RPGs, and in several unrelated instances in a row I encountered people who emphasise the role-playing/charismatic aspects of the game over the parts that I personally enjoy, and haven’t yet had much luck with finding like-minded people. Since my experience with RPGs is limited and mostly set far in the past, this makes me question: Is my approach to RPGs even valid?

(In some basic sense, I’m aware that my preferences are obviously valid, in the sense that anything is valid if everyone involved is having fun. An analogy that comes to mind: There is nothing wrong with liking fast-food more than French cuisine, just like there is nothing wrong with liking Indian cuisine more than French cuisine. However, if I expressed these two preferences, I would expect rather different responses. I’m basically asking whether in this analogy I’m a fast-food enthusiast or an Indian food enthusiast.)

Added in an attempt to clarify/narrow down the question: Does there exist a consensus in the RPG community that says that role-playing heavy RPG is, in some sense, superior to RPG that does not include much role-playing? Does there exist a consensus saying the opposite (i.e. that both approaches are valid)? Or is there no consensus at all?

P.S. Just to pre-empt the obvious comment: Yes, I am quite aware that "RP" in RPG stands for "role-playing" 😉

I want to make a video game based off of Dungeons and Dragons. How much am I legally permitted to use? [closed]

I want to make a video game based off of Dungeons and Dragons. It’ll be a top-down RPG with similar battle and class mechanics. But I’ve been planning out more, and seeing how much I’m copying. How much am I legally permitted to use? I am awayer of the SRD, but don’t understand it fully. Is it like a book of what I’m allowed to use?

How much damage is dealt/taken when that damage also reduces a creature to 0 hit points?

The particular case where this question came up was when a Hobgoblin Warlord attacked my ally, who had 7 hit points remaining. The attack’s damage was rolled for a total of 13, meaning they would fall unconscious. However, I am a Redemption Paladin which means I can Rebuke the Violent:

[…] Immediately after an attacker within 30 feet of you deals damage with an attack against a creature other than you, you can use your reaction to force the attacker to make a Wisdom saving throw. On a failed save, the attacker takes radiant damage equal to the damage it just dealt. […]

The question that arose was how to calculate "the damage it just dealt". Did the Hobgoblin Warlord deal 7 or 13 damage to my ally?


The only thing I found that somewhat answered this was the section on "Instant Death" which includes the following (emphasis mine):

[…] For example, a cleric with a maximum of 12 hit points currently has 6 hit points. If she takes 18 damage from an attack, she is reduced to 0 hit points, but 12 damage remains. Because the remaining damage equals her hit point maximum, the cleric dies.

This seems to state that the Cleric still takes 18 damage, even if her points drop to 0, but then it also states that 12 damage "remains". It’s not what I would call definitive and it doesn’t explicitly state that that is how damage works so I’m wondering if there’s anything that makes this even clearer.

How much XP does a monster created by Animate Objects add to an encounter?

I am making a boss fight. The boss can cast animate objects, a spell that can animate statues.

The spell gives information for, eg., 1 Large Construct:

Large – HP: 50, AC: 10, Attack: +6 to hit, 2d10 + 2 damage, Str: 14, Dex: 10

How much XP should I award for this Large construct?

I’m estimating CR1 (200XP) each.