Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,134
questions
0
votes
1
answer
312
views
Sort execution plan XML by estimated operator cost
I want to sort execution plan XML by Estimated Operator Cost, but the problem is:
SQL Server execution plan has the "Estimated Operator Cost" entry in UI:
...but that value is missing from ...
- 113
1
vote
1
answer
62
views
Query planner showing identical row counts for MCV and non-MCV values with a partial expression index of a JSONB field
I have the following table in PostgreSQL 13.3:
CREATE TABLE node (
id serial NOT NULL,
meta jsonb NOT NULL,
...
);
And there's index:
CREATE INDEX node_meta_group_owner_uuid ON node USING BTREE ...
- 121
1
vote
0
answers
72
views
Questions about execution plan
I'm reading "SQL Server Execution Plans, Third Edition, by Grant Fritchey".
It includes the following query and the execution plan (queried against the Adventure Works database).
SELECT soh....
0
votes
0
answers
75
views
SQL Server query tuning strategy
I'm new to query tuning and have been using Brent Ozar's tools to identify costly queries on the server.
One query that is executed numerous times per minute is triggering warnings for:
Forced ...
- 11
0
votes
2
answers
143
views
Need to maintain execution plans on SQL Server Upgrade 2017 to 2019
We are working on a performance issue and need to maintain the execution plans for troubleshooting. Need to maintain the execution plans and ensure the plan cache is not flushed out on upgrade to 2019....
0
votes
1
answer
27
views
In which case Optimizer rearranges the conditons in mysql?
recently I have added index "new_index" on this table.
CREATE TABLE `y` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`shipping_provider_id` int(10) unsigned DEFAULT NULL,
`...
- 97
2
votes
1
answer
552
views
Why does adding a WHERE clause break my working query, with error "Query processor could not produce a query plan because of the hints defined..."?
I have the following (stupidly-simplified) query that leverages two CTEs that refer to the same table and joins them to each other:
WITH CTE1 AS
(
SELECT dbo.RemoveNonNumericCharacters(PhoneNumber)...
- 31.7k
0
votes
1
answer
561
views
Performance issues after upgrading to Mysql 8.0.19 version
Few queries are performing very poorly after the upgrade. Here are the examples.
****Example 1**:**
SELECT
x.CI_TITLE 'ciTitle',
x.DEPARTMENT_MEANING 'departmentMeaning',
x....
3
votes
3
answers
1k
views
Reducing Temp Table Scans when joining two temp tables using OR conditions
I'm working on a complicated query. I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
- 115
0
votes
1
answer
157
views
SSMS - SQL Execution Plans
I am new to SQL execution plans and I'm trying to learn more about them.
I have a few questions.
How long does a SQL Execution Plan stay in memory? If I disconnect my connection, I assume it won't be ...
0
votes
1
answer
59
views
Do we have information about duration time of query in actual execution plan?
I am building application in C# which is parsing Actual Execution Plan. I need basic stats like Row count, CPU time, Page Reads etc. All above stats I found in XML of Actual Execution Plan. I can not ...
- 45
0
votes
1
answer
524
views
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.
SELECT * FROM MyTable
The execution plan gives the following info:
...
- 323
0
votes
1
answer
132
views
Conversion may affect "CardinalityEstimate" CONCAT NVARCHAR and UNIQUEIDENTIFIER
So I have a temporary table with only one column NVARCHAR(400).
In this temp table, I Insert values from various sources.
One of these is
INSERT INTO @permissions
SELECT Concat (N'resourcetype_', ...
- 103
1
vote
1
answer
152
views
Does each member of execution plan run in its entirety before the next execution starts?
I am looking at an execution plan and reading it from right to left.
Example:
1<-2<-3<-4
^
|_5
So 4 and 5 execute in parallel. When both are fully done, then 3 executes in its ...
- 3,205
0
votes
1
answer
72
views
Recalculate "Estimated I/O Cost" without exporting and importing database
I have the following query:
SELECT [t].[Key], SUM([t].[ValueA]), SUM([t].[ValueB])
FROM [MyTable] AS [t]
GROUP BY [t].[Key]
This table had an nvarchar column with value lengths of 2000 characters (I ...
- 1
0
votes
1
answer
633
views
Postgres Planner not using GIN index Occassionally
Platform: Heroku; PostgreSQL Version 13
I have a table around 46GB and there are two GIN indexes on name and email columns
I have a search query that is suppose to search in name and email columns ...
- 103
0
votes
1
answer
107
views
Is there any option to get actual execution plan as xml value to insert do different table?
Do you know If there is any option to get actual execution plan
and insert this plan to another table ? I would like to have table with history of actual plans.
- 45
1
vote
0
answers
55
views
What is the best way to have actual execution plan and IO stats as values to insert?
We have idea to create procedure which will test performance of query and insert specific information associated with executed query to table with test performance history ([dbo].[TestHistory]. We ...
- 45
-3
votes
1
answer
429
views
is HAVING clause before GROUP BY clause processed differently than after?
Is there any difference how query is processed (translated to extended relational algebra), if HAVING condition is written before GROUP BY (compared to after)?
In logical query plan it should be ...
0
votes
1
answer
200
views
SQL Server chooses very poor query plan
Recently, we've moved with our database from MySQL to Microsoft SQL Server (software is compatible with both engines). Overall performance after migration is quite good, but we've got problem with ...
- 1
2
votes
1
answer
50
views
Speed the response time of the Query
So I have this query that I am using on an API endpoint, the query takes between 3-4 seconds to run, returning a small OFFSET of 10 rows. Is there a way I can put indexes somewhere or improve on ...
- 21
5
votes
3
answers
463
views
Hovering over an Operator in Execution Plans: How to make the tooltip not disappear when moving the mouse
I want to share my screen and show an execution plan but when I hover my mouse over an operator and what to point to something with my mouse in the tooltip then it disappears. Is there a way to make a ...
- 765
5
votes
1
answer
568
views
Query plan not reused for identical queries sp_executesql vs adhoc
One of the queries in my application is timing out. With the SQL Server profiler I was able to get the exact faulty SQL code. It's an sp_executesql dynamic statement that runs in > 2 minutes in ...
- 150
1
vote
1
answer
351
views
PostgreSQL chooses a wrong order of Hash Join
EXPLAIN shows that PostgreSQL chooses a bad plan for hash join. it should have first iterate by tags_name_value_height2 in the table tags and use it to find corresponding transactions.id, but instead ...
- 735
0
votes
0
answers
20
views
Force PostgreSQL not to think beyond what EXPLAIN says [duplicate]
EXPLAIN gives a good strategy but nevertheless the query (and EXPLAIN ANLALYZE) runs extremely slowly.
Is there a simple way to force PostgreSQL always query by the same plan that EXPLAIN produces for ...
- 735
5
votes
1
answer
1k
views
Why does COALESCE prevent the use of an index on a varchar, but not on a text column?
Imagine a view which contains a COALESCE of two varchar columns from two different tables.
The underlying varchars are indexed in both tables.
In Postgres 11.6, filtering this view on the result of ...
- 223
1
vote
3
answers
774
views
SQL Server not creating parallel plan with OPTION(MAXDOP 20)
We have a UAT3 server hosted on VM with 8 sockets and 20 processer, we have similar UAT2 server hosted on the same VM with same configuration.
We are running a below query on both the server
select ...
1
vote
0
answers
48
views
PSQL 9.6 - OR query is much slower on joined table
We have a production POSTGRES 9.6 db with about 100M records (LOYALTY) and new table (has just 4 data as of now) "INFO"
Full index name on table loyalty
indexname "loyalty_activated_at&...
- 111
0
votes
1
answer
758
views
Why doesn't /*+ NO_INDEX*/ hint , affect the "execution plan"?
I have a sample table with below structure :
create table mi_dimcustomer
(customer_num number(10),
<other columns> <data types>)
And there is a unique index on column customer_num. I'...
- 1,402
1
vote
1
answer
562
views
DISTINCT forces re-sorting of data on PostgreSQL
I have a table recording transactions between accounts, looking like this:
txid varchar(64)
account varchar(64)
ts timestamp
amount numeric
(there are more fields but they are irrelevant for ...
- 121
1
vote
1
answer
412
views
Why is Index causing bad estimation and execution plan
SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) -
ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0)
FROM [Credit] M WITH(NOLOCK)
INNER JOIN [Transaction] T4 WITH(...
- 167
0
votes
1
answer
2k
views
Facing error "cannot fetch plan for SQL_ID" while trying to capture the execution plan
I'm trying to capture the execution plan of my query on PL/SQL developer as you can see below :
select *
from vmi_dimcustomer t1
inner join vmi_factcustomer t2
on t1.customer_num = t2....
- 1,402
1
vote
0
answers
129
views
Postgres failing to consistently use best partial indexes when multiple are present
In Postgres 10.17, I have two queries with different WHERE clauses and two partial indexes (idx_s_id and idx_w_id) optimized for each one respectively that should allow index only scans for both ...
- 11
1
vote
1
answer
760
views
The correct way for generating "Execution plan" for a PL-SQL statement in Oracle
I'm studying about Explain plan and Execution plan in Oracle and according to what I've read so far , The explained plan can differ from the actual plan used during statement execution. So I think ...
- 1,402
0
votes
0
answers
263
views
Slow query in SSD when enable_bitmapscan=on
Table INFO and index INFO:
CREATE TABLE PART (
P_PARTKEY SERIAL,
P_NAME VARCHAR(55),
P_MFGR CHAR(25),
P_BRAND CHAR(10),
P_TYPE VARCHAR(25),...
- 223
10
votes
1
answer
624
views
Why does a clustered Key Lookup on a primary key have a high estimated rows per execution?
We have a query with a Key Lookup which is estimating thousands of rows per execution. As I understand it, there should only ever be one row per execution. I understand statistics can be misleading ...
- 1,841
1
vote
2
answers
267
views
Can parameter sniffing happen even after disabling it?
I'm having a very strange issue with an EF linq query, the query runs a dynamic sql, something like this:
EXEC sp_executesql N'select 1 from X',N'@parameter1 int',@parameter1 = 123
The query was ...
- 11
2
votes
2
answers
195
views
What does Nested Loops join operator has to do with a Scalar?
As far as I understand the docs, the Nested Loops operator is a join operator, i.e., it requires two tables as input.
If that's correct, then why the Nested Loops operator is used with a Scalar input?
...
- 425
3
votes
1
answer
253
views
Understanding the relationship between a Table Spool and correlated subquery
I'm learning about execution plans, and have a question regarding the relationship between a Table Spool and correlated subquery (I'm following this tutorial, don't mind the typos).
I've created the ...
- 425
0
votes
0
answers
177
views
Get Join Keys dynamically from a table
SQL/Impala
I have a scenario where I need to join my input file with a mapping table which has map columns and derived columns. The join keys are dynamic and need to taken from a dynamic metadata ...
4
votes
1
answer
192
views
In PostgreSQL, what area in memory does HashSetOp use, work_mem or shared_buffer?
I want to know the difference between the hash table in a hash join and the hash table in HashSetOp Except.
Here is the PostgreSQL script I wrote to explain my question.
drop table if exists t1 ...
- 51
-1
votes
1
answer
53
views
Why does query rowcount change when I enable "include actual execution plan" and "include live query statistics" together?
I have a moderately complex SELECT query that utilises inline views and joins. I am intending to see if I can rewrite portions of the query to improve performance. I pasted the query into two SQL ...
- 1,525
8
votes
2
answers
582
views
SQL Server not Using Index
I am absolutely stumped as to why my query is not using what I think is a selective index.
My Model consists of Claims, Contacts, and Phone Numbers. Each Claim has 1 Contact and Each Contact has Many ...
- 140
1
vote
0
answers
165
views
Actual Number of Rows Significantly Lower than Estimated
My Problem
I am having an issue with a update query, where the execution plan has a bad cardinality estimate. It is estimated way more rows (14000%) than there should be when it does an Index seek on ...
- 45
5
votes
1
answer
2k
views
Msg 6355 "Conversion of one or more characters from XML to target collation impossible" when querying sys.dm_exec_query_plan
I like to find missing indexes on the go, looking at the execution plans!
It can potentially give me an indication where further to look at if I want to improve something that is currently running.
...
- 15.6k
0
votes
2
answers
373
views
Weird tables with numbers and underscores in PostgreSQL execution plans
I'm debugging performance problems with my DB and see some unusual entries in the DB log:
2021-04-21 01:07:33.857 UTC [14823] user@db LOG: duration: 933645.469 ms plan:
Query Text: select systemv0_....
- 163
4
votes
1
answer
179
views
Date index SEEK is chosen when using OPTION RECOMPILE but not with OPTION OPTIMIZE FOR
I have a table containing 10 years worth of 'package scans'. Somebody scans a package and it records the date and username. Let's pretend for now that retaining 10 years of data actually has a purpose....
- 215
0
votes
1
answer
128
views
Why is below 2 query generating different execution plan in MYSQL?
Only difference in the below query is the order by column. The resultset of both query is same.
However, the 2nd query execution plan shows 'Using temporary; Using filesort' for ordering where as for ...
- 1
1
vote
1
answer
345
views
How do I debug Postgres' join strategy?
In the process of optimizing a Postgres query, I ran into a situation where two equivalent, simple queries execute in very different amounts of time. While I can't provide a reproducible example for ...
- 13
1
vote
1
answer
574
views
Reason for failed query plan executions in SQL Server
We are using Azure SQL Database and recently we have experienced a severe performance degradation on our production database. When looking into Regressed queries in Query Store to find out the ...
- 113