Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,135
questions
6
votes
1
answer
509
views
Estimations seem too low/inaccurate for nested loops operator
This question is a follow-up to somebody else's question: Adding an INNER JOIN ruins query performance due to different execution plan despite updated STATISTICS and RECOMPILE, why?
My question is ...
- 171
-3
votes
1
answer
69
views
4
votes
1
answer
341
views
Scalar function alters estimate for preceding operator
Please could someone help me understand why the presence of a scalar function is changing the estimate of a preceding index scan?
I'm using a copy of the StackOverflow2013 database.
SQL Server 2019 (...
- 421
0
votes
1
answer
79
views
MariaDB: Limit changes query plan and makes query very slow
It's counter-intuitive and weird to see how adding "LIMIT 2" changes whole plan for MariaDB (version 10.6.7) from using one index and having total time of 2 seconds into another index with ...
0
votes
0
answers
84
views
How to understand this execution plan?
I have a very complex execution plan and below is part of it, opened in Plan Explorer.
The image is kind of faded, I guess it's because all operators produce 0 rows.
As you can see, the arrow 1 is a ...
- 945
8
votes
1
answer
2k
views
Why am I getting a sort when I have an index?
Azure SQL Database.
I have a table from which I need to get the first and most recent rows for Col1 and Col2 based on CreateDate.
CREATE TABLE dbo.table1 (
Id INT IDENTITY(1,1) ...
- 399
5
votes
3
answers
262
views
Can Postgres scan indexes backwards?
We use an Amazon RDS instance with
PostgreSQL 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
I have a simple classic top-1-per-group query. I need to ...
- 4,577
0
votes
0
answers
32
views
Is it possible to configure a postgres DB to send a notification of some kind when a frequent query's execution plan changes?
Backend web developer here with possibly a naive question (in the title).
I recently started diving into the inner workings of Postgres query optimization and I find the query execution plan concept ...
- 101
1
vote
1
answer
178
views
Force Index does not work on aws mysql
I have a table sales with a compound index on (user_id, eastern_date, state)
CREATE TABLE sales (
id int not null auto_increment,
user_id int not null,
eastern_date date not null,
...
- 113
1
vote
0
answers
21
views
Wrong query plan for GIN index
I have the following repeatable case.
I have an uuid[] column with a gin index, which stores all unqiue uuids, without doubles and repeats.
Test table:
CREATE TABLE test
(
id serial not null, ...
- 11
6
votes
1
answer
174
views
Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan
I have a query which I force a plan for in Query Store (the plan is the one SQL Server compiled for this query) If I run the query immediately afer forcing the plan, I get the NO_PLAN ...
- 1,741
-1
votes
1
answer
77
views
queries with significant difference of estimated and actual number of rows - skew data - in the query plan
I am using dbcc show_statistics looking for skew data in my histogram and improving the quality of my statistics.
OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector.
If ...
- 15.6k
0
votes
0
answers
75
views
Azure SQL Database plan forcing
we've got Azure SQL Database with Automatic tuning - force plan turned on
sometimes executions continue with both the more expensive plan as well as the cheaper forced plan even though plan forcing is ...
- 3,703
0
votes
1
answer
118
views
How does SQL Server's Performance Dashboard categorize a query as "ad-hoc" or otherwise?
I'm looking at the CPU usage in sql server's server performance dashboard report.
I have 10 dbs on the server.
So the pie chart shows me 85% for adhoc queries. Whereas only 15% is split amongst all ...
- 3,225
0
votes
0
answers
122
views
Postgres query plan different on local and prod. Not using index and bad row estimation
Confusing index-use question, I have a database that looks like this:
+-----------------+------------------+-----------+----------+--------------+-------------+
| Column | Type | ...
- 101
0
votes
1
answer
95
views
Is there a way to tell Postgres to automatically give up on a plan and consider alternatives after exceeding estimated cost
There seems to be a few situations in which Postgres considers an "on average slightly better" plan over a "more predictable plan"
The failure mode of which can be the following:
...
1
vote
2
answers
313
views
Query Store plan forcing failed with failure reason NO_PLAN [duplicate]
I have a query in query store for which I have forced a plan.
I can confirm the plan is forced with
SELECT * FROM sys.query_store_plan WHERE is_forced_plan = 1
and the plan shows up in the results
...
- 1,741
9
votes
2
answers
706
views
Why SQL Server scans all rows when updating bit column even for primary key via linked server
I am using simple update statement for specific primary key for SQL linked server as follow
UPDATE t
SET
processed = 1,
processed_on = GETDATE()
FROM [LINKED\SERVER].DATABASE.dbo.FileQueue t
...
- 93
0
votes
1
answer
139
views
How to enhance performance when compare two table feature value to run faster?
I work on sql server 2019 i face issue when compare feature values between two tables
Based on RecomendationId .
it take too long time to display result
time that take it 5 minute .
total rows on ...
- 103
0
votes
1
answer
22
views
Formatting numbers that appear in Sql Server Execution Tooltips
Is it possible to format the numbers which appear in SQL Server's Execution Plan Tooltips/Properties? For example in the image below it would be nice to have the Estimated Number of Rows for All ...
- 103
0
votes
1
answer
132
views
Query Execution Plan Analysis
I've recently started my journey as a dba and I found those interesting scripts by Brent Ozar to check the health of my database. I run some of the scripts and I figure out that there are some huge ...
- 35
7
votes
1
answer
323
views
Why does an inequality search on an indexed column give constant scans
Using the StackOverflow2010 database, I can create an index on the users table as follows:
CREATE INDEX IX_DisplayName ON dbo.Users
(
DisplayName,
UpVotes
)
And then run an inequality search ...
- 1,741
0
votes
0
answers
43
views
What are all the reasons there would be multiple plans for the same statement_sql_handle and query_id?
I use Azure SQL Database in compatibility level 150, using Simple Parameterization. Indexes are rebuilt weekly and statistics are updated nightly. Auto-update for statistics is enabled.
I have a query ...
- 2,139
0
votes
0
answers
43
views
How to optimize execution plan for lopsided table
I've got a table with an index that's pretty lopsided, where a small number of values has a much higher count of rows than most of them. In the table there are a few hundred million rows - my index is ...
- 137
-2
votes
1
answer
208
views
External tables: Why create statistics?
The documentation for Polybase says that creating statistics for External Tables will help getting better execution plans. This question is about when the External Data Source is another SQL Server.
I ...
- 765
1
vote
0
answers
69
views
Optimizer Occasionally chooses a bad plan - how to tune query to ensure it is consistent
I have a query in my environment which occasionally gets a "bad" plan and takes a long time to run.
We can see from the query store output below that the query normally uses plan 25493651 ...
- 1,741
0
votes
1
answer
143
views
Retrieve execution plan via XML data from sp_WhoIsActive in adhoc process
We have installed a process in which we execute the sp_WhoIsActive procedure every 1 minute. We combine some additional logic into the same job.
Currently the sp_WhoIsActive procedure is run every 4-5 ...
- 261
0
votes
1
answer
124
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 ))
...
- 259
0
votes
1
answer
48
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 ...
- 399
1
vote
2
answers
368
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 ...
- 675
0
votes
1
answer
43
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?
- 252
3
votes
0
answers
197
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
64
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,134
11
votes
2
answers
804
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.
...
- 37.6k
1
vote
1
answer
121
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
595
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
453
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
1
vote
1
answer
2k
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,...
- 155
-1
votes
1
answer
1k
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 ...
0
votes
1
answer
707
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
296
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 ...
- 153
4
votes
1
answer
381
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
902
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
265
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 ...
- 181
1
vote
1
answer
55
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 ...
- 193
7
votes
2
answers
972
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 ...
- 3,225
4
votes
1
answer
181
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
99
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
-----------------+---------------+-----------+----------+----...
- 225
1
vote
3
answers
119
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
483
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 ...
- 15.6k