Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
2 votes
2 answers
487 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 ...
Marcello Miorelli's user avatar
0 votes
0 answers
46 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 ...
Newbie-DBA's user avatar
1 vote
1 answer
108 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 ...
Lucas03's user avatar
  • 115
0 votes
1 answer
864 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=...
tail's user avatar
  • 225
1 vote
2 answers
92 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 ...
AndreKR's user avatar
  • 537
1 vote
1 answer
40 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 ...
Sranda's user avatar
  • 152
2 votes
3 answers
998 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 ...
hrdwdmrbl's user avatar
1 vote
0 answers
393 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 ...
Joe's user avatar
  • 259
3 votes
0 answers
269 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] ...
Vojislav Depalov's user avatar
5 votes
2 answers
731 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 ...
Marcello Miorelli's user avatar
3 votes
2 answers
1k 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 ...
valenterry's user avatar
6 votes
1 answer
569 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 ...
Josh Darnell's user avatar
  • 28.6k
-2 votes
1 answer
254 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 ...
ahmed elbarbary's user avatar
2 votes
2 answers
48 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 ...
ahmed elbarbary's user avatar
8 votes
1 answer
1k 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 ...
Radu Gheorghiu's user avatar
3 votes
1 answer
4k 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 ...
ahmed elbarbary's user avatar
2 votes
0 answers
102 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 ...
adam.g's user avatar
  • 261
4 votes
1 answer
249 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 (...
Eyal Shulman's user avatar
2 votes
4 answers
1k 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 ...
ahmed elbarbary's user avatar
6 votes
1 answer
1k 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....
SE1986's user avatar
  • 1,741
1 vote
0 answers
468 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, ...
Francisco Camello's user avatar
0 votes
1 answer
85 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 ...
Bobogator's user avatar
0 votes
1 answer
43 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 ...
Pantea's user avatar
  • 1,402
2 votes
1 answer
706 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 ...
Vadim Samokhin's user avatar
-2 votes
3 answers
475 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 ...
anu's user avatar
  • 57
0 votes
1 answer
236 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 ...
StrangerToKindness's user avatar
1 vote
1 answer
435 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 = ...
Pantea's user avatar
  • 1,402
1 vote
1 answer
34 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). ...
Evan Carroll's user avatar
  • 60.5k
0 votes
1 answer
266 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 ...
Pantea's user avatar
  • 1,402
2 votes
2 answers
1k 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 ...
William Oliveira's user avatar
6 votes
1 answer
1k 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 (...
Dean MacGregor's user avatar
3 votes
5 answers
746 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 ...
Daniel Bragg's user avatar
2 votes
2 answers
105 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) ...
Will's user avatar
  • 21
0 votes
1 answer
87 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 ...
underachiever's user avatar
2 votes
1 answer
167 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 ...
MeMario's user avatar
  • 33
1 vote
1 answer
96 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 ...
nfdavenport's user avatar
0 votes
1 answer
66 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, ...
568ml's user avatar
  • 101
3 votes
2 answers
459 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 ...
Thomas Franz's user avatar
0 votes
1 answer
632 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 ...
Nuno's user avatar
  • 758
1 vote
2 answers
66 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 ...
Cade Roux's user avatar
  • 6,592
0 votes
0 answers
64 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.*,...
Dogukan Evcil's user avatar
1 vote
1 answer
46 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 ...
doppeltesjott's user avatar
1 vote
2 answers
1k views

Different execution time for same query on different Postgres databases

I have two identical databases, development and live. I run this query on both of them.... select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device) On the dev box the ...
ConanTheGerbil's user avatar
0 votes
1 answer
60 views

How is the postgres actually getting some of the columns? Execution plan question

I've got a table with a bunch of columns. Among them facility_id and po_date. I'm writing a complex query and I have an index on those two columns. You can see from the planner output below that ...
mj_'s user avatar
  • 307
3 votes
2 answers
1k views

PostgreSQL: Materialize as inner node of Merge Join in query plan

I'm learning PostgreSQL EXPLAIN plan nodes. Currently, I investigate Materialize node. Here is a query I found in the blog post (https://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/) ...
peremeykin's user avatar
6 votes
1 answer
712 views

Creating multicolumn index for WHERE and ORDER BY

I am trying to create an index to be used in WHERE and ORDER BY clauses at the same time. Reading Postgres 14 documentation (11.4. Indexes and ORDER BY - https://www.postgresql.org/docs/14/indexes-...
IvanD's user avatar
  • 291
0 votes
1 answer
314 views

SqlCommand using Parameters but still it is creating Multiple Plans for One Query

I have the below code, I'm using parameters, but still it is generating many Duplicate SQL Execution Plans. What is wrong and how it can be fixed, please? string cmdString = @"INSERT INTO ...
Ivan Bonanno's user avatar
3 votes
1 answer
171 views

How to correct row estimates due to join of 2 well-estimated results

The following query runs across ~60 databases in parallel. Without hints there are numerous spills and non-optimal plans in at least 10% of the DBs. Using a larger DB as a guide, the query was locked ...
crokusek's user avatar
  • 2,036
0 votes
0 answers
40 views

Can related queries be executed in a single plan?

I'm experimenting with a web app. It executes the exact same set of closely related queries each time to update the fields on the page. There are about 25 of them, and many share subqueries. ...
vandenheuvel's user avatar
1 vote
0 answers
128 views

PostgreSQL: Taking into account correlation between null / not null values of columns during planing

Question: Is PostgreSQL able to take into account correlation between null value of one column and not null values of other columns during planing? Does "Cross Columns Stats" feature allow ...
peremeykin's user avatar

1 2
3
4 5
23