Hot answers tagged

7

The sum of operator costs is more than 100% in execution plan is a known bug and is closed as by design ! Also, AaronBertrand filed a similar bug - SSMS : Execution plan sometimes exceeds 100% If you want to understand how plan costing works .. Paul White explains it at his best here. From the query processor team - What’s this cost? General guidelines ...


4

tl;dr You can't force MySQL to use a lot of cores. 40 million (4 crore) rows is a large, but not huge, database for MySQL. It is well within the capability of that software. You don't have to resort to desperate measures to get MySQL to work with that amount of data. You do have to index it correctly though. MySQL can use a lot of CPU cores as of recent ...


3

You could merge the subqueries using this model: SELECT bool_or(B.tags&1<>0) as "has_children_tag_1", bool_or(B.tags&2<>0) as "has_children_tag_2", bool_or(B.tags&4<>0) as "has_children_tag_3", bool_or(B.tags&8<>0) as "has_children_tag_4" FROM A LEFT JOIN B ON A.id = B.parent_id WHERE [conditions] ...


3

I would not worry about the 100% The big number are the big number A lot repeats so start optimizing just one This is just a subset of your query DECLARE @date SMALLDATETIME SELECT Reffd AS NAME , ( SELECT ( ( SELECT count(*) FROM [cal_reg].[dbo].[customer] WHERE upper(Reffd) = upper(main.reffd) ...


2

You are correct that PostgreSQL cannot currently use one index to provide selectivity and another index to provide order, in the way you want. Adding this feature has been discussed, but I don't think anyone considers it a high priority. Creating a multicolumn index will not help in any way. From my testing Postgres will not change its query plan at all ...


2

I'm not sure why you need to join all three tables every time. For your specific example, what about the following query: WITH rel AS ( SELECT prm.power_relation_id FROM power_relation_members prm JOIN power_lines pl ON prm.member_id = pl.id WHERE pl.geom = :BIND_VAR_HERE -- in this case, 'abc' GROUP BY prm.power_relation_id ) SELECT pl.id, pl....


2

I want to load my entire database into memory, but how can I do this? Before figuring out "how" to do something, it is often best to be clear on "why" that something should be done. So, why exactly do you want to load your entire database into memory? Memory is a finite resource, so it needs to be used efficiently / wisely. I have about 256 GB memory ...


2

Estimated sub tree cost is a SUM of cost of all operators preceding the one you are looking at. The easiest example is to look at the left-most icon - it will have an Estimated sub tree cost of whole query plan. There are a lot of sign in a query plan that show it needs optimization, however, I've seen a lot of situations when even perfect plans caused ...


1

My solution of this would be use relation tables instead of merging ids, use uniqueness for tables phone number, email, and website etc. and insert with IGNORE command like Insert ignore into emails values (5,a.a@a.com); If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a ...


1

I'd recommend looking into pre-splitting and/or using a hashed shard key to do the insertion and stick with dropping the collection (with remove you are basically doing a delete for every write, so it will always be slow). The hashed shard key is usually the easiest one to get started with. If you are looking to measure write throughput then each of those ...



Only top voted, non community-wiki answers of a minimum length are eligible