Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
915
questions
0
votes
0answers
22 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
98 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
65 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
27 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
28 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 ...
9
votes
1answer
138 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
161 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
102 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
40 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 ...
5
votes
2answers
176 views
SQL Server Indexed View and TOP
I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in ...
2
votes
2answers
139 views
WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance
Earlier today I was working on a Reporting Stored Procedure which had poor performance. I was able to fix the issue by changing a static variable which held a string of ten characters or less from ...
0
votes
3answers
79 views
Why are those estimates way off?
I have problems understanding the following query plan: https://www.brentozar.com/pastetheplan/?id=SyoD_PosP
First there is the bottom right index seek. The estimation says over 22 million rows but ...
0
votes
2answers
74 views
SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?
Let me set the stage:
SQL Server > 2016
Several databases with the same schemas
Data is similar but not identical
The same indexes exist etc., all systems but one work as expected
Examining the ...
1
vote
2answers
80 views
SQL Server estimating badly
I have a heap with over a million inserts daily. This table is a "staging table" where messages are received and sent to differens queues that are processed by the application.
A stored proc ...
0
votes
1answer
19 views
Can Postgresql exploit “accidental” clustering in plans?
Suppose I have an append-only table with columns customer_id (randomly generated string) and x, and lookups are always done on customer_id.
Say the data look like below, as if we get a batch of rows ...
0
votes
1answer
53 views
How to get a parallel nested loop in postgres 12?
I have a lateral join, and I would like each subselect to execute in parallel. According to the docs (https://www.postgresql.org/docs/12/parallel-plans.html), nested loops can be parallelized:
Just ...
1
vote
2answers
73 views
Optimizing postgres sorting by word_similarity
I have a table with 2M rows of hotel names in Postgresql 12. I am building a typeahead so users can quickly find the hotel by name. I have played around with different Postgres options including FTS, ...
1
vote
1answer
31 views
Do databases optimize repeated similar query params on the same column?
I have the following query:
SELECT * FROM table t
WHERE t.id IN :ids
AND t.id IN :allowedIds
(where the query params are later replaced)
Are these query params optimized by the database itself? I ...
9
votes
2answers
454 views
Building an execution plan takes too long on SQL Server
I have an SQL Server 2014 instance (12.0.2000.8) and a quite complex SELECT statement with about 20 joins. This query works fine with the same data set on PostgreSQL, Oracle and other databases, and ...
0
votes
0answers
9 views
Understanding rows_examined_per_scan and rows_produced_per_join
I'm not mysql guy, but I've got opportunity to work with that recently - I've got to optimize some query on Mysql5.7 (I have to cover that also on 5.6, but started with 5.7 as it has apparently more ...
0
votes
0answers
23 views
alternative IN condition on SQL
i have query like this
select name, time_attandance
from
"VIEW_GO_EMP" "viewGoEmp"
left join "VIEW_ATTENDANCE" "viewAttendance" on
"viewAttendance&...
0
votes
1answer
20 views
Oracle autotrace - pstart and pstop always the same
I have been executing autotrace against a partitioned table. The table is partitioned by month range. For the data I have so far there are 4 partitions - however, when I execute autotrace against ...
0
votes
1answer
18 views
Why does the query plan start with a not-filtered-for table when the filtered-for table's entries exceed a certain threshold?
We process data for our customers. Each customerrequest and is associated with one or more items (less than two on average) and falls into one of <100 categories, foreign keys to which are also ...
0
votes
1answer
41 views
PostgreSQL parallel scan with “greater” or “less” than
I have two queries. One has "=" other one has ">" in as filter condition. My question is why planner plans only query with equals in condition to run in parallel.
If I mess with ...
0
votes
0answers
24 views
PostgreSQL JSONB Execution Plan with high Offset
How does PostgreSQL work i.e. the execution plan on JSONB query with high Offset number and how to improve the performance?
Considering the below "Test Query", help me to clarify the ...
0
votes
1answer
37 views
How to capture execution plan from sql profiler for filtered queries only
Using sql profiler, we trace all slow queries (filter on duration/reads) to see where we can optimise.
Events used:
RPC:Completed
SQL:BatchCompleted
Filter on Duration.
If I add the event ShowPlan ...
0
votes
0answers
25 views
in SSMS, different result sets for a same query in Azure
I have a complex stored procedure that calls different other stored procedures.
I've never seen this behaviour in over 20 years of dba work (since Sql 7).
When I call the stored procedure with about ...
1
vote
1answer
71 views
PostgreSQL: query slow, planner estimates 0.01-0.1 of actual results
I will preface this by saying I'm not well-versed in SQL. I mainly work with ORMs, and this recent headache has brought me to dive into the world of queries, planners, etc..
A very common query is ...
0
votes
2answers
88 views
Why Whole Table Scan (Clustered Index)
I have a big table with 315M rows. with these columns & Index :
[key_] (int & IDENTITY(1,1)), [time_] (datetime), [value_] (float), [quality_] (int)
PK & Clustered-Index: key_ , Non-...
0
votes
1answer
46 views
What is the best way to add optional update in procedure in transaction?
Currently I have a procedure that performs 4 operations in the Transaction:
Update record in table A
Update record in table B
Insert record into table C
Delete record from table D
The first ...
0
votes
1answer
17 views
Query dramatically slower with less rows from WHERE to evaluate
What is the query
SELECT SQL_NO_CACHE id, laptime, driver_id, driverName
FROM (
SELECT race_result_details.id id, driver_id, driver.name as driverName, laptime
FROM race_result_details
LEFT ...
3
votes
1answer
87 views
How does Postgres compute the row count estimate during query planning
I have a simple query such as:
SELECT
*
FROM
example
WHERE
filter_1 = ?
AND filter_2 = ?
LIMIT
10
The table is quite large (about 100 millions row) and it has an index ...
2
votes
1answer
56 views
MariaDB wants to use bizarre and extremely inefficient query plan on Aria, but not on InnoDB (5 seconds vs 1+ hour query)
I'm having some troubles getting a query to preform as expected on my MariaDB 10.4.14 system.
We recently migrated the database from InnoDB to Aria so we could store the INDEX separately on a fast PCI-...
3
votes
1answer
323 views
Select with CROSS APPLY runs slow
I am trying to optimize the query to run faster.
The query is the following:
SELECT grp_fk_obj_id, grp_name
FROM tbl_groups as g1
CROSS APPLY (SELECT TOP 1 grp_id as gid
...
0
votes
1answer
25 views
Readable join for execution plan in Oracle
I want to understand what join operations were done on my database by different users for future analysis and improvements. but I have issues with understanding the execution plan.
Example:
Consider ...
0
votes
1answer
25 views
Statistics Update Time is missing in Actual execution plan
I remember that I have seen somewhere in the past that when I saw the XML of the actual execution plan, it had the details about when the stats were updated on the indexes on a table and what was the ...
0
votes
1answer
42 views
Actual Execution plan does not have memory grant info
I manually ran query in SSMS and captured actual execution plan of the query by enabling its icon in SSMS. I am not getting "Memory Grant" when I keep my mouse on SELECT OPERATOR. I didn't ...
0
votes
1answer
116 views
Query tuning update with hash flow distinct operator
Need some help in understanding the slowness in one of the UPDATE statements like below:-
UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc....
1
vote
0answers
25 views
Why does adding an index increase the execution time in SQLite?
I'll just show you an example. Here candidates is a table of 1000000 candidates from 1000 teams and their individual scores. We want a list of all teams and whether the total score of all candidates ...
1
vote
0answers
64 views
Correlated subquery with common table expressions in SQLite
Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
0
votes
1answer
103 views
Can OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY cause to create totally different execution plan and also cause query to run slow?
We have a SELECT query which is taking time (around 90 secs) to execute. It has OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY in it.
When we remove OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY, it completes in 4-5 ...
0
votes
0answers
32 views
How can I capture wait stats for a previous Actual Plan in SQL Server?
I have some SQL Server stored procedures that sometimes take too much time to run.
If I run them manually, I'm able to catch the Wait Stats. However, I would like to automatically capture all plans ...
1
vote
0answers
30 views
Unexpected result by MySQL Optimizer for identical queries?
Following query is working like expected and uses index
Query takes 0,0481 sec
SELECT
geodb_locations.name,
geodb_locations.name_url,
COUNT(user.uid) AS useranzahl
FROM
user
LEFT ...
-1
votes
1answer
43 views
How are database views executed?
Lets assume there is a design where every table is represented by a view, that is simply selecting all of the columns of the table and all the rows.
--vw
Select * From sys_table
And I then run a ...
0
votes
1answer
50 views
Postgres not using index in citext column
The following query is returning a SEQ Scan instead a Index.
select file_name from myschemadb.my_files where file_name = 'djsaidjasdjoasdjoaidad'
Engine
Postgres 11.5
My table:
CREATE TABLE ...
0
votes
1answer
60 views
Postgres sometimes uses sequential scan instead of an index only scan for data that doesn't exist
We are using Postgres 12 and We have a simple table like this:
> \d live_event_segmentation
Table "public.live_event_segmentation"
Column | Type ...
1
vote
2answers
100 views
Why is index not being used? Can I replicate plan?
I have been tuning a query/ indexes in a dev environment, but cannot replicate the new query plan when applying the same changes to a UAT environment.
Specifically, in the UAT environment the ...
4
votes
0answers
141 views
Hash join hint returns an error when using local variable
The query below is using the StackOverflow2010 sample database:
DECLARE @Id INT = 18471
SELECT c.UserId FROM dbo.Comments AS c
INNER HASH JOIN dbo.Users AS u
ON c.UserId = u.Id
AND c.UserId = @Id
...
0
votes
2answers
62 views
Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server
This is driving me bananas.
According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and ...
0
votes
0answers
48 views
Clearing plan cache improves performance - bad plans in cache?
I have run into this situation a number of times on a VM with SQL Server Express 12.0.6205.1 (SQL Server 2014 SP3 CU1)
Using SSMS, I'll run a stored procedure a few times. It consistently takes, say ...
