Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
893
questions
0
votes
1answer
16 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
37 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
22 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
30 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
23 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
67 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
84 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
44 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
41 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
53 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
193 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
23 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
24 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
38 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
112 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
22 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
42 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
65 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
33 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
39 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
49 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
98 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
133 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
55 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
41 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 ...
1
vote
1answer
38 views
PostgreSQL incorrectly using primary key index for min/max query
I have a table with the structure similar to this:
CREATE TABLE employees (
id bigserial NOT NULL,
name_id uuid NOT NULL,
department uuid NOT NULL,
details text NULL,
deleted bool ...
1
vote
1answer
26 views
Wrong estimated row count
PostgreSQL 11
Running EXPLAIN returns a row estimate that is way too low compared to the actual row count.
Given the query
SELECT s.offer_id,
category_id, category_name_de, category_name_fr, ...
0
votes
1answer
33 views
SQL extended event captured only the estimated execution plan but no actual execution plan
My SQL instance version is Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288)
I am troubleshooting the application time-out and the time-out SELECT query is quite simple. It only takes less than 1 ...
0
votes
1answer
25 views
Bad estimates when sorting, good estimates without sort
I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2.
I needed to anonymize the query due to security policies.
The query is like this:
SELECT TOP 1000 Object1....
2
votes
0answers
54 views
Does SQL Server really require fully qualified object references to match a statement to a cached execution plan [duplicate]
From SQL Server Docs, section Execution Plan Caching and Reuse:
The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully ...
2
votes
1answer
81 views
How to optimize Merge Anti Join?
While trying to find the fastest way to do a bulk update of a large table, I came up with the following plan:
having main_table (potentially above 1000 mln rows) and update table up to a few million ...
2
votes
1answer
178 views
SQL Server - Prevent Clustered Index Scan on a specific table
My database contains a specific table that is quite large (250+M rows, 100+GB data space).
Generating a clustered index scan on this table is always a bad idea.
This table has multiple indexes and ...
1
vote
1answer
50 views
Is there anything wrong with splitting a complicated view into many small ones?
I have a view where each row is associated with a customer, and the columns are various computed values such as life_time_value and purchases_per_week, as well as more complicated statistical values ...
0
votes
1answer
25 views
can't find column stats for newly created table
I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached.
database current settings are:
I started with ...
0
votes
1answer
46 views
Does plan recompilation happens to plan guide?
Does plan recompilation happens for plan guide due to the, for example, auto update stats?
P.S Do not take into account adding a hint OPTION (RECOMPILE) in the plan guide
1
vote
1answer
46 views
How to prevent seek pagination from doing sequential scan over entire table
I have 3 tables and a materialized view:
resource_categories contains all category names and metadata
create table if not exists resource_categories (
category_id INT,
title VARCHAR(255),
...
0
votes
1answer
85 views
Query processor ran out of internal resources and could not produce a query plan” - MariaDB
i have some query
SELECT COUNT(loan_payment) as setcolumn
FROM table1
WHERE paydate between {d '2020-05-01'} AND {d '2020-05-31'}
and period_code = 'MONTHLY'
AND company_id =...
0
votes
1answer
24 views
Postgres foreign data wrappers weird join behavior
So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins.
Keep in mind the following snippets are ...
1
vote
2answers
67 views
SQL Server order of execution and query plan
I am aware that the order of execution of a query in SQL Server is (excluding TOP, etc):
FROM, JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
However, I cannot understand how in the query plan the sort (...
7
votes
1answer
217 views
GROUPING SETS returning unexpected results with calculated expression
Here we have two similar queries using grouping sets
where the SELECT clause contains some expressions calculated in aggregation:
SELECT RN10, RN10 / 10, COUNT(*) FROM
(
SELECT RN, RN/10 AS ...
13
votes
1answer
1k views
SQL Server Spill level 0 - what is it?
I was asked to look into performance of a query in SQL Server 2016 SP2, and I found something that I have not seen before:
<Warnings>
<SpillToTempDb SpillLevel="0" SpilledThreadCount="1" /&...
-2
votes
1answer
31 views
Clustered index scan in Delete query
I am trying to improve the performance of the delete query below. #ROW_IDS.Column_ID and Table1.ID are big integer values.Table1.ID and #ROW_IDS.Column_ID are clustered indexes. Table1 has 131 million ...
5
votes
2answers
94 views
More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows
I have some queries where I need to get more than 64 specific rows, like this example with 65 IDs.
TableID is primary key, type BigInt.
SELECT * FROM TableA
WHERE TableID IN (260905384, ...
1
vote
1answer
24 views
Query performance improvement
Can I improve performance of this query?
SELECT transact.id
FROM transaction0 transact
JOIN journ_notes journ ON transact.journal_jdoid = journ.jdoid
JOIN product ...
1
vote
1answer
66 views
Weird planning time data dependency in Postgres
We got a fairly large table holding session data in Postgres. Recently, we found that the planning time for the most common query on this particular table increased over time, at one point to ~130ms ...
0
votes
1answer
46 views
Why scan clustered index instead seek nonclustered index?
There is SQL script:
CREATE TABLE users
(
id INT,
firstname VARCHAR(50),
surname VARCHAR(50)
);
CREATE CLUSTERED INDEX ix_users_id
ON users (id);
CREATE ...
2
votes
1answer
83 views
Query planner enerates different plans in psql and application for the same query
I am optimising an application on a development server; the database is being dropped and restored from time to time (always the same database and data) and the caches flushed.
I want to identify ...
