Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
816
questions
1
vote
2answers
62 views
Disable top (n) Sort Optimization for a specific view
I have a a view with a complex logic and three levels of deepness (nested views). Because of the complexity I can't paste the execution plan.
As the purpose of the view is to provide some business ...
0
votes
1answer
52 views
Understanding about Execution plan and Index in SQL Server
I’m Christine and I have been studying for Data Analytics, I would like to know if you could help me clarify what I'm drilling down this execution plan as below.
About detail of execution plan, you ...
4
votes
2answers
191 views
Execution Plan Warnings cardinality xml data type
I have the following statements:
-- 1st
DECLARE @AuditParameters XML = (
SELECT
1 AS AccountID,
2 AS CategoryID,
3 AS CategoryAttributeID,
'4' AS SyncBatchGUID
...
0
votes
0answers
49 views
Why additional plans are created under simple parameterization?
I have a table which has 2163 rows and clustered index on id column.
I run the following queries:
select id
from ticket_attach
where id = 1
select id
from ticket_attach
where id = 2
select id
from ...
0
votes
0answers
64 views
Explaining about SQL Server query Execution Plans and understanding Clustered and Nonclustered Indexes
I'm Christine and I'm a newbie. I would like to know if someone could help me clarify what I'm drilling down this execution plan as below :
Here's query generated from SQL server that is accompanied ...
1
vote
0answers
17 views
Why is PostgreSQL prefix/range search finicky?
Configuration
PostgreSQL 11.4
SET default_statistics_target=1000;
Data
The scenario is that there are "computers" each with "files", which each have path (ordered text list) unique to that ...
0
votes
0answers
24 views
SQL Query assistance (plan link pasted)
I have a query that I was able to take down from 6 minutes to 3 minutes -
Just curious if anyone can see if there is anything additional I can do w/ this query or if I am missing something simple.
...
0
votes
1answer
29 views
Postgres10 picks very slow query plan, when faster plans seem available
we are using Postgres 10.
We are seeing a situation where, for the same query, with one value it picks a super ineficient query plan, but with another value it picks a good query plan.
SELECT
"...
0
votes
0answers
19 views
Properties of grouping in relational algebra
The properties of relational algebra (commutativity, associativity, distribution) allow us to take a relational algebra expression and transform/rewrite it into another one which is logically ...
4
votes
2answers
407 views
Why is there an aggregate in this UPDATE plan?
Given this
declare @Data table (id int, fact char(1));
declare @Summary table (id int, collected varchar(99));
insert @Data(id, fact)
values
(1, 'a'),
(1, 'b'),
(2, 'c'),
(2, 'd'),
...
1
vote
0answers
26 views
Slow query plan with small LIMIT [duplicate]
A query generated by our web framework leads to different query plans, depending on the number of rows to be returned. The problem lies within the combination of a low LIMIT and the ORDER BY clause:
...
0
votes
1answer
29 views
Query with left join not using index
The below query is taking about 1 second to run
SELECT `taxi_driver_mapping`.*,
concat("dpk00",
taxi_driver_mapping.mapping_payment_plan_id) AS package_text,
`people`.*,
...
1
vote
1answer
65 views
For a complex query how do i increase the time SQL Server searches for the best execution plan
Our system generates SQL with a very large number of tests. I suspect on some occasions it is taking too long to find the ideal execution plan. So SQL just picks the best plan it has found so far ...
0
votes
2answers
36 views
Is it possible to tell if a database has AUTOMATIC_TUNING switched on?
In SQL Server 2017 is it possible to tell if automatic tuning is switched on? This relates to tuning of the plan. So the database automatically monitors itself and if the plan gets worse it goes back ...
1
vote
1answer
26 views
How can I see the plan for ONE statement in a stored procedure?
I've got some SQL which I've run several times a day in development, generally getting execution time of 3-8 minutes.
Now I've wrapped it in a stored procedure (with no parameters), the performance ...
1
vote
2answers
51 views
Are there side effects to Postgres 12's NOT MATERIALIZED directive?
I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the ...
0
votes
1answer
63 views
What causes sql server to generate a new plan for a stored procedure?
We've ran into an issue in production a couple of times now where it looks like a stored procedure gets a new, bad plan and starts causing havoc. Unfortunately, we weren't able to capture the plan ...
0
votes
1answer
66 views
Very slow query despite index being used
I have this table, created to hold tons of modbus measurements that comes from my devices:
CREATE TABLE public.tv_smartdevicemeasurement_modbus (
measurement_id serial NOT NULL,
insert_time ...
-1
votes
1answer
46 views
Index Update with Eager Spool and Sort operators in Execution Plan
After an UPDATE statement on a large table, the execution plan shows updates of indexes (all non-clustered) that include the updated columns.
Before each index update, there is an Eager Spool ...
4
votes
2answers
650 views
Query execution plan is horrible until Statistics are Updated
I hope you guys can help me here.
Our application polls a message table every 3 seconds looking for notifications to send out. This works well on all our Customers (single-tenant DB) except one. They ...
11
votes
2answers
597 views
Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?
Setup
DROP TABLE IF EXISTS #EmptyTable, #BigTable
CREATE TABLE #EmptyTable(A int);
CREATE TABLE #BigTable(A int);
INSERT INTO #BigTable
SELECT TOP 10000000 CRYPT_GEN_RANDOM(3)
FROM sys....
3
votes
1answer
53 views
Why does ORDER BY NULLS LAST affect the query plan on a primary key?
Using PostgreSQL 11, I have the following table with around 450 million rows:
postgres=> \d+ sales
Table "public.sales"
...
1
vote
2answers
77 views
Overestimated query resulting in poor performance - TVF with xml processing
Today i asked question about bad query performance. That query had a lot of joins over a lot of views. I think i have found which view is a trouble for SQL server. Here is actuall query plan for ...
0
votes
1answer
61 views
Optimize query that accoridng to plan produces over 500 billions rows - Actual result is only 400k rows
I'm trying to improve DB performance. I'v noticed a query that according to the plan it produces a lot of rows but i know for fact there are no more than 400k rows in the table after that insert. Why ...
0
votes
1answer
72 views
Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?
If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored ...
1
vote
1answer
111 views
How to use index for simple time range join?
I have a large (~ 100 million rows) timeseries table t_16 in Postgres 11.5 where the primary key is a field abs_date_time of type timestamp.
This is a follow up of this question:
Why does a CTE ...
2
votes
2answers
79 views
Postgresql Query plan different on two servers
I'm having some strange issue of running the same query between prod and dev and receiving a bit of a different query plan and I'm not sure why. I'll try to cover everything I can, but if I forgot ...
6
votes
2answers
114 views
Why do we have Top N Sort in this example?
In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top?
use tempdb;
go
create table dbo.t1 (id int identity primary key, v int);
...
0
votes
1answer
34 views
Is there a way to figure out an executing query's plan handle by the query text?
Is there a way to determine the plan handle for the query plan of an executing query by that query's text?
Perhaps in any of the DMVs?
(I'm trying to find the query stats on an ad-hoc query.)
0
votes
1answer
24 views
How do I make Postgres use an index when using IN or ANY?
I have a query such as:
select col1 from mytable where col1 in('a','b','c')
But running such a query yields a Seq Scan per EXPLAIN
If I have an index on col1, why does Postgres not do an index scan?...
2
votes
1answer
37 views
How to get more physical details when executing an SQL query?
I want to get some physical features about a SQL query execution with PostgreSQL, like
Read/write page accesses
I/O operations
Response time
Sequential I/O throughput rate
Deadlocks
Pages scanned ...
6
votes
1answer
105 views
When are non-parameterized, non-trivial, adhoc query plans reused
I'm currently investigating an application that seems to generate 99% adhoc query plans against the database it is querying. I can verify this by running the following statement to retrieve a summary ...
7
votes
1answer
475 views
Is there a reliable way to determine how long it took the optimizer to generate a query plan?
I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the ...
9
votes
1answer
230 views
Is it possible to re-use a query plan from the plan cache from one database in another database?
For example, if I hit the sys dynamic views to select a specific query plan, am I able to insert that query plan into another database's plan cache, that runs the same exact query? (I know the query ...
1
vote
1answer
52 views
How to profile the PostgreSQL query planner diagnose what is causing it to take up large amounts of memory and time
I'm fighting with a specific "generated" query (the SQL is generated from a proprietary system, and I won't be able to share it or the execution plan here), and running into simply massive memory ...
9
votes
2answers
499 views
Xml index, slow count of rows
I am not getting index seek in the following scenario.
Instead of inserting <Number>xxx</Number> in xml column like in this post Why is the secondary selective index not used when the ...
1
vote
1answer
51 views
Query plan for a dynamically built `query_int` using `format()`
To make things simple here is a statically built version of the query that runs perfectly fast:
SELECT * FROM media WHERE tag_ids @@ '123&321'::query_int ORDER BY rating DESC LIMIT 20;
The catch ...
1
vote
2answers
138 views
Why won't SQL Server 2012 use my “better” execution plan when provided via the USE PLAN hint or through a stored query plan?
I have a 3rd party application that I cannot change that runs a "bad" select query that can run very slowly in some circumstances (we have killed it after 6 hrs on occasion). This is after we've done ...
1
vote
3answers
108 views
Filtered index is not used when variable in WHERE condition
Why MS SQL Server refuse using supporting filtered index in this scenario?
-- demo data
CREATE TABLE #Test (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY
,Col1 NVARCHAR(36) ...
2
votes
0answers
39 views
Advantage of querying data outside a filtered index
Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?
My playground:
Table with billion rows and 100 columns
Unique clustered ...
1
vote
2answers
94 views
How to check actual execution plan of a query after it has been executed
In SQL Server, how can we see actual execution plan of a query, after it has been executed?
As per my knowledge, only estimated execution plan (not actual execution plan) are stored in plan cache. is ...
1
vote
0answers
15 views
Understanding PostgreSQL Execution Plans
I'm trying to understand the motivation of PostgreSQL 12 query planner here.
Database Structure
CREATE TABLE subjects
(
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
);
CREATE TABLE ...
2
votes
1answer
115 views
How to rewrite CASE expression to short-circuit evaluation
I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase?
Demo data:
CREATE TABLE #...
0
votes
1answer
81 views
Worse query performance even after improving the query by making it to run in parallel mode
I have a SQL Query which gets generated from application using EFF.
It uses a view and this view has joins to multiple tables in there.
The query runs slow and when i looked at the execution plan,i ...
2
votes
1answer
42 views
Improving the query standards and performance run time
For one of our Vendor based SQL server database, they have created certain views for users to query. Since i am new to SQL querying, please help me understand the below format of SQL and how can i ...
0
votes
0answers
65 views
Explicit conversion to avoid the implicit conversion warning [duplicate]
I have a TVF function which has the below code in it.I stripped down the function and has the logic as shown below.
WITH X
AS (SELECT cis.ProductionStatus AS CurrentDiaryStatus,
...
4
votes
3answers
459 views
SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week
I have a very simple query:
INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
ORDER BY CommentID DESC
against this table:
CREATE ...
0
votes
2answers
121 views
Horribly performing query, how to fix
select
user_id,
count(id) as unread_count
from
notifications
where
is_read = false
and user_id in(select(unnest('{200 user IDs}' :: bigint[])))
group by
user_id;
Problem is, ...
-1
votes
1answer
55 views
Query Plan for a linked-server query getting cleared from plan cache after every execution
I have linked server query which runs of a view created in the remote server.
Whenever i run the query i can see that a Prepared plan gets created in the plancache.
But that gets removed from the plan ...
0
votes
1answer
67 views
Query runs in serial and parallel mode in two identical databases on the same instance [duplicate]
I have two databases in the same SQL Server instance.
One is a test database with a smaller number of records and total size of 500GB.
Other one is production database with a size of 2.2 TB.
Both ...
