Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,125
questions
0
votes
3
answers
62
views
Window function with subquery results to unexpectedly slow query plan
This is my victim query
SELECT *,
lag(quantity)
over (partition by product_id, size_id, warehouse_id order by size_id, warehouse_id, created_at ASC) -
quantity as orders
FROM ...
- 1
2
votes
3
answers
177
views
EXPLAIN ANALYZE does not show what takes time with my GIN index scan
Context
I have a table named companies_establishments that holds ~33M rows.
I created a GIN index with trigrams, so I can make LIKE queries much faster.
CREATE INDEX companies_establishments_id_index ...
- 123
4
votes
1
answer
99
views
SQL Server generates a query plan with parallelism and TOP statement
I have two simple queries:
SELECT TOP(20) * FROM Clients ORDER BY City
SELECT TOP(20) Id, Name, City FROM Clients ORDER BY City
In the first case I get a result like this
Id
Name
City
many more ...
2
votes
2
answers
106
views
Query Store plan does not include parameter list
I use the query store to get an overview of executed queries on our databases and to generate performance tests where I execute those queries in parallel on multiple hosts.
My task is to test how many ...
- 345
2
votes
0
answers
84
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
0
votes
1
answer
45
views
Query using VIEW very slow when the same query is fast using WITH
Given this view
CREATE OR REPLACE VIEW public."SellMeta"
AS WITH wh AS (
SELECT "WWHH".id,
"WWHH".url,
"WWHH".name,
...
- 101
0
votes
0
answers
43
views
Help me in optimising the query which takes almost 50 seconds
Please help me optimise the following query running on MySQL 8.0.28 Community Edition.
explain select id,
created_at,
data,
deleted_at,
entity_id,
...
6
votes
1
answer
510
views
How to reduce query size with many repeated UNION subqueries?
I use Postgres 13 and have a table defined with the following DDL:
CREATE TABLE item_codes (
code bytea NOT NULL,
item_id bytea NOT NULL,
time ...
- 185
3
votes
1
answer
516
views
How To Optimize a Query That Aggregates Then Duplicates a Large Number of Records in a Single Table
In the following post, J.D. brings up that I have a poorly performing query.
I am running this query on SQL Server 2019 Standard Edition (query plan was generated in Development Edition)
Let's take a ...
- 205
0
votes
0
answers
25
views
PostgreSQL query times out when selecting a smaller amount of data (different query plan)
PostgreSQL 15.2. We have a table like this (simplified):
create table behavior.event_59 (
event_id integer not null primary key,
second_offset integer not null,
-- ... more columns
)
...
- 188
1
vote
0
answers
45
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
10
votes
1
answer
1k
views
SELECT Statement with , * in column list is faster than same statement without *
Situation
When querying a database with a SELECT statement with a defined set of columns, the results are received in around 21 seconds.
If there is an additional asterisk (, *) at the end of the list ...
- 16.2k
3
votes
1
answer
105
views
Improving query response speed when joining a non-spatial table with a spatial table on a non-spatial column
I have a table of frequently updating events that affect certain geometries. The geometry areas are fixed, but there may be an event affecting multiple areas at once, so there's a many-to-many ...
- 33
1
vote
2
answers
136
views
Lots of "key value" joins causes SQL Server query to exponentially slow down
I am migrating some data that's stored "key value" style, from an attributes table that uses an Object ID and an Attribute Type as a Clustered Index (I've also tried as a nonclustered index):...
- 33
0
votes
1
answer
34
views
What makes the optimizer insert a sort operator in the plan
What are the reasons that the optimizer chooses to insert a sort operator in the plan (to satisfy a stream aggregate or a merge join, etc.) instead of going for hash match or hash join?
I have now see ...
- 743
0
votes
0
answers
27
views
Hierarchical query to select `children` from `children`
I have the following table mytable where id is a unique identifier at a given r (resolution) and children is an array of unique id's of r + 1.
id
r
children[]
0
2
{0,2467972595799031808,...
- 263
2
votes
0
answers
16
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 ...
- 253
1
vote
0
answers
19
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 ...
- 249
0
votes
0
answers
46
views
Why is Live Query Plan not available on my SQL Server 2019?
Based on this article on Query Profiling Infrastructure SQL Server 2019 should have the lightweight profiling infrastructure v3 enabled by default. However, when I try to display Live Execution Plan, ...
- 89
2
votes
1
answer
142
views
Why changing limit triggers different query plans in postgresql?
Changing only limit from 40 to 50 in following query triggers different execution plans. And unfortunatly one I needed is much slower. So the question is: why this happening and how can I force ...
- 123
1
vote
1
answer
53
views
Postgresql - long column select/projection list leads to poor performance
I have a rather structurally "simple" query that consists basically in these clauses:
SELECT
[650 columns here]
FROM
MAIN_TABLE
(Several Joins)
WHERE
(Some conditions in ...
- 155
6
votes
1
answer
446
views
Why does SQL Server sometimes estimate that joining onto an empty table will increase the row count?
I recently came across an issue where tSQLt tests were taking a long time to run.
The procedure under test was doing a 38 table (!) join (with 37 faked tables and a table valued parameter).
Only two ...
- 80.4k
0
votes
0
answers
207
views
How to avoid cluster index scan?
I've a table with a few millions of records. Here's the table structure:
Column_name
Type
Computed
Length
Prec
Scale
Nullable
TrimTrailingBlanks
FixedLenNullInSource
Collation
Id
int
no
4
10
0
no
(n/...
- 61
1
vote
1
answer
79
views
Using btree index with LIKE operator but not equal (=)
In summary, Postgres prefers doing the equivalent of a [datetime index seqscan]+[filter (using the heavy function)] instead of using the index when using = to find what I like. When simply replacing = ...
- 113
1
vote
2
answers
78
views
PostgreSQL WHERE IS NULL causes bad query plan
I have a PostgreSQL table with the following schema and indexes
+---------+---------+-----------+----------+--------------+-------------+
| Column | Type | Modifiers | Storage | Stats target | ...
- 117
5
votes
1
answer
294
views
How to optimize PostgreSQL OR query on two indexed columns
I have a large partitioned table that stores monetary transactions between accounts.
CREATE TABLE "transactions" (
"from" BYTEA NOT NULL -- sender account
,"to&...
- 117
0
votes
1
answer
20
views
Can Postgresql FDW avoid n+1 when planning joins?
Does the PostgreSQL executor / planner have the ability to avoid n+1 querying of FDW tables? If so, what conditions have to be in place for this to happen, e.g. does the FDW need to emit a specific ...
- 649
0
votes
0
answers
103
views
Force Microsoft SQL to recreate and use Specific execution plan?
I have a stored procedure that was working really well for about 3 weeks,
than all of a sudden it basically ground to a complete Stop.
This was i believe due to the server creating an additional query ...
- 101
0
votes
1
answer
36
views
Enforce query plan for queries with expensive predicates/UDFs
I have a question on how to enforce the query optimizer to use a certain execution plan. Lets assume we have two tables, t1 (25k rows) and t2 (100k rows). Also note that all the rows in t1 will find ...
- 3
0
votes
0
answers
70
views
Computed column marked as PERSISTED still calculates every time [duplicate]
I have the following table:
CREATE TABLE dbo.Cases (
CaseCaption nvarchar(255),
NickNameComputed AS (dbo.fn_ParseNickName(CaseCaption)) NOT NULL,
NickNamePersisted AS (dbo.fn_ParseNickName(...
- 1,919
1
vote
0
answers
62
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
3
votes
1
answer
184
views
Query store plan is not forcing and there is no failure reason [duplicate]
I have forced a plan in query store as follows
EXEC sys.sp_query_store_force_plan @query_id = 113366, @plan_id = 3687662
but when I run the query again, the query does not use the plan, nor does it ...
- 1,799
0
votes
0
answers
11
views
Is it possible to view the execution plan in RethinkDB?
I've been looking online to see if I could find anything on seeing the actual execution plan on RethinkDB, but it doesn't seem to be possible. Their website says sort of how it works, but not if it is ...
- 101
4
votes
2
answers
185
views
Execution plan - reading more records than in table
SQL newbie here, so apologies if this is pretty basic stuff.
SQL server 2019, Windows
2 tables - FixingHeader and Product
Simple query reading all fixing header and find related product
select [f].[...
- 49
0
votes
1
answer
87
views
ANALYZE or EXPLAIN for previous query
EXPLAIN SELECT ... shows the EXPLAIN for a query (without actually running it).
SHOW EXPLAIN FOR conn_id shows the EXPLAIN for a query that is currently running.
Is it possible to get the EXPLAIN for ...
- 748
0
votes
0
answers
21
views
Local variables and their impact on execution plans in SQL Server Stored Procedure [duplicate]
If a parameter that is passed to stored procedure is assigned to a local variable and the local variable is used inside the stored procedure logic, will that affect the execution plan in any way? Is ...
- 319
6
votes
1
answer
493
views
Estimations seem too low/inaccurate for nested loops operator
This question is a follow-up to somebody else's question: Adding an INNER JOIN ruins query performance due to different execution plan despite updated STATISTICS and RECOMPILE, why?
My question is ...
- 171
-3
votes
1
answer
68
views
4
votes
1
answer
339
views
Scalar function alters estimate for preceding operator
Please could someone help me understand why the presence of a scalar function is changing the estimate of a preceding index scan?
I'm using a copy of the StackOverflow2013 database.
SQL Server 2019 (...
- 391
0
votes
1
answer
53
views
MariaDB: Limit changes query plan and makes query very slow
It's counter-intuitive and weird to see how adding "LIMIT 2" changes whole plan for MariaDB (version 10.6.7) from using one index and having total time of 2 seconds into another index with ...
0
votes
0
answers
79
views
How to understand this execution plan?
I have a very complex execution plan and below is part of it, opened in Plan Explorer.
The image is kind of faded, I guess it's because all operators produce 0 rows.
As you can see, the arrow 1 is a ...
- 897
8
votes
1
answer
2k
views
Why am I getting a sort when I have an index?
Azure SQL Database.
I have a table from which I need to get the first and most recent rows for Col1 and Col2 based on CreateDate.
CREATE TABLE dbo.table1 (
Id INT IDENTITY(1,1) ...
- 399
5
votes
3
answers
192
views
Can Postgres scan indexes backwards?
We use an Amazon RDS instance with
PostgreSQL 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
I have a simple classic top-1-per-group query. I need to ...
- 4,577
0
votes
0
answers
29
views
Is it possible to configure a postgres DB to send a notification of some kind when a frequent query's execution plan changes?
Backend web developer here with possibly a naive question (in the title).
I recently started diving into the inner workings of Postgres query optimization and I find the query execution plan concept ...
- 101
1
vote
1
answer
129
views
Force Index does not work on aws mysql
I have a table sales with a compound index on (user_id, eastern_date, state)
CREATE TABLE sales (
id int not null auto_increment,
user_id int not null,
eastern_date date not null,
...
- 113
1
vote
0
answers
17
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
6
votes
1
answer
157
views
Query Store plan force fails with NO_PLAN dependent on where filter operator is in plan
I have a query which I force a plan for in Query Store (the plan is the one SQL Server compiled for this query) If I run the query immediately afer forcing the plan, I get the NO_PLAN ...
- 1,799
-1
votes
1
answer
66
views
queries with significant difference of estimated and actual number of rows - skew data - in the query plan
I am using dbcc show_statistics looking for skew data in my histogram and improving the quality of my statistics.
OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector.
If ...
- 15.4k
0
votes
0
answers
64
views
Azure SQL Database plan forcing
we've got Azure SQL Database with Automatic tuning - force plan turned on
sometimes executions continue with both the more expensive plan as well as the cheaper forced plan even though plan forcing is ...
- 3,645
0
votes
1
answer
96
views
How does SQL Server's Performance Dashboard categorize a query as "ad-hoc" or otherwise?
I'm looking at the CPU usage in sql server's server performance dashboard report.
I have 10 dbs on the server.
So the pie chart shows me 85% for adhoc queries. Whereas only 15% is split amongst all ...
- 3,153