Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
-2 votes
0 answers
31 views

warning in execute plan [closed]

Who has an idea please for this type of warning as is corrected ?
user avatar
4 votes
1 answer
96 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 ...
user avatar
  • 368
1 vote
1 answer
31 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 -----------------+---------------+-----------+----------+----...
user avatar
  • 95
-3 votes
0 answers
35 views

Page Life Expectancy is dropping frequently

Server Spec: 64 GB Ram (R5.2xLarge) Support Team reported there is memory pressure on the SQL Server. Allocated 52 GB memory as min and max for SQL Instance Observed page life expectancy counter value ...
user avatar
  • 1
1 vote
3 answers
51 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) ...
user avatar
2 votes
2 answers
294 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 ...
user avatar
0 votes
0 answers
39 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 ...
user avatar
1 vote
1 answer
45 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 ...
user avatar
  • 113
0 votes
1 answer
46 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=...
user avatar
  • 95
1 vote
2 answers
43 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 ...
user avatar
  • 415
1 vote
1 answer
31 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 ...
user avatar
  • 105
2 votes
3 answers
144 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 ...
user avatar
0 votes
0 answers
31 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 ...
user avatar
1 vote
0 answers
25 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 ...
user avatar
  • 197
3 votes
0 answers
127 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] ...
user avatar
5 votes
2 answers
244 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 ...
user avatar
2 votes
2 answers
74 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 ...
user avatar
5 votes
1 answer
206 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 ...
user avatar
  • 27.1k
-2 votes
1 answer
51 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 ...
user avatar
0 votes
0 answers
41 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 ...
user avatar
2 votes
2 answers
43 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 ...
user avatar
8 votes
1 answer
664 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 ...
user avatar
3 votes
1 answer
236 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 ...
user avatar
0 votes
0 answers
37 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 ...
user avatar
  • 11
2 votes
0 answers
76 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 ...
user avatar
  • 307
4 votes
1 answer
82 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 (...
user avatar
2 votes
4 answers
203 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 ...
user avatar
6 votes
1 answer
252 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....
user avatar
  • 1,450
1 vote
0 answers
40 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, ...
user avatar
0 votes
1 answer
36 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 ...
user avatar
0 votes
1 answer
21 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 ...
user avatar
  • 1,294
2 votes
1 answer
107 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 ...
user avatar
-2 votes
3 answers
90 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 ...
user avatar
  • 55
0 votes
1 answer
49 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 ...
user avatar
1 vote
1 answer
68 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 = ...
user avatar
  • 1,294
1 vote
1 answer
30 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). ...
user avatar
  • 55.7k
0 votes
1 answer
89 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 ...
user avatar
  • 1,294
1 vote
2 answers
183 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 ...
user avatar
1 vote
1 answer
153 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 (...
user avatar
3 votes
5 answers
148 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 ...
user avatar
2 votes
2 answers
82 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) ...
user avatar
  • 21
0 votes
1 answer
76 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 ...
user avatar
  • 57
2 votes
1 answer
89 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 ...
user avatar
  • 33
1 vote
1 answer
31 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 ...
user avatar
0 votes
1 answer
49 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, ...
user avatar
  • 101
3 votes
2 answers
324 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 ...
user avatar
0 votes
1 answer
117 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 ...
user avatar
  • 599
1 vote
2 answers
54 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 ...
user avatar
  • 6,582
0 votes
0 answers
56 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.*,...
user avatar
1 vote
1 answer
37 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 ...
user avatar

1
2 3 4 5
22