New answers tagged optimization
0
Here's how to identify which 'bad' query to fix that will give you the most bang for the buck: The slowlog plus pt-query-digest. More details here: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
I rarely need to go down the list more than 3 items to make a dramatic improvement.
If you are weak on knowing what indexes to add, don'...
0
As the error says, that is limitation. However, you probably want try as follows:
SELECT Q.CUSTOMER,
COUNT (DISTINCT CASE WHEN O.REFDOCNUM is not null and D.REFDOCNUM is not null and D.REFDOCNUM is not null
THEN S.DOCNUM END
) AS HasInvoice,
COUNT (DISTINCT CASE WHEN O.REFDOCNUM is not null and D.REFDOCNUM ...
0
The queries are bad
Then they need fixing. You could add extra hardware but you will get linear improvements at best, fixing the queries could potentially give several orders of magnitude of improvement by removing huge scans and such. Though I'm only guessing here as we have no information about the actual queries.
and there are far too many of them to ...
1
You are not getting a full index scan. You are getting a regular (partial or parameterized) index scan, repeated 52,190 times. Each individual scan is very fast (I'm not sure how much it can be improved) but when you do it that many times it adds up.
It might be faster to do a hash join against that table rather than the nested loop index scan. It ...
1
The main problem are the bad estimates of the join cardinalities that lead PostgreSQL to use a nested loop join when a hash join would perform better.
There is one simple thing you can do to reduce the impact of the outermost nested loop join:
CREATE INDEX ON shipments_address (company_view_id) WHERE is_shipper;
That should cut the execution time roughly ...
2
You can do it in a single statement.
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM
Errn
WHERE
erid IN (
select
distinct m.ID
from
Member m
left join
(select
*, row_number() over (PARTITION BY rid ...
1
This query, as written, must compare every pair of rows, and the cost estimate is reasonably accurate.
On a supported version of SQL Server you might get a parallel plan, or there is an undocumented query hint that will force a parallel plan.
select t1.itemid, t2.itemid
from #temp T1
inner join #temp as t2
on t1.itemid like '%' + t2.itemid + '%'
and ...
3
Currently i have classID as an included column in an index idx, so i
think that the idx will be used when classID is in select statement.
No, not really. The index that is selected in the query plan depends on the query optimiser deciding which index best satisfies the query requirements. If your index key columns match, or are very similar to, your query ...
0
Almost always index merge intersect can be improved on by using a composite index. Adding an index can be done without dealing with the ORM.
0
Few possibilities:
Rows Removed by Filter: 88963
This is filtering large number of rows. You may want to ensure that this filtering happens very early in the query. You can trick the planner by using OFFSET 0 in your subqueries.
AND p.type::TEXT = 'product'::TEXT
LEFT JOINed table having WHERE condition may not result correctly, unless you are pretty ...
0
There are two low level steps which take up much of your run time.
-> Function Scan on cogs (cost=0.25..10.25 rows=1000 width=12) (actual time=9440.934..9443.004 rows=49301 loops=1)
We have no visibility into what that function does. You will have to look into it yourself, or show us the source code
-> Bitmap Heap Scan on account_move_line aml (...
0
Please check below section has been properly written.
In short you may have to check the joins are proper as I do not see any of the tables are joined for the selected table, the below section.
If unexpected cartesian joins visible then you may have to consider introducing proper joins.
SELECT sale_order_report_ept.product_id,
...
1
Your query eliminates user_id from the passed array. Typically, you'd want to show those with a count of 0.
LEFT JOIN LATERAL .. ON true, followed by COALESCE takes care of that. If you actually want those eliminated switch to CROSS JOIN and drop COALESCE, same performance:
SELECT t.user_id, COALESCE(unread_count, 0) AS unread_count
FROM unnest('{200 user ...
2
Your explain plan is a bit confusing, as it looks like the index scan is getting the data for all 200 user_ids at once, but then doing that 200 times. But doing the experiment, that is not what it is doing, each iteration of the nested loop is getting the data for one user_id from that list, not the whole list. So it is just a presentation issue in the ...
0
SQL Server I/O and CPU Cost is an estimate in seconds from the year 2000.
SQL Server estimates that each I/O will take 3.125 ms (i.e. 1/320 s, because of the assumption that the disk can perform 320 I/O operations per second. 1/320 = 0.003125). Each I/O is fetching an 8 KB page from the disk.
This is one of the magic numbers inside SQL Server. Others are:
...
1
Because a PL/pgSQL is a black box to the planner it has no idea of how many rows the function will return.
The best solution is to rewrite the function a plain SQL function which could be inlined by the planner:
Assuming you never pass values that can't be converted to an integer, the following function is a much better choice than your complicated and ...
1
If I understand correctly, you are getting count to satisfy your Pagination requirements. Generally, two separate queries are executed; one to get the 10 records, and another to get the total count.
The problem in your approach is that you are calculating the Count in a subquery inside the SELECT clause. For every row returned by the SELECT clause, is going ...
1
You have 6 many-to-many tables linking one table to 6 other tables??
If any of them is just one-to-many, toss the linking table.
Normalization is good; over-normalization is bad.
Why have a table LOCATION if the only thing it contains is a column LOCATION?
Many-to-many tables are somewhat inefficient. (See this for a partial solution: http://mysql.rjweb....
0
Add composite keys:
games: INDEX(home, active, id)
games: INDEX(guest, active, id)
gamegroup: INDEX(team, game)
Shrink the data:
INT always takes 4 bytes. TINYINT UNSIGNED takes 1 bytes and is probably more than adequate for period, active etc.
If gamegroup is a many-to-many mapping between teams and games, you probably don't need id. Instead:
...
0
#innodb_flush_log_at_trx_commit = 0 -- use 2 for speed; 1 for safety
max_connections = 1500 -- rather high
query_cache_size =300M -- much too high; use 50M
tmp_table_size = 1500M -- much too high; use 200M
max_heap_table_size = 1500M -- much too high; use 200M
Generally, the Query cache is useless for Production machines.
Created tmp disk ...
1
There is no way to do that with COPY alone. You either have to pre-process or post-process the data.
Perhaps UNLOGGED tables can make this a little bit faster:
/* no WAL logging for better performance */
CREATE UNLOGGED TABLE test1 (v1 double precision);
COPY test1 FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a ...
0
I noticed the plans 1-3 use Full Table Scan. It's probably not a problem with small tables (as far as I understand messages_thread has only 40 records). So if you expect the tables to grow and have variety of mt.creator values, then you would probably want to access mt by index and not scanning it.
I think the query optimizer would switch in this mode ...
0
For what it's worth, the real table that this question applies to is the asset table in Maximo 7.6.1.1.
The asset table in Maximo does not have a unique index on the assetnum column (nor a primary key on assetnum).
However, it does have a unique index on the assetnum and the siteid columns.
While the index isn't exactly what I want, perhaps it will help ...
0
MySQL 8.0.14 has introduced Lateral Derived Table. In your second query, you are computing max() value for all the threadId, whether you need it or not during the JOIN. You can avoid this materialization of m2 (a costly process, which is basically temp table creation, either in-memory, or disk (if too big)), by using the following instead:
SELECT max(m2.id),...
1
I don't know how useful this will prove to be as I've not tested it out thoroughly, but you should be able to utilize Statistics Density Vector values to see what columns (and column combinations) will provide a higher likelihood of unique values. The script listed below basically pulls all the Density Vector values for all stats on a given table. This is ...
2
This is not specific to MySQL, it is about B-tree indexes in general.
Leaving aside the implementation details, you can imagine a B-tree index as a sorted list of the indexed columns with a pointer to the table.
So if you imagine a two-column index on (num1, num2), it would look somewhat like this:
num1 | num2 | pointer
--------+--------+---------
...
3
Think about it. Such a join without further information can not be skipped. Even if you do not select columns from maximo_assets, the join to it may increase the number of rows returned, because for 1 ID in gis_sidewalks, you can have multiple rows in maximo_assets with the same ID.
If the IDs are unique (or PK), and you make this known to the database by ...
0
It looks like using a subquery instead of a join might do the trick:
create or replace view gis_sidewalks_vw_2 as (
select
s.id,
s.last_edited_date as gis_last_edited_date,
case when
s.last_edited_date > (select lastsyncdate from maximo_assets a where s.id = a.id) then 1
end as sync_needed
from
gis_sidewalks s
);
select
...
2
There is nothing obviously wrong with the plan, so there is no obvious large optimization to be done. You are fundamentally doing a lot of work, and it takes a lot of time.
I would probably start by taking a step back and looking at your business case. Why do you need exactly this output? Could you perhaps "need" something easier to optimize? Like, ...
2
I think this will do what you are asking.
SELECT
ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
ON am1.ID = ivr.ID
AND am1.ADDR_TYPE IN ('WORK','HOME')
WHERE (PHONE1 = '0123456789'
OR PHONE2 = '0123456789'
OR PHONE3 = '0123456789'
OR am1.PHONE = '0123456789'
)
That said, there are a few things you could do differently.
This might be ...
1
Refactoring the table would be easier. Phone numbers should be in a separate, related table, not in the addresses table (as shown by the need for multiple phone fields), something like this (simplified as an example - flags for FAX machines, cellphone, Peferred #, always an answering machine, no calls after 6pm, etc may be needed as well):
CREATE TABLE ...
2
The below creates 300000 dummy orders with 3 random "phone" numbers and then finds the matches, should get you started.
use tempdb
GO
drop table if EXISTS Orders
GO
create table Orders (
OrderID int primary key,
UserName varchar(50),
PhoneNumber1 varchar(50),
PhoneNumber2 varchar(50),
PhoneNumber3 varchar(50)
)
-- generate 300000 ...
1
If you need to have multiple values (like one ART having several AUTHORs), you need this kind of linking. If that is not needed, just have all the ids in ART table.
I'd recommend you move to ART all attribute ids where a single value is enough and leave only the multivalued ones using the more complex kind of linking via external link table.
0
Create calculated field
HOURpkey1 TINYINT AS (HOUR(pkey1))
Make it either STORED or VIRTUAL - think by yourself what is better for you.
Create index
KEY idx_pkey2_pkey3_HOURpkey1 ON table (pkey2,pkey3,HOURpkey1)
Any fields order is possible and safe for the query in question, think by yourself what is the best.
If the amount of records in output is ...
1
Your query is not sargeable. Using Hour() function on the first column (pk1) of the composite primary key is inhibiting the usage of the Primary Key for index lookup; and thus (in absence of any other proper index), it is most likely doing full table scan. The general rule of thumb to follow while defining a working index is:
First priority should be given ...
1
Very often joining to a VALUES clause improves performance in these cases:
SELECT *
FROM "TranslationText"
JOIN "Phrase" ON "TranslationText"."phrase" = "Phrase"."id"
[.. your other joins ...]
JOIN (
values
($1, $2, $3, $4),
($5, $6, $7, $8),
....
) as v(org_text, org_lang, new_lang, description)
on v.org_text = "...
Top 50 recent answers are included
