Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,058
questions
-2
votes
0
answers
31
views
warning in execute plan [closed]
Who has an idea please for this type of warning as is corrected ?
4
votes
1
answer
96
views
Execution plan with a lot of ComputeScalar operations
I executed the same query with and without OPTION (RECOMPILE). When I compare the 2 plans, visually the one main difference I see is that the plan without option recompile shows a lot of ComputeScalar ...
- 368
1
vote
1
answer
31
views
Why is the optimizer not using the clustering index on my table?
I have this table
Table "public.lineitem"
Column | Type | Collation | Nullable | Default
-----------------+---------------+-----------+----------+----...
- 95
-3
votes
0
answers
35
views
Page Life Expectancy is dropping frequently
Server Spec: 64 GB Ram (R5.2xLarge)
Support Team reported there is memory pressure on the SQL Server.
Allocated 52 GB memory as min and max for SQL Instance
Observed page life expectancy counter value ...
- 1
1
vote
3
answers
51
views
Optimizing a PostgreSQL query with many small lookups
I have a database about books and book sales.
The tables look like this:
CREATE TABLE purchase (
person_id integer,
book_id integer,
CONSTRAINT purchase_pk PRIMARY KEY (person_id, book_id)
...
2
votes
2
answers
294
views
how to find out the `Key Lookups` in the current query plan?
I am putting a query to list the key lookups that are present in the current requests being executed, I basically would like to tackle then and see if I could eliminate these key lookup from the ...
- 14.8k
0
votes
0
answers
39
views
How can i store execution plans for certain stored procs
Unfortunately we cannot use query store as we are on SQL 2014 and need to find a way to store execution plans of certain stored procedures.
I saw XE like sp statement completed but unfortunately it ...
- 703
1
vote
1
answer
45
views
Optimizing simple query in PostgreSQL (two table join and sorting)
I have a simple query that should return data about booking, but it misbehaves and sometimes does not return data within 1 minute.
So I have main booking table:
table booking_data
- bid bigint primary ...
- 113
0
votes
1
answer
46
views
Forcing a sequential scan over the index scan
I have this query
select
s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment
from
part,supplier,partsupp,nation,region
where
p_partkey=ps_partkey
and
s_suppkey=...
- 95
1
vote
2
answers
43
views
How to make Postgres use the correct index here?
Here's a test setup:
CREATE TABLE t (
filename int,
cropped bool not null default false,
resized bool not null default false,
create_date date not null default '1970-01-01'
);
INSERT ...
- 415
1
vote
1
answer
31
views
Azure MI - clustered and nonclustered indexes with the same columns - different execution plans
I have a problem to understand why I am getting 2 different execution plans based on whether clustered index is involved or not. It's happening in Azure managed instance, I have no possibility to test ...
- 105
2
votes
3
answers
144
views
Efficient unique Inner Join
Context
I have a query that is something like where I want to query all of the days that each product was on sale at at least 1 store. This query runs as part of a larger query and runs whenever a ...
- 23
0
votes
0
answers
31
views
Unique query to multiple tables with array. Is it possible?
I'm using PostgreSQL 14.
Today, I have the following tables, with more than 4 million lines each table:
tbx_00, tbx_01, tbx_02, tbx_03, tbx_04
and
tby_00, tby_01, tby_02, tby_03, tby_04
About the ...
- 1
1
vote
0
answers
25
views
Query Planner has bad row estimate for second-degree joined link table and uses inefficient nested join
Note: This is a toy database example I came up with and a simplification of a real design, so please disregard from commenting about the way the database is structured.
Each Group has precisely one ...
- 197
3
votes
0
answers
127
views
Why adding select into table is changing execution plan?
I am having following query:
DECLARE @VersionsIds TABLE(VersionId INT)
INSERT @VersionsIds(VersionId) VALUES (520)
SELECT
vEps.MasterPolicyId
-- INTO #Temp
FROM [Events].[vwEventMasterPolicy_Search] ...
5
votes
2
answers
244
views
dateadd in the where clause - how can I avoid a full scan in this particular situation?
I have the following query:
SELECT u.userId,
app.applicationId
FROM app.application AS app
INNER JOIN app.applicant AS ap
ON ...
- 14.8k
2
votes
2
answers
74
views
Dealing with suboptimal postgres query plans related to nested loops
I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13.
Some of the queries are very slow because they use nested-loops but the query planner ...
- 121
5
votes
1
answer
206
views
Why are the majority of cached plans missing from sys.dm_exec_query_stats?
I'm trying to understand some execution plan caching metadata on a SQL Server 2016 SP3 system, and I can't reconcile what I'm seeing with the docs.
The docs for sys.dm_exec_cached_plans say it ...
- 27.1k
-2
votes
1
answer
51
views
when update large table it take too much time to update
I work on sql server 2019
when update table Z2DataCore.parts.SourcingNotMappedParts i found slow and long time when update
estimated execution plan
actual execution plan
Update statement takes 26.5 ...
- 155
0
votes
0
answers
41
views
when check long wrong query i found status suspend with wait log buffer?
I work on sql server 2019 i run my stored procedure on sql server .
it take may be 5 hours so i try to trace why it take too much time or too long time .
so I make this query to trace issue on my ...
- 155
2
votes
2
answers
43
views
when make select to top 5 of rows from table it take too much time?
I work on sql server 2019 i face issue when make select top 5 rows it take too much time .
Number of Rows on Table Z2DataCore.parts.SourcingNotMappedParts 70 milion rows .
when run statment select ...
- 155
8
votes
1
answer
664
views
Why does OPTION RECOMPILE cause a predicate pushdown?
I have a SQL query that is made out of a nested mess of views and table-valued functions going on for at least 4 levels deep (I didn't have the time or patience to go through it all, it's hundreds of ...
- 943
3
votes
1
answer
236
views
Very slow update statement taking up to 11min to update 20 or 0 rows even with indexes
When I update 20 rows or no rows, it takes 11 minutes.
I mean 20 rows or no rows by different on m.MaximumReflowTemperatureID <> r.z2valueid between two tables.
Why is my update so slow, even ...
- 155
0
votes
0
answers
37
views
Matching against a ruleset, and visualisation by crosstab
This question is "inspired" by another question at https://stackoverflow.com/questions/66165290 about "matching on a NULL". To summarise, they wanted to provide a partial row, and ...
- 11
2
votes
0
answers
76
views
Huge Difference in IO and CPU for same query
I saw that same query got more than two execution plans. Query is included inside procedure which runs once a day every day. All tables in query got from 2000 to 50000 additional records every day. I ...
- 307
4
votes
1
answer
82
views
Columnstore index predicate is not used in index scan
We are using a clustered columnstore index table with ~50M records and experiencing big performance degradation when running on GCP cloud sql vs. running locally using the same db schema and data (...
- 143
2
votes
4
answers
203
views
NONCLUSTERED index seek on mslid is slow
I have an issue in SQL Server where a NONCLUSTERED index seek is performing poorly.
Below is the actual execution plan
https://www.brentozar.com/pastetheplan/?id=Sk3-4JGAK
How can I enhance ...
- 155
6
votes
1
answer
252
views
Bitmap Creation in Execution Plan Causes bad Estimate on Clustered Index Scan
Given the following simple query on the StackOverflow2010 database:
SELECT u.DisplayName,
u.Reputation
FROM Users u
JOIN Posts p
ON u.id = p.OwnerUserId
WHERE u....
- 1,450
1
vote
0
answers
40
views
Which is the better way to select the last entry row from a table in PostgreSQL?
I need to test an attribute from the last entry row from a table to trigger a trigger function on PostgreSQL 9.6. Just below, there is a little piece of my table.
It's doesn't have a timestamp column, ...
0
votes
1
answer
36
views
Query plan XML: one stmt's DegreeOfParallelism is 1 for any maxdop other than 1 (maxdop 1 leads to DegreeOfParallelism = 0 with reason MaxDopSetToOne)
I have a procedure with two SELECTs. One statement respects the server's/database's/query hint's maxdop setting and uses parallelism, the other is being difficult and never going parallel.
The 8 core ...
- 41
0
votes
1
answer
21
views
what does the number in the "A_ROWS" column of the execution plan shows for operation "Index scan"?
I have a query as you can see below :
select /*+gather_plan_statistics*/
*
from mi_dimcustomer t
where t.CUSTOMER_NUM = 321937
There's a Unique Index (IDX1_DIMCUSTOMER) on Customer_Num column of ...
- 1,294
2
votes
1
answer
107
views
Postgresql 10: Bitmap Heap Scan with exact heap block
I have the following query:
select ro.*
from courier c1
join courier c2 on c2.real_physical_courier_1c_id = c1.real_physical_courier_1c_id
join restaurant_order ro on ro.courier_id = c2.id
...
- 555
-2
votes
3
answers
90
views
Query performance on different servers
Same query with select and where clause is less than 2 seconds on one server and same query takes more than 10 minutes on another server.
Both servers have same max dop and parallelism settings. Both ...
- 55
0
votes
1
answer
49
views
How can I prompt SQL server to use more suitable index in query?
I have a query that somtimes runs longer than the application calling it allows. I've traced it down a clustered index seek on rather larger table. I've created a new index, that better supports this ...
1
vote
1
answer
68
views
Three "Nested loop join" in the Execution plan despite using two inner joins in the main query
I have a query as you can see below :
with cte as
(select customer_num
from vmi_segment_customer_relation
where effective_date = to_date('12/30/2021', 'mm/dd/yyyy')
and segment_id = ...
- 1,294
1
vote
1
answer
30
views
Does `USING (column)` provide any memory advantage over an an explicit JOIN condition with ON?
Are there any performance differences between
JOIN bar USING (foo_id)
JOIN bar ON (foo.foo_id = bar.foo_id)
I ask this because at least in theory there is more data to project (a duplicate column). ...
- 55.7k
0
votes
1
answer
89
views
Getting warnings while generating query Execution plan in Oracle , PLSQL developer
These are the steps I follow to generate a real Execution Plan in PLSQL developer :
1)Using this hint in my main query /+gather_plan_statistics/
select /*+gather_plan_statistics*/ from mi_factcustomer
...
- 1,294
1
vote
2
answers
183
views
Postgres slow exists subquery
I have a queue system that has two tables, jobs and jobs_queues.
jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are ...
1
vote
1
answer
153
views
How to utilize partition pruning with subqueries or joins?
I have a partition table...
CREATE TABLE erco.rtprices
(
scedtime timestamp with time zone NOT NULL,
node_id integer NOT NULL,
lmp numeric(12,6),
CONSTRAINT rtprices_pkey PRIMARY KEY (...
- 601
3
votes
5
answers
148
views
Actual and Estimated rows differ greatly
The full Actual plan is here.
Prior to executing the plan (because I'm debugging a poorly functioning plan) I have this block of variable assignments:
DECLARE @Days INT = 180
DECLARE @DateRangeFrom ...
- 173
2
votes
2
answers
82
views
Why there's unnecessary 2 index join of the same table?
I have a table named [Job] with 2 indexes as below. (Due to confidentiality, I can't post the whole execution plan with the exact name, sorry about that.)
[IX_Job_WillTest] : (ColA ASC, ColB ASC) ...
- 21
0
votes
1
answer
76
views
Query optimization: LEFT JOIN makes the query incredibly slow
I have the following query with explain analyze and it's incredibly slow because of the LEFT JOIN.
EXPLAIN ANALYZE SELECT DISTINCT ON (project_name, release_version, project_id, release_id) vbc.id
...
- 57
2
votes
1
answer
89
views
WHERE IN performance hit
I got send a SQL query with the question why it is running poorly. They claim the query does not have any performance issues with their other clients who have a similar dataset and blame the ...
- 33
1
vote
1
answer
31
views
Postgresql (11.9) LIKE query not using text_ops index
I've seen related questions but I think this should be working and isn't. I have a text_ops function based index on column to do LIKE searches, but it only picks up the index with an equals:
CREATE ...
- 111
0
votes
1
answer
49
views
Why does an integer comparison result in no temporary index in MariaDB?
I am running a query on a table (transaction_items, c. 12 million rows) that has a many-to-many relationship with another table, and I wish to group transaction_items by this relationship.
To do this, ...
- 101
3
votes
2
answers
324
views
Columnstore: terrible execution plan - filter instead of seek
See the execution plan on https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise)
I have a datawarehouse table peak_reporting_data which tracks the activity per day and hour and ...
- 827
0
votes
1
answer
117
views
Subquery not using Index, even if forcing
Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1
If I run:
EXPLAIN SELECT COUNT(*) FROM ips WHERE ip="1.2.3.4"
This query ...
- 599
1
vote
2
answers
54
views
Execution time/cost of function in assembly
I have a query calling XmlTransform from Microsoft.MasterDataServices.DataQuality on about 1000 rows or so and it takes 30s to do the whole set (the XSL is in a fixed variable), is there any way to ...
- 6,582
0
votes
0
answers
56
views
Oracle Query Performance
I have a query that I need it to be execute fast as possible. Even though I have indexes, it is executing very slowly and I can't figure out why.
This is the query, I am trying to optimize.
SELECT d.*,...
1
vote
1
answer
37
views
Specific index issue with postgresql planner/optimizer
i have an issue where i have no idea anymore and can't seem to find a similar one already discussed, so...
Am/have to be on pgsql 10.15 / RHEL x86_64.
rel A has about 813 million records.
format is ...
