Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,135
questions
1
vote
1
answer
576
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
2
votes
1
answer
2k
views
OPENJSON running once per row of an INNER JOIN unnecessarily?
I have a table MP_User with 147 entries, and in order to query it, I am passing in JSON containing an array of "query objects", each of which has a unique ID and either an email address or a ...
- 685
1
vote
3
answers
165
views
Is it worth adding or rebuilding an index on a table already involved in an active query
I've kicked off an SQL Server query that has been going for 12 hours. Annoyingly I forgot to rebuild my indexes on one of the tables involved, I needed to do this as they are over 95% fragmented due ...
- 123
1
vote
1
answer
393
views
How to get an actual execution plan from the linked server?
There is a "main" SQL Server to which I have full access and I can connect to it via SSMS. Its version is:
Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64)
Jun ...
- 4,577
0
votes
1
answer
191
views
Forced Parameterization generating duplicate plans
https://www.brentozar.com/pastetheplan/?id=H1qoS7BSu
I am using Azure SQL 18 vCore db with Forced Parameterization, and MAXDOP 8. The vCore count may change much lower for other dbs but for the same ...
- 3
1
vote
1
answer
390
views
PostgreSQL chooses to seq scan huge table instead of index lookup
I have a table, account_balance, with primary key (address, timestamp, currency). This table is pretty big, having nearly 1 billion rows and taking ~3T of space. I am doing a simple query on it:
...
- 121
5
votes
1
answer
2k
views
PostgreSql changes same query plan at runtime
im having a really weird issue here. We have search query that receives multiple parameters and searches by multiple joins in different tables. The query works fine first few time we run it and then ...
- 53
0
votes
2
answers
69
views
How to see if index is being read "ordered" from DMVs
I have a table that is being used in many reporting queries. I would like to know how many times the table is read in an "ordered" way. In the execution plan for clustered index scan one can ...
- 765
2
votes
1
answer
540
views
MySQL performance issue when a hash join is used
I'm having an issue where the optimizer seems to be doing a full table scan and exhibiting a big performance hit when a hash join is used.
Schema and sample data:
CREATE TABLE `px_po_status` (
`...
0
votes
1
answer
69
views
Why does pre-sorted index not help me in window function?
I am trying to speed up this query. Currently it takes ~6s.
It queries a TEMP TABLE providers_tam_with_offices_tmp.
select *,
%(max_providers_to_target_per_territory)s as max_providers,
case
...
- 103
0
votes
1
answer
84
views
DB2 for i equivalent of MSSQL's Show Query Execution Plan
I'm a DB2 newcomer and I'm hoping that DB2 for i has an equivalent to MS SQL's Show Query Execution path.
If so, what's it called, how do I access it, and what permissions do I need to use it?
Thank ...
- 101
2
votes
2
answers
480
views
How to hint Remote Scan operator to estimate more than 10000 rows?
I need to read data from a linked server and insert into a local table. I need to remove duplicates in data and I need to do it on a local server, because remote server is overloaded. So, I added ...
- 4,577
2
votes
4
answers
691
views
Adding an INNER JOIN ruins query performance due to different execution plan despite updated STATISTICS and RECOMPILE, why?
In the home-page of my multi-tenant web application, Linq-to-Entities (EF6) is used to get a paged list of Calendar Events. This data is JOINed with some other tables to produce this query, below.
The ...
- 505
1
vote
1
answer
164
views
Stale Statistics and View performance on SQL Server 2016
I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.
So my doubt essentially is -
What could be the reason that even with ...
- 131
0
votes
1
answer
199
views
why is there an index range scan on max() query?
I have a table that logs the transmission of data to another system.
create table transfer_log (id number(10),
fk_data number(10),
first_sent_time ...
1
vote
2
answers
2k
views
Postgres chooses seq scan over index scan
I have the following query:
SELECT *
FROM foo
LEFT OUTER JOIN foo_history ON foo.id = foo_history.foo_id
WHERE foo.entity_id = 11111
foo table is around 8Million rows and foo_history is around ...
- 23
1
vote
1
answer
925
views
Does it make sense to use OPTION(RECOMPILE) in dynamic SQL?
I would like to find out if using OPTION(RECOMPILE) is beneficial when executing dynamic sql queries?
There are a few points to keep in mind:
There will be hundreds (maybe even thousands) of ...
- 13
0
votes
1
answer
59
views
"Heap Blocks: " Number meaning on Query Plan for a bitmap heap + index scan
What does the number next to Heap Blocks represent in the query plan? I'm not sure if it means the # of blocks (8kb per) of data located on disk representing your data, or in some way the bitmap, ...
- 3
0
votes
1
answer
27
views
Poor execution plan leads to query time increasing dramatically
I have a relatively complex query like the one that follows
select * from (
select
distinct on("car")
entity.id as "car",
metric as "last_visit"
...
- 121
2
votes
2
answers
277
views
Why does SQL Server do lots of unnecessary clustered key lookups in this SELECT - OFFSET - FETCH?
I have the following table:
CREATE TABLE [dbo].[MP_Notification_Audit](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[type] [int] NOT NULL,
[source_user_id] [bigint] NOT NULL,
[target_user_id]...
- 685
3
votes
2
answers
2k
views
Why doesn't SQL Server use my index in this SELECT ... WHERE?
I've created a table with a nonclustered PK (this is by design), and an additional nonclustered index on the column I'm filtering with a WHERE clause ([target_user_id]):
CREATE TABLE [dbo].[...
- 685
3
votes
0
answers
416
views
MariaDB - Query to find unused indexes
I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this ...
- 69
1
vote
2
answers
614
views
Speed up Key Lookup (clustered)
I have a slow query in Sql 2017. Key lookup (Clustered) is taking 62%. There is also an Index Seek (nonclustered) taking 30%.
Is there anyway I can speed this query up?
Execution plan: https://www....
- 11
3
votes
2
answers
394
views
SQL Server Execution Plans and Index Drops
When a SQL Server index is dropped (say it's a duplicate) what are the steps undertaken by the engine to clear and then rebuild the execution plans that reference it? In particular I'm wondering ...
- 119
3
votes
1
answer
227
views
Why does BETWEEN use a btree index but "element is contained by" range operator (<@) does not? [duplicate]
I've got a table with a utc timestamptz which has a "btree" index on the utc column:
CREATE TABLE foo(utc timestamptz)
CREATE INDEX ix_foo_utc ON foo (utc);
This table contains about 500 ...
- 223
0
votes
2
answers
42
views
How to update the time of each row and check if its updated or not
Name
Time
Status
T1
9/12/2020 12:12:09
expected
T2
8/06/2020 11:09:12
expected
These are table name and updated time in the DB I want to get the status and time of updation of specific tables .Set ...
- 1
1
vote
0
answers
427
views
Postgres row estimate is very low for hash join
I have a query that keeps thinking that the result of a hash join will be 1 and I don't know which knob to turn to fix it.
The query takes a form field values in a revision of a document and maps them ...
- 433
0
votes
1
answer
158
views
Execution plan push sort operator to avoid too many PK lookups
For a query like this:
select top 40 ColumnNotInIdexCausingPKLookup from table
where ColumnIncludedInIndex=A
order by ColumnIncludedInIndex,ColumnIncludedInIndex,ColumnIncludedInIndex
Any way to ...
- 553
2
votes
1
answer
67
views
When autovaccum will analyze?
Let's suppose that a table foo became eligible for autovacuum analyze, e.g. I have inserted a number of rows into foo that exceeds autovacuum_analyze_scale_factor * number of rows + ...
- 367
0
votes
1
answer
100
views
A simple T-SQL query plan reuse question [duplicate]
Even though I have parameterized this INSERT on SQL Server, it fails to reuse the plan if the length of one of the VARCHAR fields changes. Do I have any recourse short of padding the data?
The query ...
- 1
1
vote
1
answer
106
views
Row estimate more accurate for primary key column than for others?
Let's create two test tables in a PostgreSQL 13 database:
CREATE TABLE foo (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
value int NOT NULL
);
CREATE TABLE bar (
id bigint PRIMARY ...
- 367
0
votes
1
answer
219
views
Multicolumn GIN index not fully facilitated by query planner
I am experiencing unexpected behaviour with ProstgreSQL 11.10 query planner with multicolumn GIN index usage.
After defining an index on two columns (a,b) of a large table and executing a query ...
- 167
1
vote
3
answers
126
views
evaluate execution plan with different statements
I have 2 different sql statements similar to this:
SELECT * FROM complicated_view v WHERE v.day = DATE '2020-01-25';
SELECT * FROM complicated_view v WHERE v.day = DATE '2021-01-25';
while both of ...
1
vote
1
answer
39
views
Execution plan favorize joins over sort
recently I ran into an issue on our production server (Pg 9.6) and even though I found solution for my problem I would like to understand better the reason behind it. For easier explanation I am using ...
- 115
3
votes
2
answers
1k
views
Postgres prefers slow Seq Scan over fast Index Scan
Tables
I have the following two tables.
books, which contains ~5 million rows:
Table "public.books"
Column | Type | ...
- 133
0
votes
1
answer
188
views
Slow query / wrong query plan for SORT BY LIMIT LEFT JOIN
I have a following 3 tables a,b,c all with compound unique key (application_id, tid) however all those tables are not related with foreign key (it's a limitation of our solution). All tables has ...
- 115
3
votes
0
answers
360
views
Time to generate execution plan for select into from a view takes significantly more time than select
I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've ...
- 131
2
votes
0
answers
396
views
PostgreSQL predicate not pushed down (through join conditions)
Consider the following data model in a PostgreSQL v13 system;
Here, parent table dim contains a small set of reference data, and child table fact contains a much higher volume of records. A typical ...
- 121
1
vote
1
answer
2k
views
How can I investigate why Sql Server is chosing the "wrong" Index?
I have a Transaction table with about 200 million records, one primary key clustered on Id and 2 indexes:
IX_SiloId_ChangedTime_IncludeTime
IX_SiloId_Time_IncludeContent
I run these 2 statements ...
1
vote
2
answers
1k
views
Performance issues since migrating from SQL Server 2012R2 to 2019
I've migrated my database from a SQL Server 2012R2 to a new SQL Server 2019. I've just backupped the database and restored it on new sql server. After that I've imported and updated some additional ...
- 123
1
vote
1
answer
252
views
Getting the "flat" query corresponding to a view using nested views?
I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many ...
- 111
1
vote
1
answer
142
views
How to convince Oracle to create optimal execution plans in SQL queries with bind variables?
An attempt to parameterize a query in Oracle using bind variables and OR :B1 IS NULL or NVL(:B1,col1) in a WHERE clause leads to suboptimal execution plans.
For example, I'd like to write SELECT like ...
10
votes
1
answer
349
views
Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan?
This Question brings a situation where the Query Optimizer chooses poorly the seek predicate among the existing predicates of a simple query. After running some tests I got to the conclusion that the ...
- 4,964
4
votes
2
answers
304
views
Remote Scan when updating using functions
Why does using ISNULL() when updating through a linked server use Remote Scan to retrieve all rows and filter locally instead of filtering through Remote Query?
UPDATE LINKEDSERVER1.database1.dbo....
- 167
2
votes
1
answer
145
views
Suboptimal plan when joining on an ID column with incorrect statistics
We are having an issue with a joining query between 2 tables (showing only relevant columns from the query):
CREATE TABLE IF NOT EXISTS payments (
id TEXT PRIMARY KEY,
consumer_id TEXT,
...
- 143
0
votes
2
answers
535
views
MariaDB 10.4 Query Optimizer Wrong Execution Plan
I have a very strange behavior with some tables and the query optimizer on a MariaDB server.
First of all we have our main table let's call it huge and we have on the same database another table a ...
5
votes
1
answer
344
views
SQL Server Indexed View and TOP
I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in ...
- 421
2
votes
2
answers
994
views
WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance
Earlier today I was working on a Reporting Stored Procedure which had poor performance. I was able to fix the issue by changing a static variable which held a string of ten characters or less from ...
- 1,011
2
votes
3
answers
554
views
Why are those estimates way off?
I have problems understanding the following query plan: https://www.brentozar.com/pastetheplan/?id=SyoD_PosP
First there is the bottom right index seek. The estimation says over 22 million rows but ...
- 341
0
votes
2
answers
881
views
SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?
Let me set the stage:
SQL Server > 2016
Several databases with the same schemas
Data is similar but not identical
The same indexes exist etc., all systems but one work as expected
Examining the ...
- 463