Questions tagged [execution-plan]

The strategy selected by the query optimizer to process a query.

Filter by
Sorted by
Tagged with
1
vote
1answer
25 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....
0
votes
1answer
13 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
vote
1answer
43 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 ...
1
vote
1answer
50 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 ...
0
votes
1answer
21 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 ...
1
vote
3answers
110 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 ...
1
vote
1answer
36 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 ...
0
votes
1answer
45 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 ...
1
vote
1answer
26 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: ...
3
votes
1answer
32 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 ...
0
votes
2answers
56 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 ...
1
vote
1answer
45 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
1answer
55 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 ...
0
votes
1answer
21 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 ...
1
vote
2answers
59 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 ...
2
votes
4answers
86 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 ...
1
vote
1answer
50 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 ...
0
votes
1answer
34 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 ...
0
votes
1answer
80 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 ...
1
vote
1answer
24 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 ...
0
votes
1answer
30 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, ...
0
votes
0answers
24 views

PostgreSQL + Postgis strange execution plan

I have constructed the following query to find the % area of intersection between 2 geometry columns then I join this to another table. This query is extracted from a larger query. SELECT fractions....
0
votes
1answer
19 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" ...
2
votes
2answers
205 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]...
3
votes
2answers
254 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].[...
2
votes
0answers
22 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 ...
1
vote
2answers
78 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....
3
votes
2answers
138 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 ...
3
votes
1answer
83 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 ...
0
votes
2answers
30 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
vote
0answers
34 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 ...
0
votes
1answer
54 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 ...
2
votes
1answer
29 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 + ...
0
votes
1answer
26 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
vote
1answer
29 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 ...
0
votes
1answer
28 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 ...
1
vote
3answers
65 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
1answer
26 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 ...
1
vote
2answers
73 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 | ...
0
votes
1answer
42 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 ...
3
votes
0answers
90 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 ...
0
votes
0answers
30 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 ...
1
vote
1answer
127 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
2answers
95 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 ...
1
vote
1answer
32 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 ...
1
vote
1answer
40 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
1answer
164 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 ...
3
votes
2answers
174 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....
2
votes
1answer
106 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, ...
0
votes
2answers
62 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 ...

1
2 3 4 5
20