Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
11 views

Understanding “bitmap heap scan” and “seq scan”

I'm trying to understand that which scan is efficient, Here is the explain for the query, EXPLAIN SELECT * FROM 'myTable' WHERE cast(TIMESTAMP AS VARCHAR(100)) > CAST(1586415955169 AS VARCHAR(100))...
0
votes
1answer
45 views

Querying a table with 200+ million rows by filtering on the clustered index AND additional column is super slow

We have one table that has clustered index on two columns: first one is NVARCHAR(50) and second one is on DATETIME2(3) type. There are few other columns. When we query the table with WHERE clause on ...
1
vote
1answer
14 views

Can I tell mariadb which query plan to use for a given query?

I have a query which gets different query plans over time and one of them takes less then a minute to execute, the other one takes days. Can I tell the db to use a given plan (taken from explain or ...
1
vote
1answer
40 views

why would a simple query have poor performance on MS SQL Server 2014 or 2014SP3 when on other versions it performs well?

I am running the following query on MS SQL Server 2014 (and also on 2014SP3) SET NOCOUNT ON CREATE TABLE #GUIDs( PartyNames_GUID UNIQUEIDENTIFIER NULL, Party_GUID UNIQUEIDENTIFIER NULL, ...
0
votes
1answer
47 views

Query performance discrepancy between Azure and on premise SQL Server

I have the same database on both SQL Azure and on premise. Most of my queries run in similar time on the two instances but I have a handful of queries where the performance difference is on the order ...
0
votes
0answers
30 views

adding case statement in where clause leads to poor plan choice

We have a large number of procedures that use case statements in where clauses--essentially, we want to run the same procedures at different times, and we set "flags" in the database to change the ...
0
votes
0answers
27 views

SQL Server - is there a way to convert OR conditions to UNION to improve performance (as part of execution plan, under the hood)? [duplicate]

Let's assume we have following query (greatly simplified, of course): SELECT * FROM MyTable Where A or B The query takes about 30 seconds to run When I rewrite the query as SELECT DISTINCT * FROM ...
0
votes
0answers
30 views

MongoDB Explain plan - elapsed time breakdown for executionStats

I have run an explain plan for a query, and am a bit confused with the timings of each stage, and how they aggregate to the total elapsed time. Following command was run to generate the statistics, ...
1
vote
0answers
21 views

DELETE a single row from a table with CASCADE DELETE picks a slow plan… but not always

Schema Summary A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables' FKs are indexed. This looks like ...
14
votes
2answers
3k views

SQL estimates are way off on DELETE statement with Triggers on huge tables

I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
1
vote
1answer
53 views

Memory Grant Warning on what appears to be a better query?

OK, I was looking at potentially reducing this (as more of an exercise than a need mind you) and it seems that Im getting this warning on what appears to be a better performing query and not on the ...
0
votes
2answers
74 views

Help with execution plan analysis

I need some help interpreting this execution plan - https://www.brentozar.com/pastetheplan/?id=SJ5NYLLSL Whenever this query is ran it uses up a huge amount of CPU and kills the server. I have ...
0
votes
1answer
77 views

Unable to get actual execution plan for SQL Server 2014 SP3

I was reading this blog post https://techcommunity.microsoft.com/t5/sql-server/using-xevents-to-capture-an-actual-execution-plan/ba-p/392136 and based on same created extended event CREATE EVENT ...
1
vote
2answers
110 views

Performance - Looking for Some Advice on Improving this Query

I've been looking into a performance issue for the last few days and although I've improved the query runtime from 48 seconds to 11 seconds, I feel that it can be improved even further. The issue is ...
5
votes
2answers
295 views

Oracle does not use transitive closure to add additional predicate

Presented with a query like this: select * from a, b where a.val = b.val and a.val = 1 Oracle optimizer can use transitive closure property and combine two predicates a.val = b.val and a.val = 1 to ...
0
votes
0answers
31 views

How can I tell which queries and nonclustered indexes are doing bookmark lookups on my primary key for a specific table?

Is it possible to get the queries and the names of the nonclustered indexes in my database which are doing bookmark lookups / key lookups in the execution plans? Would I have to go through the plan ...
0
votes
0answers
35 views

Different execution plan - Delete TOP 10 vs Delete TOP 1000 [duplicate]

Was troubleshooting a delete statement that periodically delete old data from a monitoring table and noticed different behaviour with different TOP numbers and wanted to understand why. Delete ...
1
vote
1answer
67 views

Is it normal for it to take up to a minute for an execution plan to be generated (even an estimated one) on simple queries?

Once in a while (but uncommonly) my SQL server will take what seems like an odd amount of time to generate an execution plan. It just took 37 seconds to generate an estimated execution plan for the ...
4
votes
4answers
256 views

Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR

I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database: SELECT * FROM Users u LEFT JOIN Comments c ON u.Id = c....
3
votes
0answers
81 views

PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice

This is the story if a nightmare... I have this table: Table "subscriptions" Column | Type | Collation | Nullable | ...
2
votes
1answer
72 views

Different execution plans in SQL Server for same query but different offset

I am doing performance experiments regarding the fulltext search feature of Microsoft SQL Server using the stackoverflow database. I have a (seemingly simple) query that results in different ...
0
votes
1answer
41 views

PostgreSQL bad plan: the frequency of a tag depends on the project!

I try to simplify a problem that I've been investigating for weeks. Suppose that you have a large table like this: SUBSCRIBERS ---------------------------- id | project_id | tags | ... --------------...
1
vote
2answers
45 views

Why does this stored proc cause a clustered index scan, but seek when using OPTION RECOMPILE?

I think I may know the answer based on my research, but am looking for confirmation on how/why the engine compiles the plan the way it does with Parameters being passed in: @ID int ,@OtherID INT ...
0
votes
1answer
33 views

get oracle execution plan by sql_id

Is it possible to get an execution plan of currently running query by sql_id? I couldn't succeed with: select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (sql_id=>'b3x6apqyskn7x') report from ...
3
votes
1answer
109 views

Aurora PostgreSQL database using a slower query plan than a normal PostgreSQL for an identical query?

Following the migration of an application and its database from a classical PostgreSQL database to an Amazon Aurora RDS PostgreSQL database (both using 9.6 version), we have found that a specific ...
6
votes
2answers
209 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 ...
4
votes
2answers
202 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
50 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
68 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
19 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
1answer
32 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
34 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 ...
5
votes
2answers
424 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
30 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
51 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
28 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
80 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
66 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
71 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
53 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
651 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
607 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
82 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
112 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
137 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
87 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 ...

1
2 3 4 5
17