Questions tagged [execution-plan]

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

96 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
673 views

SQL query fills up RDS free storage space after upgrade to MySQL 5.7

After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space. The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS ...
4
votes
0answers
125 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 ...
3
votes
0answers
119 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 | ...
3
votes
1answer
180 views

What Triggers sp_recompile in database without triggers, stored procedures, etc?

In a database with only tables and indexes (ie no stored procs, functions, triggers, etc). We get a call to sp_recompile that blocks table selects / updates randomly (ie once a month but in a highly ...
3
votes
0answers
68 views

Poor query planning in Postgres 10

The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join. Here's my ...
3
votes
0answers
289 views

Searching between two BIGINT

I have a table defined as: CREATE TABLE [dbo].[IPS_INTEGERS]( [id] [int] IDENTITY(1,1) NOT NULL, [network_start_integer] [bigint] NULL, [network_last_integer] [bigint] NULL, [...
3
votes
0answers
256 views

Postgresql row-level security generates different query plan from manually inserted WHERE clause

(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
3
votes
0answers
107 views

Bad Plan after a couple of days

I have this query that runs fine for a couple of days (from the application) and then slows down to where it can't even finish. If I rebuild the plan, it flies. There is a signifigant amount of data ...
3
votes
0answers
430 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
3
votes
1answer
539 views

Query with conditional JOIN running slow

I have a query the first part of which looks like this - With CTE AS ( select DISTINCT A.col1, A.col2, . . . from tableA A JOIN tableB B ON ( ...
2
votes
0answers
42 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 ...
2
votes
0answers
27 views

Is it possible to replace count distinct inside Correlated sub-queries?

I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)... Is it ...
2
votes
0answers
643 views

Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?

I run the following query against Postgres 9.5 which returns article information along with the currently valid price: WITH selected_article_rm(id) AS ( VALUES ('80512e52-a406-4a9b-a8df-...
2
votes
0answers
111 views

Plan cache mystery

I work on SQL Server 2016 and i had severe performance issues with few queries. So I posted several posts in here and got solution/suggestion etc and greatly improved the execution time and learned a ...
2
votes
0answers
79 views

Problem query - execution plan

I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is ...
2
votes
0answers
373 views

Poor SQL query optimization of Recursive CTE with iTVF, environment specific

I'm developing a recursive CTE which utilizes an inlined TVF. In our dev environment, the recursive CTE executes exactly as expected. In our production environment, the first iteration of the CTE ...
2
votes
0answers
61 views

What can I do to determine why my query occasionally hangs (currently hung for over 3 days)

I have a query in my data warehouse load that occasionally hangs. Traditionally, it takes only a few minutes to run, however sometimes it can take a few hours. I'm unsure whether this is the query ...
2
votes
0answers
175 views

SQL Query produced by Umbraco app & NHibernate ORM - help!

https://www.brentozar.com/pastetheplan/?id=SJ1BhSun This is a sample of a query that this CMS called "Umbraco 5" runs against its backend DB, which is currently hosted on SQL 2008R2. This query runs ...
2
votes
0answers
320 views

Execution plan of query inside pl/pgsql function

Briefly, we have a pl/pgsql function which collects some intermediate data in several temporary tables ("on commit drop") and then returns REFCURSOR which joins those temporary tables with some static ...
2
votes
0answers
320 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
2
votes
0answers
385 views

Multi-Statement Template Plan Guide

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
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
36 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 ...
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
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
0answers
22 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 ...
1
vote
0answers
27 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 ...
1
vote
0answers
58 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 ...
1
vote
0answers
17 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 ...
1
vote
0answers
29 views

Understanding where/how to use the “selectivity of an equality predicate between two columns”

This lecture on "Result size estimation" defines a formula of equi-joins based on Selinger paper. result cardinality = max number of tuples possible * product of all selectivities 1 - max number of ...
1
vote
1answer
135 views

MariaDB Inner join is slow

Here is my SQL (with explain): EXPLAIN SELECT * FROM GBI gbi INNER JOIN VF_KRED kred ON gbi.vendor = kred.vendor; Giving below the outcome: Now, the select SQL takes ~10 seconds to produce the ...
1
vote
0answers
329 views

Converting show-plan XML to graphical view plan

I am using the performance dashboard to set up baseline monitoring for my inventory of SQL servers. As per the performance dashboard available within SSMS, when i click on any query to view the plan ...
1
vote
0answers
149 views

Is there a way to make postgres count planned number of rows when using recurcive CTE?

Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle: create table test_recursive ( id serial primary key, parent_id int null references test_recursive, name text ...
1
vote
1answer
223 views

MariaDB uses “wrong”/irrelevant Keys on large table

We have migrated our database from a local hosting company to AWS. Now when we run a particular query on AWS it finishes in about a minute (processlist says "sending data") where on the old server the ...
1
vote
0answers
106 views

Converting an XML field to nvarchar causes warning on main SELECT in explain plan

I'm getting a warning on my main SELECT on one field in the explain/execution plan of my query on the following: SELECT ISNULL(CAST(p.loadDiagnostic as nvarchar(4000)),'''') FROM myTable p This ...
1
vote
0answers
74 views

Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan

I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing. I am getting this warning in an execution plan and when i checked ...
1
vote
0answers
56 views

SQL Server Execution Plan

In the following queries the Student table has Clustered Index on StudentID column, and the Enrollment Table also has a Clustered Index on EnrollmentID as well as Non-Clustered index on (SessionID, ...
1
vote
1answer
85 views

Different execution plans and joins using synonym or table

Edit: We found the leading cause. If you join tables with the same name but in different schemas you can get this result. Same with joining two tables using the same alias (whyever this works at all)....
1
vote
0answers
388 views

Why does this SQL Server execution plan contain a snapshot cursor for a select statement?

Here is the situation. I have a fairly simple query. It returns ~22k rows: select * from tbl_A When I run this query in SSMS (2017 7.6), I get a fairly simple plan: SELECT <= Clustered Index Scan (...
1
vote
0answers
37 views

Impact of numerous indexes on query optimizer for a delete statement

We have a 10GB table with 30GB indexes on it. The table consists of a few dozens of attributes a primary key on the id (varchar(32) type) and a dozen of indexes on anything but id key (except the ...
1
vote
0answers
122 views

bit columns generating warnings - how can a cast in the field list affect the cardinality estimate?

A few days ago I had a question about warnings in the execution plan Type conversion in expression may affect “CardinalityEstimate” - on a computed column? and that was solved, thanks Paul. Now I ...
1
vote
1answer
33 views

How to index cube style queries with changing aggrigations

I've got a query that emulates the CUBE function. This is many different aggregations unioned together. select x,y,z, sum(a) from tbl group by x,y,z union all select x,'all',z, sum(a) from tbl group ...
1
vote
0answers
194 views

why the queries run by a specific user are slow?

Here you can see how to save the contents of the default trace into a table. I have been saving the contents of the default trance into a table in my DBA database. The table is called [DBA].[dbo].[...
1
vote
0answers
67 views

Execution plan using index that no longer exist

I have some odd behavior with an execution plan using an index that no longer exists. let me give you the background. We had some high CPU going on so I had a look what was happening and found a ...
1
vote
0answers
1k views

Different execution plan for the same query on the same Oracle 12cR2 schema

I'm getting different execution plans for the same query running on the same Oracle 12cR2 instance. The query uses only literals (no variable binding) and takes about 10 seconds when I run it from my ...
1
vote
0answers
46 views

Measuring 'data items accessed' by index technique to retrieve item (PostgreSQL)?

In the example I have attached, a btree is being used to search for the number 19. With PostgreSQL execution plan, buffers read/hit are being shown. Which would they be in the example I have attached? ...
1
vote
0answers
588 views

Why is the Postgresql planner choosing the slower merge join (instead of hash join)

I'm using Postgres 9.6 on AWS I've run into an issue where depending on the data range for the query the planner will chose to do merge joins (requiring slower sort operations). When the date ...
1
vote
1answer
108 views

Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions

I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys....
1
vote
0answers
74 views

Using TempDB in SP causes to compile plan PER CONNECTION

uses sp_blitzcache by Brent Ozar SQL SERVER 2012 (11.0.6020.0) Steps to reproduce: create table b (b int) create table #b (b int) create procedure Y as begin insert into b select 1 select * from b ...
1
vote
0answers
42 views

Index added to view affecting other view

I created two views: vTest1 vTest2 The create procedure for each is: CREATE VIEW [dbo].[vTest1] WITH SCHEMABINDING AS SELECT Person.EmailAddress.EmailAddress, Person.Person.Title, Person....