How can I reduce Estimated I/O and subtree cost in SQL Server Execution plan?

I have a table with more than 1 million rows. When I execute a simple SELECT query it takes about 17 seconds to retrieve the data.


The execution plan gives the following info:

enter image description here

Furthermore, I have 5 non-clustered indexes and one clustered index.

Any suggestion would be appreciated.

Reduce not yielding accurate results?

I am trying to use Reduce to determine the range of values of parameter g for which NCritical[g] > N1[g].

a = 0.03; c = 0.5; b = d = 1; K = HMax = 1000;   NCritical[g_] := (   b^2 (-1 + c g)^2 Sqrt[(b - b c g)/(1 + c - c g)] K^2)/(   d^2 (-1 + c (-1 + g))^2);  Equilibria1[g_] := Module[{h, x, y},        Simplify[     Normal[Solve[       y*g*(1 - c*g)*(1 - h/HMax)*b - a*h == 0 &&         y*(1 - c*g)*h/HMax*b + y*g*(1 - c*g)*(1 - h/HMax)*b -           y*d*(x + y)/K == 0 && x*h/HMax*b - x*d*(x + y)/K == 0, {h, y,         x}, PositiveReals]]]];  H1[g_] := Equilibria1[g][[All, 1]][[1]][[2]];  Y1[g_] := Equilibria1[g][[All, 2]][[1]][[2]];  X1[g_] := Equilibria1[g][[All, 3]][[1]][[2]];  Range1 = Once[   N[Reduce[Y1[g] > NCritical[g] && 0 <= g <= 1, PositiveReals]]] 

The code above gives the following range:

0. < g < 0.000165352 

However, we can see this is wrong by testing whether the following statement is true:

 g=1;   Y1[g]>(b^2 (-1 + c g)^2 Sqrt[(b - b c g)/(  1 + c - c g)] K^2)/(d^2 (-1 + c (-1 + g))^2) 

This gives an answer of True.

Is there any way around this problem?

How can i reduce the cost in Bitmap Heap Scan, Filter and Heap Block?

Looks like the query taking time in the second last step in which it mapping index to actually rows so can I optimize this query in any terms, I m not sure if something wrong with the index? and what is Heap Block means is this related to work_mem.

Postgres Configuration: RAM 16GB vCpu 4


CREATE TABLE main.user_resources (     id uuid NOT NULL DEFAULT (md5(((random())::text || (clock_timestamp())::text)))::uuid,     user_tenant_id uuid NOT NULL,     resource_name character varying(50) COLLATE pg_catalog."default" NOT NULL,     resource_value character varying(50) COLLATE pg_catalog."default" NOT NULL,     allowed boolean NOT NULL,     created_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,     modified_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,     created_by uuid,     modified_by uuid,     deleted boolean NOT NULL DEFAULT false,     deleted_on timestamp with time zone,     deleted_by uuid,     CONSTRAINT pk_user_resources_id PRIMARY KEY (id) ) 


SELECT "resource_name","resource_value","allowed" FROM "main"."user_resources" WHERE (("user_tenant_id"=abc') AND ((("resource_value" IN ('efg')) OR ("resource_name" IN ('BO','UB')))) AND ("deleted"=false)) ORDER BY "id";  


CREATE INDEX idx_user_resource_user_tenant_id_and_not_deleted     ON main.user_resources USING btree     (user_tenant_id ASC NULLS LAST)     TABLESPACE pg_default     WHERE deleted = false; -- Index: idx_user_resources_resource_name  -- DROP INDEX main.idx_user_resources_resource_name;  CREATE INDEX idx_user_resources_resource_name     ON main.user_resources USING btree     (resource_name COLLATE pg_catalog."default" ASC NULLS LAST)     TABLESPACE pg_default; -- Index: idx_user_resources_resource_value  -- DROP INDEX main.idx_user_resources_resource_value;  CREATE INDEX idx_user_resources_resource_value     ON main.user_resources USING btree     (resource_value COLLATE pg_catalog."default" ASC NULLS LAST)     TABLESPACE pg_default; -- Index: idx_user_resources_user_tenant_id  -- DROP INDEX main.idx_user_resources_user_tenant_id;  CREATE INDEX idx_user_resources_user_tenant_id     ON main.user_resources USING btree     (user_tenant_id ASC NULLS LAST)     TABLESPACE pg_default;  


Sort  (cost=3547.20..3556.41 rows=3686 width=64) (actual time=7.080..7.438 rows=4562 loops=1)    Sort Key: id    Sort Method: quicksort  Memory: 834kB    ->  Bitmap Heap Scan on user_resources  (cost=281.12..3328.84 rows=3686 width=64) (actual time=1.070..5.325 rows=4562 loops=1)          Recheck Cond: (((user_tenant_id = 'abc'::uuid) AND ((resource_value)::text = 'efg'::text)) OR ((resource_name)::text = ANY ('{BO,UB}'::text[])))          Filter: ((NOT deleted) AND (user_tenant_id = 'abc'::uuid))          Rows Removed by Filter: 6912          Heap Blocks: exact=2177          ->  BitmapOr  (cost=281.12..281.12 rows=11470 width=0) (actual time=0.834..0.835 rows=0 loops=1)                ->  Bitmap Index Scan on idx_user_resource_mlt  (cost=0.00..4.43 rows=1 width=0) (actual time=0.021..0.021 rows=3 loops=1)                      Index Cond: ((user_tenant_id = 'abc'::uuid) AND ((resource_value)::text = 'efg'::text))                ->  Bitmap Index Scan on idx_user_resources_resource_name  (cost=0.00..274.84 rows=11469 width=0) (actual time=0.812..0.812 rows=11735 loops=1)                      Index Cond: ((resource_name)::text = ANY ('{BO,UB}'::text[]))  Planning Time: 0.214 ms  Execution Time: 7.932 ms (15 rows) 

Does reducing the Purple Worm’s HP to 184 really reduce its challenge rating to 13?

On page 113 of Princes of the Apocalypse the text reads in part:

As the tremor ends, a young purple worm with 184 hit points (challenge rating 13) burrows…

No other changes to the Purple Worm’s stats are given and the monster does not appear in the Monsters section at the end of the book. This monster also shows up on DnD Beyond’s encounter builder as a CR 13 monster as "Young Purple Worm", in contrast to the CR 15 "Purple Worm".

Is taking away 63 hit points with no other changes to this monster enough to drop it two challenge ratings? I’d like to challenge, but not kill, my party.

House Rule – Upcasting Enlarge / Reduce to extend duration

The party is deep into my 5e-updated classic Greyhawk Giants series.

The hill giants and their orc and goblin minions have attacked the Sterish city of Headwater and have taken about a quarter of the city. The party is about to embark on a mission to go behind enemy lines, kidnap and assassinate a stone giant who is critical to the hill giants’ city / siege offensive as being their only trained engineer. While the party’s patron recognizes that the stone giant needs to die, she also recognizes that at present the Stone Giant Thane has not joined the giant alliance and wants to keep it that way. Thus, she is requesting that they carry the body of the slain giant honorably back to his Thane in an effort to preserve a fragile peace between the humans and the stone giants. I would like the party to be able to accept this mission, without it becoming either a logistical challenge involving wagons and draft animals, or without loaning them a portable hole.

Instead, I would like to provide them with a version of the Enlarge / Reduce spell which is in all aspects identical to the original except that it can be upcast to extend the duration.

I figure the giant is 17 feet high and 1000 pounds; after reduce it would be 8.5 feet and a manageable 125 pounds.

For this version of Enlarge / Reduce I am proposing that:

When cast at 3rd level against living creatures, it lasts 10 minutes

When cast at 3rd level against objects, it lasts 1 hour

When cast at 4th level against living creatures, it lasts 1 hour (similar to polymorph)

When cast at 4th level against objects, it lasts 8 hours

When cast at 5th level against living creatures, it lasts 8 hours (much less than geas)

When cast at 5th level against objects, it lasts 24 hours

The party Wizard is currently 8th level and getting close to 9th. Requiring her to use both her fourth level slots and maintain concentration for the duration of travel every day in order to move swiftly and stealthily into the mountains with the body is just the right level of challenge for the group.

My only concern is that allowing this version of the spell to the party wizard permanently will have some unforeseen interaction with some other spell, ability, or item that I will later regret. This question, for example, asks about upcasting enlarge to permit two changes in creature size, and answers identify the interaction with levitation and grappling being problematic. I am interested in a similar troubleshooting review.

How can I reduce time and cost to create magic items?

According to the rules, creating magic items (like Wondrous items) takes a day per 1000gp item price and cost half the price.

In the Epic level handbook there is an epic feat called Efficient Item Creation that allows you to create 10000gp per day.

Are there other ways to reduce time taken, money cost or xp cost when creating magic items? I don’t care whether they are epic or not, and a bit of cheese can be allowed.

Does Heavy Armor reduce your speed?

I noticed in the PHB, page 20, under Speed for Dwarves:

Speed. Your base walking speed is 25 feet. Your speed is not reduced by wearing heavy armor.

However, I cannot find anywhere in PHB that mentions that wearing Heavy Armor imposes any speed reduction.

Does anyone know if I’ve missed something, or if this is actually some inconsistency in the book and Heavy Armor in fact doesn’t impose any speed reduction?