Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,134
questions
2
votes
1
answer
1k
views
PostgreSQL query performance varies depending on WHERE clause values
I'm having performance issues with a query depending on the user_id I use in the WHERE clause.
This question describes a very similar issue but not quite the same:
PostgreSQL filter/aggregate ...
- 167k
1
vote
1
answer
53
views
Filter out rows where any value is not unique
I am self joining on a table and get the following result:
+----+----+----+----+----+----+----+----+----+
| id | s1 | s2 | s3 | s4 | s5 | s6 | s7 | s8 |
+----+----+----+----+----+----+----+----+----+
|...
- 167k
1
vote
2
answers
54
views
Simple query with two WHERE clauses uses inferior index scan
I have a table named "db_log" with a size of 11GB and approximately 10 million rows in PostgreSQL 13.7 version. The issue is that a simple query is not performing an full index scan for ...
- 74.8k
0
votes
1
answer
625
views
Subquery not using Index, even if forcing
Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1
If I run:
EXPLAIN SELECT COUNT(*) FROM ips WHERE ip="1.2.3.4"
This query ...
CommunityBot
- 1
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,...
CommunityBot
- 1
0
votes
1
answer
4k
views
Costly HASH MATCH Aggregate
I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index)
Here is the view and the plan.
execution plan
CREATE ...
CommunityBot
- 1
0
votes
1
answer
74
views
Why the second query is so much faster than the first one?
I have a query that look like this:
SELECT
DISTINCT "articles"."id",
"articles"."company_uuid",
"articles"."status",
"articles&...
- 591
3
votes
1
answer
209
views
How does SQL Server estimate cardinality on nested loops index seek
I am trying to understand how SQL Server estimates cardinality on the below Stack Overflow database query
Firstly, I create the index
CREATE INDEX IX_PostId ON dbo.Comments
(
PostId
)
INCLUDE
(
...
- 80.3k
12
votes
2
answers
680
views
Unable to execute query and not even able to generate Estimated execution plan
I am working on SQL Server 2019.
I have a table dbo.AllDates where I have all dates from 1990 to 2050. I have another table dbo.ActualExchangeRates where I have actual exchange rates for certain ...
- 80.3k
-1
votes
1
answer
47
views
What causes a stored procedure to have multiple plans in SQL Server?
We have noticed that many of our stored procedures, which involve the use of local variables, have multiple plans associated with them. By which I mean the same query in the stored procedure has ...
- 24.4k
0
votes
1
answer
466
views
What exactly happens when optimizer_prune_level = 1?
This documentation link states that optimizer_prune_level
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space.
Can ...
CommunityBot
- 1
1
vote
1
answer
53
views
MySQL explain plan doesn't show key lookup
I have a query that's really slow when I select a lot of columns but greatly speeds up when I only select the columns present in the index the query is using. This makes me believe that the query is ...
- 165
17
votes
2
answers
5k
views
Are the cost percentages in this SQL Server plan over 100% for a valid reason?
I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet:
Why are many of the costs listed above 100% ? Shouldn't that be impossible?
- 131
6
votes
3
answers
1k
views
Is there an option or hint possible to improve performance of query with multiple values in the "in" clause
We have a table CustomerNote with 4 columns ID, CustomerID, Note, Date
There is an index on CustomerID asc, Date desc
When the following query is executed
select top 30
Date
from CustomerNote
...
- 80.3k
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 ...
- 9,880
1
vote
2
answers
67
views
Is it possible to save the statistics profile result into a table?
In SQL Server, we can use set statistics profile on to enable profiling for a session. Then you can execute any statement and the corresponding execution plan will be shown in a tabular form. Is it ...
- 9,880
0
votes
1
answer
246
views
Clearing plan cache improves performance - bad plans in cache?
I have run into this situation a number of times on a VM with SQL Server Express 12.0.6205.1 (SQL Server 2014 SP3 CU1)
Using SSMS, I'll run a stored procedure a few times. It consistently takes, say ...
CommunityBot
- 1
1
vote
1
answer
390
views
How to get an actual execution plan from the linked server?
There is a "main" SQL Server to which I have full access and I can connect to it via SSMS. Its version is:
Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64)
Jun ...
CommunityBot
- 1
0
votes
0
answers
22
views
Stored procedure suddenly using multiple execution plans in SQL Server 2012
We have a commonly utilized stored procedure that had been functioning properly, but it unexpectedly began utilizing several plans (as many as 20, according to the performance analyzer) and causing a ...
- 15
1
vote
2
answers
1k
views
Viewing execution plans for queries with parameters
I have a sample query generated by an application (Microsoft Dynamics AX 2012 in this case) which is ineffective performance-wise (cross joins, order by etc.)
I would like to display its execution ...
- 2,181
0
votes
1
answer
58
views
How to efficiently get absolute value of a time interval in Postgresql?
I have a huge table in Postgresql-11 like following:
CREATE TABLE my_huge_table(
tick_time timestamp(6) with time zone NOT NULL,
brok_time timestamp(6) with time zone,
trade_day date NOT ...
- 28.9k
0
votes
1
answer
41
views
PostgreSQL: Unwanted materialize node within nested loop join
I have a peculiar situation where PostgreSQL is choosing to materialize rather than use a particular index I've created for a query where I join a generate_series() against a table storing streamed ...
- 101
0
votes
1
answer
561
views
Performance issues after upgrading to Mysql 8.0.19 version
Few queries are performing very poorly after the upgrade. Here are the examples.
****Example 1**:**
SELECT
x.CI_TITLE 'ciTitle',
x.DEPARTMENT_MEANING 'departmentMeaning',
x....
CommunityBot
- 1
1
vote
1
answer
395
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 ...
CommunityBot
- 1
0
votes
4
answers
111
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 ...
- 79
2
votes
0
answers
394
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 ...
- 80.3k
3
votes
3
answers
2k
views
Why is the SQL Server query plan not using indexes in a query almost similar to one that uses indexes?
I have two UPDATE queries that are similar in structure, yet the SQL Server query plan for one shows indexes being used, and for the other it shows only a regular table scan.
The following are the ...
- 2,181
0
votes
1
answer
55
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,
...
- 79
0
votes
1
answer
894
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`.*,
...
CommunityBot
- 1
0
votes
1
answer
29
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 ...
- 659
6
votes
1
answer
23k
views
Query processor could not produce a query plan because of the hints defined in this query [duplicate]
There is a similar question, but it is not the same:
The query processor could not produce a query plan
I have the following query, and the following filtered index, and I cannot see any reason why ...
CommunityBot
- 1
2
votes
3
answers
285
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 ...
- 36.3k
4
votes
1
answer
143
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 ...
- 3,327
1
vote
2
answers
122
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 ...
- 37.5k
43
votes
3
answers
18k
views
Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
- 80.3k
0
votes
1
answer
136
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,
...
- 803
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
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 ...
CommunityBot
- 1
11
votes
2
answers
3k
views
Understanding statistics, execution plans, and 'ascending key problem'
I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution.
Am I correct in saying that statistics are only used when creating the ...
- 2,181
6
votes
1
answer
532
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 ...
- 167k
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 ...
CommunityBot
- 1
3
votes
1
answer
578
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
32
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
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 ...
- 37.5k
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
11
votes
2
answers
42k
views
ROW_NUMBER() without PARTITION BY still generates Segment iterator
I'm writing on an upcoming blog post of mine on ranking and aggregate window functions, specifically the Segment and Sequence Project iterators. The way I understand it is that Segment identifies rows ...
- 2,181
3
votes
3
answers
1k
views
Reducing Temp Table Scans when joining two temp tables using OR conditions
I'm working on a complicated query. I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
- 24.4k
3
votes
1
answer
120
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 ...
- 80.3k
1
vote
2
answers
139
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):...
- 80.3k
0
votes
1
answer
39
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 ...
- 37.5k