Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,075
questions
0
votes
1
answer
26
views
How are HashAggregate row estimates calculated?
I have the following query:
SELECT foo.*
FROM foo
WHERE foo."id" IN ((SELECT bar."id"
FROM bar
WHERE bar.user_id IN ( 100, 102 ))
...
- 207
0
votes
1
answer
26
views
SQL All Procs have WITH RECOMPLILE so how to maintain a bloated plan cache
I'm working a star schema data warehouse created in Azure SQL database where the last developer included WITH RECOMPILE on all the Procs.
This I believe was because the ETL only executes these Procs ...
- 271
1
vote
2
answers
37
views
is it possible to make the postgresql estimate the rows by condition
I have an article table with more than 2000000 rows, because the count was so slow, I am using the estimate rows like this:
select count_estimate('select * from article');
this works for the article ...
- 527
0
votes
1
answer
17
views
'Query Performance plan' query in singlestore
when I go to memsqlOps > Explore > Query. I’m able to see query performance and active long running queries. What table or tables can I select from to get that same information?
- 242
3
votes
0
answers
91
views
Execution Plan changes between good and bad query plans (Azure SQL Server)
I've done my best to follow Why is my query suddenly slower than it was yesterday?
The ShowPlanXML parses fine with SQL Sentry Plan Explorer but not via brentozar.com/pastetheplan (not sure why) so I ...
- 175
0
votes
0
answers
29
views
Select MIN on Primary Key Field Using Sub-Optimal Index / Plan
I have a stored procedure that takes in a list (table variable) of IDs, and then it performs some queries to analyze the contents of the list and take the appropriate actions. One of the queries ...
- 1,074
11
votes
2
answers
619
views
Why Does SQL Server Track Time Accurately For Some Multi-Statement Table-Valued Function Query Plans, And Not Others?
Setup
For this demo, I'm using the 2013 version of the Stack Overflow database, and SQL Server 2022 CTP2, but it is valid going back through SQL Server 2017 which is as far back as I care to check.
...
- 34.2k
1
vote
1
answer
49
views
Is a correlated function in the FROM clause executed for every row? [closed]
I have a heavy function, let's call it fcalc(x,y) -> my_z. I need the result my_z to both be a filter (too low and the row is discarded) and in the result set (so my client can see it). I write the ...
- 13
0
votes
1
answer
38
views
Postgres query with large IN, and joining on a temporary table doesn't seem to work
EDIT: The query plans in the question body come from EXPLAIN, but as @jjanes suggested, EXPLAIN (ANALYZE, BUFFERS) may be more useful. Since the output is very large I have uploaded them here: https://...
- 103
3
votes
1
answer
183
views
MariaDB Query Optimizer ignores index
MariaDB Version 10.5.15
simple Table, two columns with an index
If I make a query on a table and use two columns in the where conndition that have an index, no index is used. Why?
CREATE TABLE `logs` ...
- 133
0
votes
1
answer
109
views
MySQL does not use index, does full table scan instead
EDIT: See further developments below.
I have an InnoDB table with the following schema:
CREATE TABLE `data` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`app_id` CHAR(27) NULL DEFAULT NULL,...
- 145
-1
votes
1
answer
127
views
SQL Server stored procedure taking too long sometimes even though indexes have been added with memory grant issues
I have a stored procedure that takes too long sometimes even though I have added all suggested indexes.
I keep getting the Query had to wait for memory grant
The query plan can be found here: Query ...
-1
votes
1
answer
54
views
Partition scan order in Postgres
I have created a natively partitioned table in Postgres and when trying to query this, Postgres is scanning the partitions in ascending order. The partition key is a timestamp column. For my use case, ...
0
votes
1
answer
180
views
Adding 'top 20' massively slows down query!
I have a query:
select * from Aview where field=20
order by id desc
This returns 2700 rows from the view in about 1 second.
Adding 'top 20' to the query makes MSSQL return in 43 seconds!!
This has ...
- 155
4
votes
1
answer
192
views
SQL Server execution XML plan cut off in length
I am running the following standard query to retrieve the XML execution plan for queries that I ran on my SQL Server via command line.
SELECT pl.query_plan FROM sys.dm_exec_cached_plans ep CROSS APPLY ...
- 141
0
votes
1
answer
123
views
Optimize filter on SQL query on PostgreSQL
I have a query with a high cost on filtering the results and I am thinking I should be adding an index to optimize the plan, but the indexes I tried till now had no impact. Can I optimize the query by ...
- 115
0
votes
2
answers
61
views
In my query plan - multiple nodes say that their cost is 100%
In this query plan:
https://www.brentozar.com/pastetheplan/?id=HydtQjsO5
...multiple nodes have a cost of 100%. How does this happen? How do you figure out where to start optimizing when so many nodes ...
- 171
1
vote
1
answer
43
views
Potentially Improper Row Count Read in Parallel Bitmap Hash Match Plan
I am trying to determine whether or not this is an execution plan reporting bug or an actual feature that is working as intended.
In my execution plan, when using MAXDOP 1, I am seeing the entire ...
- 93
6
votes
2
answers
584
views
Which execution plan is stored in the plan cache?
When query is executed, SQL server will produce a list of query plans and heuristically choose the plan with lower cost.
The chosen plan will be stored in the plan cache for subsequent use when it ...
- 2,407
4
votes
1
answer
107
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
43
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
1
vote
3
answers
63
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
323
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.9k
0
votes
0
answers
44
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 ...
- 733
1
vote
1
answer
59
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 ...
- 115
0
votes
1
answer
93
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
54
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 ...
- 425
1
vote
1
answer
32
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
213
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
97
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
106
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 ...
- 207
3
votes
0
answers
143
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
298
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.9k
2
votes
2
answers
197
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
255
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.4k
-2
votes
1
answer
58
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
43
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
45
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
741
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
893
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
43
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
81
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
108
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
386
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
381
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,463
1
vote
0
answers
95
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
46
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
25
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,304
2
votes
1
answer
206
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
...
- 565
-2
votes
3
answers
145
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