Questions tagged [sql-execution-plan]
A execution, or query plan, is the breakdown of steps the database engine uses to get a result.
sql-execution-plan
1,475
questions
0
votes
0
answers
67
views
Why do I get a Clustered Index Scan instead of a Table Scan? [duplicate]
I have these simple tables, one without indexes and the other with only a primary key and clustered index on id:
CREATE TABLE [dbo].[MyTableWithoutPk]([id] [int] NOT NULL, [category_id] int NULL)
...
- 35.9k
1
vote
1
answer
52
views
Do I trust "cost" or "actual time" more when using Postgres EXPLAIN ANALYZE?
I'm trying to improve the performance of one of our most expensive queries. I'm running EXPLAIN ANALYZE on the sandbox postgres 15 database, which has a much smaller dataset than the production ...
0
votes
0
answers
46
views
Mysql select with WHERE (a, b) in (('str1', 'str2'), ...., ('str6000', 'str6000')) taking 30 seconds to execute
I have table of the following structure:
create table my_table
(
id int auto_increment
primary key,
name varchar(255) null,
vendor_name ...
0
votes
0
answers
22
views
PostgreSQL 16: SELECT returns rows but UPDATE with the same WHERE clause finds none
I am encountering an issue in PostgreSQL 16 where a SELECT query returns 4 rows, but using the same WHERE clause in an UPDATE statement results in no rows being found.
I examined the query plans and ...
0
votes
0
answers
22
views
Delete operation taking time from SQL Agent Job [migrated]
I am running a job which deletes data from 7 tables, 2 of which contains 10-20 lakhs of records. But the job get stuck at a point when deleting data from a particular table web_activity which holds ...
- 1
0
votes
1
answer
194
views
Simple insert times out in application, but fast in SSMS
In my application I have a simple insert, when captured with profiler it looks like this
insert into ford.tblFordCompoundFlowVehicle
(FordCompoundFlowID, CompoundVehicleID, SortOrder, Status1ToSend, ...
- 11.8k
1
vote
1
answer
44
views
Discrepancy between cost and row count estimates in nested loop
I have this snippet of a PostgreSQL explain plan I'm trying to comprehend:
-> Nested Loop (cost=0.57..933455.16 rows=11 width=122) (actual time=3.710..497.990 rows=86102 loops=1)
-> ...
- 1,044
-1
votes
2
answers
60
views
Used statistics from XML plan cache for query with given QueryPlanHash
I'm using SQL Server 2016/2019. I want to extract (for given @QueryPlanHash BINARY(8) = 0x397CEDB37FA0E1D2) from execution plan XML in cache - which statistics was used to generate that plan.
This ...
1
vote
2
answers
90
views
How to make Postgres use an index for a set of values?
I have a table with ~35M rows, and trying to find "processed" records to remove from time to time. There are 14 valid statuses, and 10 of them are processed.
id uuid default uuid_generate_v4(...
- 6,578
0
votes
1
answer
97
views
MySQL(5.7.26) query optimization, why select * is much faster that select id in my leading wildcard query?
I have a leading wildcard query I know it is hard to optimize. I know if I use only the trailing wildcard I can do some optimization. But our client wants the leading wildcard because when searching ...
- 11.6k
1
vote
0
answers
33
views
postgresql EXPLAIN gives seq scan rows = 12 for filter id < 10
I ran explain command on this query:
EXPLAIN
Select * from project_project where id < 10 FOR UPDATE
And the plan output:
"LockRows (cost=0.00..6.57 rows=12 width=18305)"
" -> ...
- 4,033
0
votes
1
answer
76
views
How to ensure use of an index?
I have the following query:
SELECT cp.ID_Case
FROM dbo.CaseParty cp (NOLOCK)
JOIN dbo.Client cli (NOLOCK) ON CASE
WHEN cli.ClientType = 'atty' AND cp.ID_ClientAttorney = cli.ID_Client THEN 1
...
- 61.1k
0
votes
0
answers
70
views
Non-deterministic execution plan on Postgres
Fairly simple schema:
CREATE TABLE IF NOT EXISTS Queue(
Id BIGSERIAL NOT NULL PRIMARY KEY,
SendAt TIMESTAMP(3) NOT NULL,
Payload TEXT NOT ...
- 2,475
3
votes
2
answers
64
views
Estimated execution plan alternatives
As far as I understand, given a query, Oracle tries different approaches based on some heuristics, where I guess the most driving factor is minimizing the number of rows to work with, and then chooses ...
- 11.4k
0
votes
2
answers
69
views
Complicated column dependencies in postgreSQL
I have two columns: column1 with two distinct values (0, 1) and column2 with 3 distinct values ('A', 'B', 'C'). Value in column1 is always 0 for 'A' and 'B' in column2, but if the value in column2 is '...
- 33