Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
974
questions
0
votes
0answers
21 views
PSQL 9.6 - Why does query plan change every time I run it?
We have a production POSTGRES 9.6 db with about 100M records (LOYALTY) and new table (< few thousands as of now) "INFO"
Base Table (written in django)
class Loyalty(models.Model):
...
0
votes
0answers
24 views
Execution plan for slow-running query shows table spool operator generating millions of rows
I'm an aspiring Database Administrator and I've taken on the monumental task of performance tuning our decade-old sql server. After watching countless Brent Ozar videos, I now feel like I can ...
0
votes
1answer
23 views
Why doesn't /*+ NO_INDEX*/ hint , affect the “execution plan”?
I have a sample table with below structure :
create table mi_dimcustomer
(customer_num number(10),
<other columns> <data types>)
And there is a unique index on column customer_num. I'...
1
vote
1answer
35 views
DISTINCT forces re-sorting of data on PostgreSQL
I have a table recording transactions between accounts, looking like this:
txid varchar(64)
account varchar(64)
ts timestamp
amount numeric
(there are more fields but they are irrelevant for ...
1
vote
0answers
88 views
Why is Index causing bad estimation and execution plan
SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) -
ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0)
FROM [Credit] M WITH(NOLOCK)
INNER JOIN [Transaction] T4 WITH(...
0
votes
1answer
30 views
Facing error “cannot fetch plan for SQL_ID” while trying to capture the execution plan
I'm trying to capture the execution plan of my query on PL/SQL developer as you can see below :
select *
from vmi_dimcustomer t1
inner join vmi_factcustomer t2
on t1.customer_num = t2....
1
vote
0answers
44 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 ...
0
votes
1answer
42 views
The correct way for generating “Execution plan” for a PL-SQL statement in Oracle
I'm studying about Explain plan and Execution plan in Oracle and according to what I've read so far , The explained plan can differ from the actual plan used during statement execution. So I think ...
0
votes
0answers
24 views
Slow query in SSD when enable_bitmapscan=on
Table INFO and index INFO:
CREATE TABLE PART (
P_PARTKEY SERIAL,
P_NAME VARCHAR(55),
P_MFGR CHAR(25),
P_BRAND CHAR(10),
P_TYPE VARCHAR(25),...
10
votes
1answer
298 views
Why does a clustered Key Lookup on a primary key have a high estimated rows per execution?
We have a query with a Key Lookup which is estimating thousands of rows per execution. As I understand it, there should only ever be one row per execution. I understand statistics can be misleading ...
1
vote
2answers
63 views
Can parameter sniffing happen even after disabling it?
I'm having a very strange issue with an EF linq query, the query runs a dynamic sql, something like this:
EXEC sp_executesql N'select 1 from X',N'@parameter1 int',@parameter1 = 123
The query was ...
2
votes
2answers
83 views
What does Nested Loops join operator has to do with a Scalar?
As far as I understand the docs, the Nested Loops operator is a join operator, i.e., it requires two tables as input.
If that's correct, then why the Nested Loops operator is used with a Scalar input?
...
3
votes
1answer
62 views
Understanding the relationship between a Table Spool and correlated subquery
I'm learning about execution plans, and have a question regarding the relationship between a Table Spool and correlated subquery (I'm following this tutorial, don't mind the typos).
I've created the ...
0
votes
0answers
24 views
Get Join Keys dynamically from a table
SQL/Impala
I have a scenario where I need to join my input file with a mapping table which has map columns and derived columns. The join keys are dynamic and need to taken from a dynamic metadata ...
4
votes
1answer
83 views
In PostgreSQL, what area in memory does HashSetOp use, work_mem or shared_buffer?
I want to know the difference between the hash table in a hash join and the hash table in HashSetOp Except.
Here is the PostgreSQL script I wrote to explain my question.
drop table if exists t1 ...
-1
votes
1answer
18 views
Why does query rowcount change when I enable “include actual execution plan” and “include live query statistics” together?
I have a moderately complex SELECT query that utilises inline views and joins. I am intending to see if I can rewrite portions of the query to improve performance. I pasted the query into two SQL ...
8
votes
2answers
153 views
SQL Server not Using Index
I am absolutely stumped as to why my query is not using what I think is a selective index.
My Model consists of Claims, Contacts, and Phone Numbers. Each Claim has 1 Contact and Each Contact has Many ...
1
vote
0answers
30 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 ...
5
votes
1answer
215 views
Msg 6355 “Conversion of one or more characters from XML to target collation impossible” when querying sys.dm_exec_query_plan
I like to find missing indexes on the go, looking at the execution plans!
It can potentially give me an indication where further to look at if I want to improve something that is currently running.
...
0
votes
2answers
29 views
Weird tables with numbers and underscores in PostgreSQL execution plans
I'm debugging performance problems with my DB and see some unusual entries in the DB log:
2021-04-21 01:07:33.857 UTC [14823] user@db LOG: duration: 933645.469 ms plan:
Query Text: select systemv0_....
3
votes
1answer
51 views
Date index SEEK is chosen when using OPTION RECOMPILE but not with OPTION OPTIMIZE FOR
I have a table containing 10 years worth of 'package scans'. Somebody scans a package and it records the date and username. Let's pretend for now that retaining 10 years of data actually has a purpose....
0
votes
1answer
17 views
Why is below 2 query generating different execution plan in MYSQL?
Only difference in the below query is the order by column. The resultset of both query is same.
However, the 2nd query execution plan shows 'Using temporary; Using filesort' for ordering where as for ...
1
vote
1answer
56 views
How do I debug Postgres' join strategy?
In the process of optimizing a Postgres query, I ran into a situation where two equivalent, simple queries execute in very different amounts of time. While I can't provide a reproducible example for ...
1
vote
1answer
56 views
Reason for failed query plan executions in SQL Server
We are using Azure SQL Database and recently we have experienced a severe performance degradation on our production database. When looking into Regressed queries in Query Store to find out the ...
0
votes
1answer
81 views
OPENJSON running once per row of an INNER JOIN unnecessarily?
I have a table MP_User with 147 entries, and in order to query it, I am passing in JSON containing an array of "query objects", each of which has a unique ID and either an email address or a ...
1
vote
3answers
115 views
Is it worth adding or rebuilding an index on a table already involved in an active query
I've kicked off an SQL Server query that has been going for 12 hours. Annoyingly I forgot to rebuild my indexes on one of the tables involved, I needed to do this as they are over 95% fragmented due ...
1
vote
1answer
48 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 ...
0
votes
1answer
61 views
Forced Parameterization generating duplicate plans
https://www.brentozar.com/pastetheplan/?id=H1qoS7BSu
I am using Azure SQL 18 vCore db with Forced Parameterization, and MAXDOP 8. The vCore count may change much lower for other dbs but for the same ...
1
vote
1answer
36 views
PostgreSQL chooses to seq scan huge table instead of index lookup
I have a table, account_balance, with primary key (address, timestamp, currency). This table is pretty big, having nearly 1 billion rows and taking ~3T of space. I am doing a simple query on it:
...
3
votes
1answer
59 views
PostgreSql changes same query plan at runtime
im having a really weird issue here. We have search query that receives multiple parameters and searches by multiple joins in different tables. The query works fine first few time we run it and then ...
0
votes
2answers
59 views
How to see if index is being read “ordered” from DMVs
I have a table that is being used in many reporting queries. I would like to know how many times the table is read in an "ordered" way. In the execution plan for clustered index scan one can ...
2
votes
1answer
59 views
MySQL performance issue when a hash join is used
I'm having an issue where the optimizer seems to be doing a full table scan and exhibiting a big performance hit when a hash join is used.
Schema and sample data:
CREATE TABLE `px_po_status` (
`...
0
votes
1answer
58 views
Why does pre-sorted index not help me in window function?
I am trying to speed up this query. Currently it takes ~6s.
It queries a TEMP TABLE providers_tam_with_offices_tmp.
select *,
%(max_providers_to_target_per_territory)s as max_providers,
case
...
0
votes
1answer
23 views
DB2 for i equivalent of MSSQL's Show Query Execution Plan
I'm a DB2 newcomer and I'm hoping that DB2 for i has an equivalent to MS SQL's Show Query Execution path.
If so, what's it called, how do I access it, and what permissions do I need to use it?
Thank ...
2
votes
2answers
96 views
How to hint Remote Scan operator to estimate more than 10000 rows?
I need to read data from a linked server and insert into a local table. I need to remove duplicates in data and I need to do it on a local server, because remote server is overloaded. So, I added ...
2
votes
4answers
99 views
Adding an INNER JOIN ruins query performance due to different execution plan despite updated STATISTICS and RECOMPILE, why?
In the home-page of my multi-tenant web application, Linq-to-Entities (EF6) is used to get a paged list of Calendar Events. This data is JOINed with some other tables to produce this query, below.
The ...
1
vote
1answer
56 views
Stale Statistics and View performance on SQL Server 2016
I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.
So my doubt essentially is -
What could be the reason that even with ...
0
votes
1answer
35 views
why is there an index range scan on max() query?
I have a table that logs the transmission of data to another system.
create table transfer_log (id number(10),
fk_data number(10),
first_sent_time ...
0
votes
1answer
125 views
Postgres chooses seq scan over index scan
I have the following query:
SELECT *
FROM foo
LEFT OUTER JOIN foo_history ON foo.id = foo_history.foo_id
WHERE foo.entity_id = 11111
foo table is around 8Million rows and foo_history is around ...
1
vote
1answer
48 views
Does it make sense to use OPTION(RECOMPILE) in dynamic SQL?
I would like to find out if using OPTION(RECOMPILE) is beneficial when executing dynamic sql queries?
There are a few points to keep in mind:
There will be hundreds (maybe even thousands) of ...
0
votes
1answer
33 views
“Heap Blocks: ” Number meaning on Query Plan for a bitmap heap + index scan
What does the number next to Heap Blocks represent in the query plan? I'm not sure if it means the # of blocks (8kb per) of data located on disk representing your data, or in some way the bitmap, ...
0
votes
0answers
24 views
PostgreSQL + Postgis strange execution plan
I have constructed the following query to find the % area of intersection between 2 geometry columns then I join this to another table. This query is extracted from a larger query.
SELECT fractions....
0
votes
1answer
21 views
Poor execution plan leads to query time increasing dramatically
I have a relatively complex query like the one that follows
select * from (
select
distinct on("car")
entity.id as "car",
metric as "last_visit"
...
2
votes
2answers
209 views
Why does SQL Server do lots of unnecessary clustered key lookups in this SELECT - OFFSET - FETCH?
I have the following table:
CREATE TABLE [dbo].[MP_Notification_Audit](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[type] [int] NOT NULL,
[source_user_id] [bigint] NOT NULL,
[target_user_id]...
3
votes
2answers
270 views
Why doesn't SQL Server use my index in this SELECT … WHERE?
I've created a table with a nonclustered PK (this is by design), and an additional nonclustered index on the column I'm filtering with a WHERE clause ([target_user_id]):
CREATE TABLE [dbo].[...
2
votes
0answers
45 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 ...
1
vote
2answers
93 views
Speed up Key Lookup (clustered)
I have a slow query in Sql 2017. Key lookup (Clustered) is taking 62%. There is also an Index Seek (nonclustered) taking 30%.
Is there anyway I can speed this query up?
Execution plan: https://www....
3
votes
2answers
159 views
SQL Server Execution Plans and Index Drops
When a SQL Server index is dropped (say it's a duplicate) what are the steps undertaken by the engine to clear and then rebuild the execution plans that reference it? In particular I'm wondering ...
3
votes
1answer
86 views
Why does BETWEEN use a btree index but “element is contained by” range operator (<@) does not? [duplicate]
I've got a table with a utc timestamptz which has a "btree" index on the utc column:
CREATE TABLE foo(utc timestamptz)
CREATE INDEX ix_foo_utc ON foo (utc);
This table contains about 500 ...
0
votes
2answers
30 views
How to update the time of each row and check if its updated or not
Name
Time
Status
T1
9/12/2020 12:12:09
expected
T2
8/06/2020 11:09:12
expected
These are table name and updated time in the DB I want to get the status and time of updation of specific tables .Set ...
