Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
0 votes
2 answers
35 views

In SQL Server, if I have two otherwise identical stored procedures with different names, will they use the same execution plan?

Azure SQL Managed Instance. Here is the scenario. I have a stored procedure that produces a count of rows that match some criteria. But, depending on the Customer that is chosen, the statistics can be ...
CB_Ron's user avatar
  • 291
-2 votes
0 answers
28 views

How to put indexing in this query,query takes 85 sec to complete

Select count(case when education_qualification='A' then 1 else null end) as Total_Niraksar, count(case when education_qualification='B' then 1 else null end) as Total_Saksar, count(case when ...
girish parganiha's user avatar
0 votes
2 answers
46 views

Performance tuning of SQL Server query, with LEFT OUTER JOIN vs INNER JOIN

In my scenario i need to optimize a stored procedure, that i used to import data into DB from an exchange DB. I am relly puzzled because, the solution with INNER JOIN is lower than the LEFT OUTER JOIN ...
Skary's user avatar
  • 358
3 votes
3 answers
335 views

Getting a SORT operator when I have an index

On a Azure SQL db (SQL2019 compat), I have an ETL process that populates HISTORY tables in a DeltaTrack pattern. In the Proc, there's an UPDATE to the HISTORY table which the query engine is using a ...
Geezer's user avatar
  • 441
1 vote
0 answers
33 views

Wrong query optimization plan with InnoDB

After switching DB engine from MyIsam to InnoDB on MariaDB 10.3.39 we noticed endless queries and huge disk space usage. The problem are some queries that are wrong optimized with the new engine. ...
LateAtNight's user avatar
0 votes
0 answers
32 views

Faster running for this query sql server

i used database engine tunning advisor for creating some index and statistics but still run slow SELECT C.code_commande AS 'Order', C.code_client AS 'Cust&#46', ...
octavian darie's user avatar
3 votes
1 answer
90 views

PostgreSQL Query Planner overestimating Row Count leads to slow Hash Join

I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred ...
LennartF22's user avatar
-4 votes
2 answers
78 views

Calculate total_worker_time in minutes [closed]

I have an easy query which can show me the top 10 elapsed time by query_plan_hash and query_hash: PRINT '-- top 10 elapsed time by query_plan_hash and query_hash --' SELECT TOP 10 query_plan_hash ...
Francesco Mantovani's user avatar
5 votes
1 answer
162 views

Partitioned view aggregation query not optimized

Given the following two tables: CREATE TABLE SalesLedger ( Id int PRIMARY KEY IDENTITY, Date date NOT NULL, Total decimal(38,18), INDEX IX (Date, Total) ); CREATE TABLE Purchases ( Id int ...
Charlieface's user avatar
  • 11.4k
0 votes
0 answers
57 views

How can I improve the performance of a query that relies on data from lots of tables?

I am working on a Web application that renders a food menu in one view. The menu is modelled as one big tree, which contains nodes like services (breakfast, lunch, dinner), courses (starter, main, ...
Check12's user avatar
  • 103
2 votes
2 answers
476 views

Does PERCENTILE_CONT still give terrible performance when calculating medians in 2022 versions of SQL Server?

I'm currently reading a book from SQL Server 2014. It, like every other online source that I've found, tells you that PERCENTILE_CONT is a very slow way of calculating medians and does not show the ...
J. Mini's user avatar
  • 125
0 votes
1 answer
39 views

Why is the PostgreSQL Query Planner choosing such an inefficient solution?

I'm working with postgres 13.9 on Amazon Aurora. In our production environment, we're running a query that is taking >15 seconds to run when the query is using a small LIMIT. For example when the ...
kylejw2's user avatar
  • 103
1 vote
1 answer
50 views

Is it a problem if BIGINT data type parameter gets passed into a table for join with INT data type?

We have an application that has been developed in Hibernate and the SQL’s are all inline from the app. There are no stored procedures. The problem I see is the way parameters are being passed in the ...
Texan DBA's user avatar
0 votes
3 answers
74 views

Increased LIMIT results in performance degradation

This appears to be a variation of a common problem where a small change to the LIMIT clause in a query changes the query plan to one that has vastly inferior performance. In this case, I have two ...
bstovall's user avatar
2 votes
1 answer
83 views

random_page_cost and memoize plan relation

Postgres 15.2. I have the following simple query: SELECT mark.last_modified FROM mark INNER JOIN element ON mark.element_id = element.id INNER JOIN model ON element.model_id = model.id WHERE ...
Cowabunga's user avatar
  • 135
1 vote
2 answers
83 views

Temp table with less number of rows results in Clustered Index Seek Whereas more rows result in Clustered Index Scan - SQL Server

I have the following table and data: CREATE TABLE myTable ( ID INT IDENTITY(1,1) PRIMARY KEY, Column1 VARCHAR(50), Column2 VARCHAR(50), Column3 VARCHAR(50), Column4 VARCHAR(50), ...
lifeisajourney's user avatar
0 votes
0 answers
37 views

Trying to optimize an aggregrate query for a big table in postgres

I'm trying to retrieve to get a top 3 of players from a big database containing their stats. To do this i want to sum the kills they got in all of their games and select the 3 that have the most kills....
Bijleveldje's user avatar
1 vote
1 answer
97 views

Filter out rows where any value is not unique

I am self joining on a table and get the following result: +----+----+----+----+----+----+----+----+----+ | id | s1 | s2 | s3 | s4 | s5 | s6 | s7 | s8 | +----+----+----+----+----+----+----+----+----+ |...
Some_user's user avatar
2 votes
2 answers
69 views

Simple query with two WHERE clauses uses inferior index scan

I have a table named "db_log" with a size of 11GB and approximately 10 million rows in PostgreSQL 13.7 version. The issue is that a simple query is not performing an full index scan for ...
Rio Fajar's user avatar
0 votes
1 answer
260 views

Why the second query is so much faster than the first one?

I have a query that look like this: SELECT DISTINCT "articles"."id", "articles"."company_uuid", "articles"."status", "articles&...
Mio's user avatar
  • 621
12 votes
2 answers
777 views

Unable to execute query and not even able to generate Estimated execution plan

I am working on SQL Server 2019. I have a table dbo.AllDates where I have all dates from 1990 to 2050. I have another table dbo.ActualExchangeRates where I have actual exchange rates for certain ...
saadkaul's user avatar
  • 123
3 votes
1 answer
253 views

How does SQL Server estimate cardinality on nested loops index seek

I am trying to understand how SQL Server estimates cardinality on the below Stack Overflow database query Firstly, I create the index CREATE INDEX IX_PostId ON dbo.Comments ( PostId ) INCLUDE ( ...
SE1986's user avatar
  • 1,898
1 vote
1 answer
112 views

MySQL explain plan doesn't show key lookup

I have a query that's really slow when I select a lot of columns but greatly speeds up when I only select the columns present in the index the query is using. This makes me believe that the query is ...
Archmede's user avatar
  • 165
6 votes
3 answers
1k views

Is there an option or hint possible to improve performance of query with multiple values in the "in" clause

We have a table CustomerNote with 4 columns ID, CustomerID, Note, Date There is an index on CustomerID asc, Date desc When the following query is executed select top 30 Date from CustomerNote ...
MichaelD's user avatar
  • 553
1 vote
0 answers
60 views

Compile time of update query is high on sqlserver 2019 vs sqlserver 2016

A dev team announced performance degradation after migrating their database from SQLServer 2016 to SQLServer 2019. So I started tracing the database using SQL profiler. It turns out duration, cpu ...
Peter Mobiel's user avatar
1 vote
2 answers
78 views

Is it possible to save the statistics profile result into a table?

In SQL Server, we can use set statistics profile on to enable profiling for a session. Then you can execute any statement and the corresponding execution plan will be shown in a tabular form. Is it ...
Fajela Tajkiya's user avatar
-1 votes
1 answer
153 views

What causes a stored procedure to have multiple plans in SQL Server?

We have noticed that many of our stored procedures, which involve the use of local variables, have multiple plans associated with them. By which I mean the same query in the stored procedure has ...
lifeisajourney's user avatar
0 votes
0 answers
32 views

Stored procedure suddenly using multiple execution plans in SQL Server 2012

We have a commonly utilized stored procedure that had been functioning properly, but it unexpectedly began utilizing several plans (as many as 20, according to the performance analyzer) and causing a ...
JoeCharles's user avatar
0 votes
1 answer
270 views

How to efficiently get absolute value of a time interval in Postgresql?

I have a huge table in Postgresql-11 like following: CREATE TABLE my_huge_table( tick_time timestamp(6) with time zone NOT NULL, brok_time timestamp(6) with time zone, trade_day date NOT ...
Leon's user avatar
  • 285
0 votes
1 answer
124 views

PostgreSQL: Unwanted materialize node within nested loop join

I have a peculiar situation where PostgreSQL is choosing to materialize rather than use a particular index I've created for a query where I join a generate_series() against a table storing streamed ...
shangxiao's user avatar
  • 101
0 votes
4 answers
175 views

Window function with subquery results to unexpectedly slow query plan

This is my victim query SELECT *, lag(quantity) over (partition by product_id, size_id, warehouse_id order by size_id, warehouse_id, created_at ASC) - quantity as orders FROM ...
glmn's user avatar
  • 1
2 votes
3 answers
483 views

EXPLAIN ANALYZE does not show what takes time with my GIN index scan

Context I have a table named companies_establishments that holds ~33M rows. I created a GIN index with trigrams, so I can make LIKE queries much faster. CREATE INDEX companies_establishments_id_index ...
Madeorsk's user avatar
  • 123
4 votes
1 answer
186 views

SQL Server generates a query plan with parallelism and TOP statement

I have two simple queries: SELECT TOP(20) * FROM Clients ORDER BY City SELECT TOP(20) Id, Name, City FROM Clients ORDER BY City In the first case I get a result like this Id Name City many more ...
Bert de Saffel's user avatar
1 vote
2 answers
154 views

Query Store plan does not include parameter list

I use the query store to get an overview of executed queries on our databases and to generate performance tests where I execute those queries in parallel on multiple hosts. My task is to test how many ...
SteLoe's user avatar
  • 342
2 votes
0 answers
261 views

Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?

I'm currently using Postgres 12. A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
K.Banks's user avatar
  • 21
0 votes
1 answer
356 views

Query using VIEW very slow when the same query is fast using WITH

Given this view CREATE OR REPLACE VIEW public."SellMeta" AS WITH wh AS ( SELECT "WWHH".id, "WWHH".url, "WWHH".name, ...
Fabiano Taioli's user avatar
0 votes
1 answer
65 views

Help me in optimising the query which takes almost 50 seconds

Please help me optimise the following query running on MySQL 8.0.28 Community Edition. explain select id, created_at, data, deleted_at, entity_id, ...
Avinash Pawar's user avatar
6 votes
1 answer
568 views

How to reduce query size with many repeated UNION subqueries?

I use Postgres 13 and have a table defined with the following DDL: CREATE TABLE item_codes ( code bytea NOT NULL, item_id bytea NOT NULL, time ...
Vitalii Vitrenko's user avatar
3 votes
1 answer
657 views

How To Optimize a Query That Aggregates Then Duplicates a Large Number of Records in a Single Table

In the following post, J.D. brings up that I have a poorly performing query. I am running this query on SQL Server 2019 Standard Edition (query plan was generated in Development Edition) Let's take a ...
David Rogers's user avatar
0 votes
0 answers
56 views

PostgreSQL query times out when selecting a smaller amount of data (different query plan)

PostgreSQL 15.2. We have a table like this (simplified): create table behavior.event_59 ( event_id integer not null primary key, second_offset integer not null, -- ... more columns ) ...
EM0's user avatar
  • 190
1 vote
0 answers
76 views

How to reduce high cost of sort on execution plan

I have a very large query with three UNION ALL and it takes 30 minutes to execute. Analyzing the execution plan I see that it has a high cost in SORT and Filter. I tried to generate the showplan in ...
Carlos D's user avatar
10 votes
1 answer
1k views

SELECT Statement with , * in column list is faster than same statement without *

Situation When querying a database with a SELECT statement with a defined set of columns, the results are received in around 21 seconds. If there is an additional asterisk (, *) at the end of the list ...
John K. N.'s user avatar
3 votes
1 answer
121 views

Improving query response speed when joining a non-spatial table with a spatial table on a non-spatial column

I have a table of frequently updating events that affect certain geometries. The geometry areas are fixed, but there may be an event affecting multiple areas at once, so there's a many-to-many ...
wxdan's user avatar
  • 33
1 vote
2 answers
172 views

Lots of "key value" joins causes SQL Server query to exponentially slow down

I am migrating some data that's stored "key value" style, from an attributes table that uses an Object ID and an Attribute Type as a Clustered Index (I've also tried as a nonclustered index):...
Thomas's user avatar
  • 133
0 votes
1 answer
48 views

What makes the optimizer insert a sort operator in the plan

What are the reasons that the optimizer chooses to insert a sort operator in the plan (to satisfy a stream aggregate or a merge join, etc.) instead of going for hash match or hash join? I have now see ...
xhr489's user avatar
  • 785
0 votes
0 answers
71 views

Hierarchical query to select `children` from `children`

I have the following table mytable where id is a unique identifier at a given r (resolution) and children is an array of unique id's of r + 1. id r children[] 0 2 {0,2467972595799031808,...
Michael's user avatar
  • 273
2 votes
0 answers
17 views

set options for replicating slow queries in SSMS

I am investigating a slow ad-hoc query(about 45 seconds) from our application. I am not able to replicate the slowness in ssms however to investigate indexing, reads, etc. I've attempted to use the ...
DBA Greg14's user avatar
1 vote
0 answers
42 views

Improve cardinality estimation when two joined tables have a condition on an identical column

I have a table foo, with an association table foo_associations with columns foo_parent and foo_child. foo has an column called department_id. Any association made through foo_associations is ...
Joe's user avatar
  • 259
0 votes
0 answers
112 views

Why is Live Query Plan not available on my SQL Server 2019?

Based on this article on Query Profiling Infrastructure SQL Server 2019 should have the lightweight profiling infrastructure v3 enabled by default. However, when I try to display Live Execution Plan, ...
Timbalero's user avatar
  • 101
2 votes
1 answer
321 views

Why changing limit triggers different query plans in postgresql?

Changing only limit from 40 to 50 in following query triggers different execution plans. And unfortunatly one I needed is much slower. So the question is: why this happening and how can I force ...
Gor Rustamyan's user avatar

1
2 3 4 5
23