Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
141
questions with no upvoted or accepted answers
5
votes
0
answers
462
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
...
- 167
5
votes
0
answers
408
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 ...
- 163
4
votes
0
answers
1k
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 | ...
- 523
3
votes
0
answers
197
views
Execution Plan changes between good and bad query plans (Azure SQL Server)
I've done my best to follow Why is my query suddenly slower than it was yesterday?
The ShowPlanXML parses fine with SQL Sentry Plan Explorer but not via brentozar.com/pastetheplan (not sure why) so I ...
- 175
3
votes
0
answers
266
views
Why adding select into table is changing execution plan?
I am having following query:
DECLARE @VersionsIds TABLE(VersionId INT)
INSERT @VersionsIds(VersionId) VALUES (520)
SELECT
vEps.MasterPolicyId
-- INTO #Temp
FROM [Events].[vwEventMasterPolicy_Search] ...
3
votes
2
answers
1k
views
Dealing with suboptimal postgres query plans related to nested loops
I'm developing an application that dynamically builds up SQL queries and executes them against postgres 13.
Some of the queries are very slow because they use nested-loops but the query planner ...
- 131
3
votes
0
answers
414
views
MariaDB - Query to find unused indexes
I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this ...
- 69
3
votes
0
answers
359
views
Time to generate execution plan for select into from a view takes significantly more time than select
I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've ...
- 131
3
votes
0
answers
89
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 ...
- 131
3
votes
0
answers
1k
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-...
- 246
3
votes
0
answers
653
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,
[...
- 39
3
votes
0
answers
111
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 ...
- 1,597
3
votes
0
answers
445
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 ...
- 163
3
votes
1
answer
856
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
(
...
- 41
2
votes
0
answers
160
views
Postgres: One-to-many relationship with WHERE, ORDER BY and LIMIT not scalable?
I'm currently using Postgres 12.
A few years ago, I set up the following model: a lot table with a one-to-many relationship to a line table. My idea was to mutualize data that are common for multiple ...
- 21
2
votes
0
answers
17
views
set options for replicating slow queries in SSMS
I am investigating a slow ad-hoc query(about 45 seconds) from our application. I am not able to replicate the slowness in ssms however to investigate indexing, reads, etc. I've attempted to use the ...
- 255
2
votes
0
answers
101
views
Huge Difference in IO and CPU for same query
I saw that same query got more than two execution plans. Query is included inside procedure which runs once a day every day. All tables in query got from 2000 to 50000 additional records every day. I ...
- 261
2
votes
0
answers
393
views
PostgreSQL predicate not pushed down (through join conditions)
Consider the following data model in a PostgreSQL v13 system;
Here, parent table dim contains a small set of reference data, and child table fact contains a much higher volume of records. A typical ...
- 121
2
votes
0
answers
462
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 ...
- 131
2
votes
1
answer
61
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 ...
- 503
2
votes
1
answer
1k
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 ...
- 121
2
votes
0
answers
69
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 ...
- 21
2
votes
0
answers
600
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 ...
- 46
2
votes
0
answers
132
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 ...
- 1,295
2
votes
0
answers
87
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 ...
- 21
2
votes
0
answers
766
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 (...
- 700
2
votes
0
answers
569
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 ...
- 141
2
votes
0
answers
72
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 ...
- 213
2
votes
0
answers
185
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 ...
- 794
2
votes
0
answers
451
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 ...
- 21
2
votes
0
answers
344
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 ...
- 500
2
votes
0
answers
531
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' ...
- 131
1
vote
0
answers
44
views
Compile time of update query is high on sqlserver 2019 vs sqlserver 2016
A dev team announced performance degradation after migrating their database from SQLServer 2016 to SQLServer 2019. So I started tracing the database using SQL profiler.
It turns out duration, cpu ...
- 11
1
vote
0
answers
55
views
How to reduce high cost of sort on execution plan
I have a very large query with three UNION ALL and it takes 30 minutes to execute.
Analyzing the execution plan I see that it has a high cost in SORT and Filter.
I tried to generate the showplan in ...
- 21
1
vote
0
answers
23
views
Improve cardinality estimation when two joined tables have a condition on an identical column
I have a table foo, with an association table foo_associations with columns foo_parent and foo_child. foo has an column called department_id. Any association made through foo_associations is ...
- 259
1
vote
0
answers
75
views
How do I test the estimated query plan of a portion of a trigger that uses inserted/deleted?
I'm performance tuning a SQL Server trigger that has several statements using the "inserted" and "deleted" tables. How can I break out those individual queries from the trigger ...
- 301
1
vote
0
answers
21
views
Wrong query plan for GIN index
I have the following repeatable case.
I have an uuid[] column with a gin index, which stores all unqiue uuids, without doubles and repeats.
Test table:
CREATE TABLE test
(
id serial not null, ...
- 11
1
vote
0
answers
69
views
Optimizer Occasionally chooses a bad plan - how to tune query to ensure it is consistent
I have a query in my environment which occasionally gets a "bad" plan and takes a long time to run.
We can see from the query store output below that the query normally uses plan 25493651 ...
- 1,741
1
vote
1
answer
2k
views
MySQL does not use index, does full table scan instead
EDIT: See further developments below.
I have an InnoDB table with the following schema:
CREATE TABLE `data` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`app_id` CHAR(27) NULL DEFAULT NULL,...
- 155
1
vote
2
answers
91
views
How to make Postgres use the correct index here?
Here's a test setup:
CREATE TABLE t (
filename int,
cropped bool not null default false,
resized bool not null default false,
create_date date not null default '1970-01-01'
);
INSERT ...
- 537
1
vote
1
answer
40
views
Azure MI - clustered and nonclustered indexes with the same columns - different execution plans
I have a problem to understand why I am getting 2 different execution plans based on whether clustered index is involved or not. It's happening in Azure managed instance, I have no possibility to test ...
- 152
1
vote
0
answers
381
views
Query Planner has bad row estimate for second-degree joined link table and uses inefficient nested join
Note: This is a toy database example I came up with and a simplification of a real design, so please disregard from commenting about the way the database is structured.
Each Group has precisely one ...
- 259
1
vote
0
answers
451
views
Which is the better way to select the last entry row from a table in PostgreSQL?
I need to test an attribute from the last entry row from a table to trigger a trigger function on PostgreSQL 9.6. Just below, there is a little piece of my table.
It's doesn't have a timestamp column, ...
1
vote
1
answer
95
views
Postgresql (11.9) LIKE query not using text_ops index
I've seen related questions but I think this should be working and isn't. I have a text_ops function based index on column to do LIKE searches, but it only picks up the index with an equals:
CREATE ...
- 111
1
vote
0
answers
127
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 ...
- 188
1
vote
0
answers
72
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....
1
vote
0
answers
55
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 ...
- 45
1
vote
0
answers
48
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&...
- 111
1
vote
0
answers
129
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 ...
- 11
1
vote
0
answers
165
views
Actual Number of Rows Significantly Lower than Estimated
My Problem
I am having an issue with a update query, where the execution plan has a bad cardinality estimate. It is estimated way more rows (14000%) than there should be when it does an Index seek on ...
- 45