Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
26 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 )) ...
user avatar
  • 207
0 votes
1 answer
26 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 ...
user avatar
  • 271
1 vote
2 answers
37 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 ...
user avatar
  • 527
0 votes
1 answer
17 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?
user avatar
3 votes
0 answers
91 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 ...
user avatar
  • 175
0 votes
0 answers
29 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 ...
user avatar
  • 1,074
11 votes
2 answers
619 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. ...
user avatar
  • 34.2k
1 vote
1 answer
49 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 ...
user avatar
  • 13
0 votes
1 answer
38 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://...
user avatar
3 votes
1 answer
183 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` ...
user avatar
0 votes
1 answer
109 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,...
user avatar
-1 votes
1 answer
127 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 ...
user avatar
-1 votes
1 answer
54 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, ...
user avatar
0 votes
1 answer
180 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 ...
user avatar
4 votes
1 answer
192 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 ...
user avatar
  • 141
0 votes
1 answer
123 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 ...
user avatar
  • 115
0 votes
2 answers
61 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 ...
user avatar
  • 171
1 vote
1 answer
43 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 ...
user avatar
6 votes
2 answers
584 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 ...
user avatar
  • 2,407
4 votes
1 answer
107 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
43 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
1 vote
3 answers
63 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
323 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
44 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
59 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
  • 115
0 votes
1 answer
93 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
54 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
  • 425
1 vote
1 answer
32 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
213 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
97 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
106 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
  • 207
3 votes
0 answers
143 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
298 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
197 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
255 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.4k
-2 votes
1 answer
58 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
43 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
45 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
741 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
893 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
43 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
81 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
108 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
386 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
381 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,463
1 vote
0 answers
95 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
46 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
25 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,304
2 votes
1 answer
206 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
145 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

1
2 3 4 5
22