Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,015
questions
0
votes
1answer
20 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, ...
2
votes
2answers
126 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 ...
0
votes
1answer
27 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 ...
1
vote
2answers
48 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 ...
0
votes
0answers
50 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.*,...
1
vote
1answer
32 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 ...
1
vote
2answers
58 views
Different execution time for same query on different Postgres databases
I have two identical databases, development and live.
I run this query on both of them....
select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device)
On the dev box the ...
0
votes
1answer
34 views
How is the postgres actually getting some of the columns? Execution plan question
I've got a table with a bunch of columns. Among them facility_id and po_date. I'm writing a complex query and I have an index on those two columns. You can see from the planner output below that ...
2
votes
2answers
102 views
PostgreSQL: Materialize as inner node of Merge Join in query plan
I'm learning PostgreSQL EXPLAIN plan nodes. Currently, I investigate Materialize node.
Here is a query I found in the blog post (https://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/) ...
5
votes
1answer
230 views
Creating multicolumn index for WHERE and ORDER BY
I am trying to create an index to be used in WHERE and ORDER BY clauses at the same time.
Reading Postgres 14 documentation (11.4. Indexes and ORDER BY - https://www.postgresql.org/docs/14/indexes-...
0
votes
1answer
46 views
SqlCommand using Parameters but still it is creating Multiple Plans for One Query
I have the below code, I'm using parameters, but still it is generating many Duplicate SQL Execution Plans. What is wrong and how it can be fixed, please?
string cmdString = @"INSERT INTO ...
3
votes
1answer
78 views
How to correct row estimates due to join of 2 well-estimated results
The following query runs across ~60 databases in parallel. Without hints there are numerous spills and non-optimal plans in at least 10% of the DBs.
Using a larger DB as a guide, the query was locked ...
0
votes
0answers
37 views
Can related queries be executed in a single plan?
I'm experimenting with a web app. It executes the exact same set of closely related queries each time to update the fields on the page. There are about 25 of them, and many share subqueries. ...
1
vote
0answers
53 views
PostgreSQL: Taking into account correlation between null / not null values of columns during planing
Question:
Is PostgreSQL able to take into account correlation between null value of one column and not null values of other columns during planing? Does "Cross Columns Stats" feature allow ...
0
votes
0answers
32 views
How to optimize the following query by adding more indexes?
I am trying to optimize a query which has been destroying my DB.
https://explain.depesz.com/s/isM1
If you have any insights into how to make this better please let me know.
We are using RDS/Postgres ...
0
votes
2answers
120 views
Reduce rows read by the database engine for this aggregate query
I would like to tune this query - it is reading more rows than I would like. I've added an index as suggested by the Database Tuning Advisor.
The query goes like this:
SET STATISTICS TIME, IO ON;
...
0
votes
1answer
22 views
PostgreSQL IO Cost logic
I've been trying to get an IO cost number, similiar to how MS SQL provides it, but in PostgreSQL.
I have read that using EXPLAIN ANALYZE SELECT ..., there will be a cost in the output, but the cost ...
0
votes
1answer
56 views
Sort execution plan XML by estimated operator cost
I want to sort execution plan XML by Estimated Operator Cost, but the problem is:
SQL Server execution plan has the "Estimated Operator Cost" entry in UI:
...but that value is missing from ...
1
vote
1answer
44 views
Query planner showing identical row counts for MCV and non-MCV values with a partial expression index of a JSONB field
I have the following table in PostgreSQL 13.3:
CREATE TABLE node (
id serial NOT NULL,
meta jsonb NOT NULL,
...
);
And there's index:
CREATE INDEX node_meta_group_owner_uuid ON node USING BTREE ...
1
vote
0answers
66 views
Questions about execution plan
I'm reading "SQL Server Execution Plans, Third Edition, by Grant Fritchey".
It includes the following query and the execution plan (queried against the Adventure Works database).
SELECT soh....
0
votes
0answers
32 views
SQL Server query tuning strategy
I'm new to query tuning and have been using Brent Ozar's tools to identify costly queries on the server.
One query that is executed numerous times per minute is triggering warnings for:
Forced ...
0
votes
2answers
45 views
Need to maintain execution plans on SQL Server Upgrade 2017 to 2019
We are working on a performance issue and need to maintain the execution plans for troubleshooting. Need to maintain the execution plans and ensure the plan cache is not flushed out on upgrade to 2019....
0
votes
1answer
25 views
In which case Optimizer rearranges the conditons in mysql?
recently I have added index "new_index" on this table.
CREATE TABLE `y` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`shipping_provider_id` int(10) unsigned DEFAULT NULL,
`...
2
votes
1answer
106 views
Why does adding a WHERE clause break my working query, with error "Query processor could not produce a query plan because of the hints defined..."?
I have the following (stupidly-simplified) query that leverages two CTEs that refer to the same table and joins them to each other:
WITH CTE1 AS
(
SELECT dbo.RemoveNonNumericCharacters(PhoneNumber)...
0
votes
1answer
51 views
Performance issues after upgrading to Mysql 8.0.19 version
Few queries are performing very poorly after the upgrade. Here are the examples.
****Example 1**:**
SELECT
x.CI_TITLE 'ciTitle',
x.DEPARTMENT_MEANING 'departmentMeaning',
x....
3
votes
3answers
172 views
Reducing Temp Table Scans when joining two temp tables using OR conditions
I'm working on a complicated query that I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
0
votes
1answer
92 views
SSMS - SQL Execution Plans
I am new to SQL execution plans and I'm trying to learn more about them.
I have a few questions.
How long does a SQL Execution Plan stay in memory? If I disconnect my connection, I assume it won't be ...
0
votes
1answer
32 views
Do we have information about duration time of query in actual execution plan?
I am building application in C# which is parsing Actual Execution Plan. I need basic stats like Row count, CPU time, Page Reads etc. All above stats I found in XML of Actual Execution Plan. I can not ...
0
votes
1answer
59 views
How can I reduce Estimated I/O and subtree cost in SQL Server Execution plan?
I have a table with more than 1 million rows.
When I execute a simple SELECT query it takes about 17 seconds to retrieve the data.
SELECT * FROM MyTable
The execution plan gives the following info:
...
0
votes
1answer
34 views
Conversion may affect "CardinalityEstimate" CONCAT NVARCHAR and UNIQUEIDENTIFIER
So I have a temporary table with only one column NVARCHAR(400).
In this temp table, I Insert values from various sources.
One of these is
INSERT INTO @permissions
SELECT Concat (N'resourcetype_', ...
2
votes
1answer
124 views
Does each member of execution plan run in its entirety before the next execution starts?
I am looking at an execution plan and reading it from right to left.
Example:
1<-2<-3<-4
^
|_5
So 4 and 5 execute in parallel. When both are fully done, then 3 executes in its ...
0
votes
1answer
47 views
Recalculate "Estimated I/O Cost" without exporting and importing database
I have the following query:
SELECT [t].[Key], SUM([t].[ValueA]), SUM([t].[ValueB])
FROM [MyTable] AS [t]
GROUP BY [t].[Key]
This table had an nvarchar column with value lengths of 2000 characters (I ...
0
votes
1answer
54 views
Postgres Planner not using GIN index Occassionally
Platform: Heroku; PostgreSQL Version 13
I have a table around 46GB and there are two GIN indexes on name and email columns
I have a search query that is suppose to search in name and email columns ...
0
votes
1answer
54 views
Is there any option to get actual execution plan as xml value to insert do different table?
Do you know If there is any option to get actual execution plan
and insert this plan to another table ? I would like to have table with history of actual plans.
1
vote
0answers
52 views
What is the best way to have actual execution plan and IO stats as values to insert?
We have idea to create procedure which will test performance of query and insert specific information associated with executed query to table with test performance history ([dbo].[TestHistory]. We ...
-3
votes
1answer
64 views
is HAVING clause before GROUP BY clause processed differently than after?
Is there any difference how query is processed (translated to extended relational algebra), if HAVING condition is written before GROUP BY (compared to after)?
In logical query plan it should be ...
0
votes
1answer
104 views
SQL Server chooses very poor query plan
Recently, we've moved with our database from MySQL to Microsoft SQL Server (software is compatible with both engines). Overall performance after migration is quite good, but we've got problem with ...
2
votes
1answer
46 views
Speed the response time of the Query
So I have this query that I am using on an API endpoint, the query takes between 3-4 seconds to run, returning a small OFFSET of 10 rows. Is there a way I can put indexes somewhere or improve on ...
5
votes
3answers
352 views
Hovering over an Operator in Execution Plans: How to make the tooltip not disappear when moving the mouse
I want to share my screen and show an execution plan but when I hover my mouse over an operator and what to point to something with my mouse in the tooltip then it disappears. Is there a way to make a ...
5
votes
1answer
231 views
Query plan not reused for identical queries sp_executesql vs adhoc
One of the queries in my application is timing out. With the SQL Server profiler I was able to get the exact faulty SQL code. It's an sp_executesql dynamic statement that runs in > 2 minutes in ...
1
vote
1answer
37 views
PostgreSQL chooses a wrong order of Hash Join
EXPLAIN shows that PostgreSQL chooses a bad plan for hash join. it should have first iterate by tags_name_value_height2 in the table tags and use it to find corresponding transactions.id, but instead ...
0
votes
0answers
19 views
Force PostgreSQL not to think beyond what EXPLAIN says [duplicate]
EXPLAIN gives a good strategy but nevertheless the query (and EXPLAIN ANLALYZE) runs extremely slowly.
Is there a simple way to force PostgreSQL always query by the same plan that EXPLAIN produces for ...
3
votes
1answer
127 views
Why does COALESCE prevent the use of an index on a varchar, but not on a text column?
Imagine a view which contains a COALESCE of two varchar columns from two different tables.
The underlying varchars are indexed in both tables.
In Postgres 11.6, filtering this view on the result of ...
1
vote
3answers
208 views
SQL Server not creating parallel plan with OPTION(MAXDOP 20)
We have a UAT3 server hosted on VM with 8 sockets and 20 processer, we have similar UAT2 server hosted on the same VM with same configuration.
We are running a below query on both the server
select ...
1
vote
0answers
38 views
PSQL 9.6 - OR query is much slower on joined table
We have a production POSTGRES 9.6 db with about 100M records (LOYALTY) and new table (has just 4 data as of now) "INFO"
Full index name on table loyalty
indexname "loyalty_activated_at&...
0
votes
1answer
41 views
Why doesn't /*+ NO_INDEX*/ hint , affect the "execution plan"?
I have a sample table with below structure :
create table mi_dimcustomer
(customer_num number(10),
<other columns> <data types>)
And there is a unique index on column customer_num. I'...
1
vote
1answer
53 views
DISTINCT forces re-sorting of data on PostgreSQL
I have a table recording transactions between accounts, looking like this:
txid varchar(64)
account varchar(64)
ts timestamp
amount numeric
(there are more fields but they are irrelevant for ...
1
vote
1answer
196 views
Why is Index causing bad estimation and execution plan
SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) -
ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0)
FROM [Credit] M WITH(NOLOCK)
INNER JOIN [Transaction] T4 WITH(...
0
votes
1answer
41 views
Facing error "cannot fetch plan for SQL_ID" while trying to capture the execution plan
I'm trying to capture the execution plan of my query on PL/SQL developer as you can see below :
select *
from vmi_dimcustomer t1
inner join vmi_factcustomer t2
on t1.customer_num = t2....
1
vote
0answers
51 views
Postgres failing to consistently use best partial indexes when multiple are present
In Postgres 10.17, I have two queries with different WHERE clauses and two partial indexes (idx_s_id and idx_w_id) optimized for each one respectively that should allow index only scans for both ...
