Why do two queries run faster than combined subquery?

I’m running postgres 11 on Azure.

If I run this query:

select min(pricedate) + interval '2 days' from pjm.rtprices 

It takes 0.153 sec and has the following explain:

    "Result  (cost=2.19..2.20 rows=1 width=8)"     "  InitPlan 1 (returns $  0)"     "    ->  Limit  (cost=0.56..2.19 rows=1 width=4)"     "          ->  Index Only Scan using rtprices_pkey on rtprices  (cost=0.56..103248504.36 rows=63502562 width=4)"     "                Index Cond: (pricedate IS NOT NULL)" 

If I run this query:

    select pricedate, hour, last_updated, count(1) as N      from pjm.rtprices     where pricedate<= '2020-11-06 00:00:00'     group by pricedate, hour, last_updated     order by pricedate desc, hour 

it takes 5sec with the following explain:

    "GroupAggregate  (cost=738576.82..747292.52 rows=374643 width=24)"     "  Group Key: pricedate, hour, last_updated"     "  ->  Sort  (cost=738576.82..739570.68 rows=397541 width=16)"     "        Sort Key: pricedate DESC, hour, last_updated"     "        ->  Index Scan using rtprices_pkey on rtprices  (cost=0.56..694807.03 rows=397541 width=16)"     "              Index Cond: (pricedate <= '2020-11-06'::date)" 

However when I run

    select pricedate, hour, last_updated, count(1) as N      from pjm.rtprices     where pricedate<= (select min(pricedate) + interval '2 days' from pjm.rtprices)     group by pricedate, hour, last_updated     order by pricedate desc, hour 

I get impatient after 2 minutes and cancel it.

The explain on the long running query is:

    "Finalize GroupAggregate  (cost=3791457.04..4757475.33 rows=3158115 width=24)"     "  Group Key: rtprices.pricedate, rtprices.hour, rtprices.last_updated"     "  InitPlan 2 (returns $  1)"     "    ->  Result  (cost=2.19..2.20 rows=1 width=8)"     "          InitPlan 1 (returns $  0)"     "            ->  Limit  (cost=0.56..2.19 rows=1 width=4)"     "                  ->  Index Only Scan using rtprices_pkey on rtprices rtprices_1  (cost=0.56..103683459.22 rows=63730959 width=4)"     "                        Index Cond: (pricedate IS NOT NULL)"     "  ->  Gather Merge  (cost=3791454.84..4662729.67 rows=6316230 width=24)"     "        Workers Planned: 2"     "        Params Evaluated: $  1"     "        ->  Partial GroupAggregate  (cost=3790454.81..3932679.99 rows=3158115 width=24)"     "              Group Key: rtprices.pricedate, rtprices.hour, rtprices.last_updated"     "              ->  Sort  (cost=3790454.81..3812583.62 rows=8851522 width=16)"     "                    Sort Key: rtprices.pricedate DESC, rtprices.hour, rtprices.last_updated"     "                    ->  Parallel Seq Scan on rtprices  (cost=0.00..2466553.08 rows=8851522 width=16)"     "                          Filter: (pricedate <= $  1)" 

Clearly, the last query has it doing a very expensive gathermerge so how to avoid that?

I did a different approach here:

    with lastday as (select distinct pricedate from pjm.rtprices order by pricedate limit 3)         select rtprices.pricedate, hour, last_updated - interval '4 hours' as last_updated, count(1) as N          from pjm.rtprices         right join lastday on rtprices.pricedate=lastday.pricedate         where rtprices.pricedate<= lastday.pricedate         group by rtprices.pricedate, hour, last_updated         order by rtprices.pricedate desc, hour 

which took just 2 sec with the following explain:

    "GroupAggregate  (cost=2277449.55..2285769.50 rows=332798 width=32)"     "  Group Key: rtprices.pricedate, rtprices.hour, rtprices.last_updated"     "  CTE lastday"     "    ->  Limit  (cost=0.56..1629038.11 rows=3 width=4)"     "          ->  Result  (cost=0.56..105887441.26 rows=195 width=4)"     "                ->  Unique  (cost=0.56..105887441.26 rows=195 width=4)"     "                      ->  Index Only Scan using rtprices_pkey on rtprices rtprices_1  (cost=0.56..105725202.47 rows=64895517 width=4)"     "  ->  Sort  (cost=648411.43..649243.43 rows=332798 width=16)"     "        Sort Key: rtprices.pricedate DESC, rtprices.hour, rtprices.last_updated"     "        ->  Nested Loop  (cost=0.56..612199.22 rows=332798 width=16)"     "              ->  CTE Scan on lastday  (cost=0.00..0.06 rows=3 width=4)"     "              ->  Index Scan using rtprices_pkey on rtprices  (cost=0.56..202957.06 rows=110933 width=16)"     "                    Index Cond: ((pricedate <= lastday.pricedate) AND (pricedate = lastday.pricedate))" 

This last one is all well and good but if my subquery wasn’t extensible to this hack, is there a better way for my subquery to have similar performance to the one at a time approach?

MariaDB (MySQL) slow query when primary key range combined with fulltext index

I’ve a table described below, with two columns – integer primary key and title text – currently holding circa 3 million records. As seen in the metadata below, there’s a BTREE index on integer primary key column, and FULLTEXT index on title column.

MariaDB [ttsdata]> describe records; +------------------+---------------------+------+-----+---------------------+-------------------------------+ | Field            | Type                | Null | Key | Default             | Extra                         | +------------------+---------------------+------+-----+---------------------+-------------------------------+ | id               | int(15) unsigned    | NO   | PRI | NULL                | auto_increment                | | title            | varchar(2000)       | YES  | MUL |                     |                               | +------------------+---------------------+------+-----+---------------------+-------------------------------+  MariaDB [ttsada]> show index from records; +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table   | Non_unique | Key_name                | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | records |          0 | PRIMARY                 |            1 | id               | A         |     2798873 |     NULL | NULL   |      | BTREE      |         |               | | records |          1 | title                   |            1 | title            | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 

I’d like to run the following query:

SELECT SQL_NO_CACHE * FROM records WHERE   id > 1928177 AND   MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 

This query takes more 5 seconds to execute. When I remove the the range part or the fulltext part, in both cases the query executes in circa 100 ms. Below is analysis of individual queries, the last one being the one I want to use.

I’m new to MySQL and DBA in general. I’ve posted EXPLAIN statements but I have no idea how to draw any conclusions from them. I assume that the query is slow because the range filtering happens on data set obtained from full text query.

So my question is: How can I make the query fast?

The 1928177 magic number is something that just happens to be needed.

Query 1

SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200; +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ | id   | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra                 | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ |    1 | SIMPLE      | records | range | PRIMARY       | PRIMARY | 4       | NULL | 227183 | Using index condition | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ 1 row in set (0.005 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200; ... 200 rows in set (0.108 sec) 

Time: 0.108 sec

Query 2

SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id   | select_type | table   | type     | possible_keys | key   | key_len | ref  | rows | Extra       | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ |    1 | SIMPLE      | records | fulltext | title         | title | 0       |      | 1    | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.007 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (0.138 sec) 

Time: 0.138 sec

Query 3

SELECT SQL_NO_CACHE * FROM records WHERE   id > 1928177 AND   MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id   | select_type | table   | type     | possible_keys | key   | key_len | ref  | rows | Extra       | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ |    1 | SIMPLE      | records | fulltext | PRIMARY,title | title | 0       |      | 1    | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.005 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (5.627 sec) 

Time: 5.627 sec

How are numerical bonuses combined?

Are there explicit rules for combining numerical bonuses in 5th edition, or is it just left implicitly to "common sense"? There are special cases (e.g. damage resistance and vulnerability, choice of base AC formulae, identical spell/item effects) which are well-defined, but I can’t find a general rule.

Additive and multiplicative bonuses

Example: Longstrider and Haste for a creature with 30′ base walking speed.

Longstrider: You touch a creature. The target’s speed increases by 10 feet until the spell ends.

Haste: Choose a willing creature that you can see within range. Until the spell ends, the target’s speed is doubled […]

Possible interpretations:

  • Always multiply first: (30′ × 2) + 10′ = 70′
  • Always add first: (30′ + 10′) × 2 = 80′
  • Do it in the order the spells were cast
  • Player chooses
  • GM chooses

Multiplicative bonuses with one another

Example: Haste and Boots of Speed for a creature with 30′ base walking speed.

Boots of Speed: While you wear these boots, you can use a bonus action and click the boots’ heels together. If you do, the boots double your walking speed […]

Possible interpretations:

  • Doubling the second time just idempotently repeats the fact that it’s been doubled: max(30′ × 2, 30′ × 2) = 60′
  • Doubling the second time means your speed is quadrupled: 30′ × 2 × 2 = 120′
  • Doubling the second time adds another 100% of your base movement, resulting in an overall tripling: 30′ × (100% + 100% + 100%) = 90′. This was the rule in 3rd edition.

Can Rainbow Servant be combined with Versatile Spellcaster and Spontaneous Divination to cast all Cleric spells spontaneously?

A ridiculous idea has occurred to me and I’m looking to verify if really works. Consider the following build:

  • Wizard 5/Rainbow Servant 10 (a class from Complete Divine)
  • At Wizard 5, take Spontaneous Divination from Complete Champion so that you qualify for Versatile Spellcaster from Races of the Dragon.
  • Take Versatile Spellcaster at some point.
  • At Rainbow Servant 10, you gain access to the entire Cleric spell list.
  • Put together, Versatile Spellcaster and Rainbow Servant 10 should let a Wizard cast spontaneously from the entire Cleric spell list.

This gives me two questions, the first as a focus and the second as a supplemental:

  1. Does this actually work? My first objection is that I’m not quite sure if the Wizard automatically knows all of the Cleric spells at Rainbow Servant 10. If you have to record them in your spellbook, you’ll soon run out of gold.
  2. If this does work, can the "powerful non-Diviner spellcaster class that can also cast spontaneously from the entire Cleric spell list" idea be done better? The term "Rainbow Warake" seems to be in my memory.

Can Living Gloves combined with Remove Curse be used to gain access to multiple artisan tool skills?

Living Gloves (p278 ERftLW) Says:

While attuned to these gloves, you gain one of the following proficiencies (your choice when you attune to the gloves):

  • Sleight of Hand
  • Thieves’ tools
  • One kind of artisan’s tools of your choice
  • One kind of musical instrument of your choice

Symbiotic Nature. The gloves can’t be removed from you while you’re attuned to them, and you can’t voluntarily end your attunement to them. If you’re targeted by a spell that ends a curse, your attunement to the gloves ends, and they can be removed.

Can remove curse (p271 PHB) be used to end the curse, then you can put the gloves back on to attune once again and choose a different proficiency?

Calculate combined standard deviation

If I have a data that I fit with NonlinearModelfit that fits a data based on two fitting parameters, c1 and c2.

When I used nlm["ParameterTable"] // Quiet I get the following table:

Image

If I have an equation such as:

eq = (2.303*((70 + 273.15)^2)*(c1/c2))/1000

Is there any code (as opposed to doing it manually) I can use to calculate the value of eq with the combined standard deviation based on the standard deviations of c1 and c2 from the table?

Thank you!

What DnD 5e build (with minimum number of combined levels over all classes) is needed to summon all summonable monsters?

What build would make if you just want to be able summon all monsters (of the summonable monsters) without a general purpose spell like wish or special equipment – how many levels of a multiclass do you need at minimum? Specify background. I believe a Wizard (Summoning school) can nearly summon all monster types with CR limits but for instance has to choose between demons and animals at level 4.

Unity: How to prevent animation from twitching when combined with Navmesh Agent

I am trying to make a third person shooter in Unity and trying to implement a zombie that follows the player and when close to player, attack animation is played. I am using the NavMesh Agent for that purpose. But I am facing the problem that once the zombie comes close and if the player moves back or side ways, the zombie twitches and the transition between the walking and attacking animation appears broken. Also, it slides on the floor around the player. Here is the video of it: Video Here is my code to control the zombie movement:

using System.Collections; using System.Collections.Generic; using UnityEngine; using UnityEngine.AI;  public class ZombieController : MonoBehaviour {     public Animator animator;     public NavMeshAgent agent;     public GameObject player;      public float turnSmoothing = 15f;               public float speedDampTime = 0.1f;     private const float stopDistanceProportion = 0.1f;      private int health = 100;      private PlayerContorller pc;      // Start is called before the first frame update     void Start()     {         agent.updateRotation = false;         agent.SetDestination(player.transform.position);         agent.isStopped = true;         pc = player.GetComponent<PlayerContorller>();     }       // Update is called once per frame     void Update()     {         agent.SetDestination(player.transform.position);          if (agent.pathPending)             return;         float speed = 0.0f;         if (!pc.Dead)         {             if (agent.remainingDistance <= agent.stoppingDistance * stopDistanceProportion)             {                 agent.isStopped = true;                 animator.SetBool("PlayerClose", true);                 Quaternion targetRotation = Quaternion.LookRotation(player.transform.position - transform.position);                 transform.rotation = Quaternion.Lerp(transform.rotation, targetRotation, turnSmoothing * Time.deltaTime);             }             else if (agent.remainingDistance < 70.0f)             {                 Quaternion targetRotation = Quaternion.LookRotation(player.transform.position - transform.position);                 transform.rotation = Quaternion.Lerp(transform.rotation, targetRotation, turnSmoothing * Time.deltaTime);                 float proportionalDistance = 1f - agent.remainingDistance / agent.stoppingDistance;                  speed = Mathf.Lerp(0.5f, 1f, proportionalDistance);                 agent.isStopped = false;                 animator.SetBool("PlayerClose", false);             }             else if (agent.remainingDistance < 100.0f)             {                 Quaternion targetRotation = Quaternion.LookRotation(player.transform.position - transform.position);                 float proportionalDistance = 1f - agent.remainingDistance / agent.stoppingDistance;                 transform.rotation = Quaternion.Lerp(transform.rotation, targetRotation, turnSmoothing * Time.deltaTime);                 speed = Mathf.Lerp(0f, 0.5f, proportionalDistance);                 agent.isStopped = false;                 animator.SetBool("PlayerClose", false);             }         }         else         {             agent.isStopped = true;             animator.SetBool("PlayerClose", false);             speed = 0f;         }         animator.SetFloat("Speed", speed);     }      public void Hit()     {         health -= 10;         if(health == 0)         {             animator.Play("Die");             StartCoroutine(Die());          }     }      IEnumerator Die()     {         yield return new WaitForSeconds(10);         Destroy(gameObject);     } } 

The Speed parameter controls Idle (when 0), walking (0-0.5), running(0.5-1) of the zombie, all three are part of a blend tree. And when the PlayerClose parameter is set to true, the animation transitions to the attack animation. How do I fix this? what changes to make in order to fix that?