All Questions
Tagged with sql sql-execution-plan
535
questions
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 ...
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
1
vote
1
answer
96
views
How to get EXPLAIN output with a SELECT statement?
I'm trying to grab a query plan for my query. However, I do not have direct access to the db and can only run SELECT statements on my end.
Is there a way to wrap SELECT around EXPLAIN, maybe even ...
0
votes
1
answer
45
views
Why is this Postgresql UPDATE statement so slow even when it doesn't update any rows? [closed]
I see that a full table scan is planned, however it's never executed and the UPDATE takes a long time anyway. Why??
Here's the EXPLAIN output
Update on public.hone_cohortuser (cost=3180.32..8951.51 ...
- 1,335
0
votes
0
answers
68
views
Adding a WHERE clause causes "Missing data for not-null field error" in CTE but not in temp table
I have a query where I am getting the distance for each point in my table (fields longitude and latitude) from a centroid using ST_Point and ST_DistanceSphere (below). If I want to filter the results ...
- 1
1
vote
1
answer
84
views
Efficient many-to-many embedding comparisons
I am trying to recommend a user the top "articles" given embeddings of "interests" they have.
Each "user" will have 5-10 embeddings associated with their profile, ...
- 783
0
votes
1
answer
137
views
How to get explain for a foreign table
I have a query that is essentially
SELECT *
FROM foreign_table_a
LEFT JOIN local_table_b on foreign_table_a.id = local_table_b.id
I'm looking at the explain analyse in datagrip and I don't really get ...
- 847
1
vote
2
answers
398
views
Nested loop inner join with index lookup and filter is slow
I have this query I'm running in MySQL:
SELECT
count(*)
FROM
library AS l
JOIN plays AS p ON p.user_id = l.user_id AND
l.path = p.path
WHERE
l.user_id = 20977 AND
p.time >= '...
- 8,140
0
votes
1
answer
111
views
Using LIMIT makes query much slower - optimiser moves ORDER BY
I have the following query, which runs slowly:
SELECT l.track, r.rating
FROM library l LEFT JOIN rating r ON r.type='song' AND r.id=26452 AND r.value=CONCAT(l.name, ':', l.path)
WHERE l.id=26452
...
- 8,140
0
votes
0
answers
97
views
Azure Managed Instance - Slow Index Delete
I have a large table in Azure SQL Managed Instance. The table has ~ 550 million rows and is ~ 250 gb in size. The table has a clustered index on a surrogate key which is auto generated by the system ...
0
votes
1
answer
108
views
Delete statement takes too long
I have a ProfileAvatars table with 1000 rows that is the parent table for UserProfile table with 16,557,010 rows.
When I add a new picture (without any child record in UserProfile) and I want to ...
- 5,741
0
votes
1
answer
156
views
psql execution plan interpretation, Time spike compare to Cost (no spike)
could you help me to interpret a psql execution plan, i'm a java developer, rather then DB engineer and can't get the below.
I have a big sql (the actual sql is not that important i guess), but it ...
- 45
0
votes
2
answers
365
views
Oracle SQL Performance issue with 'IS NULL' - NVL
I am using Oracle 12.2 version. I have SQL that has two filter condition to fetch NULL values from huge table. i have converted that IS NULL to NVL (OFF_CODE, -1)=-1 and nvl(off_date,'01-JAN-1900')= '...
- 39
0
votes
1
answer
296
views
Optimizing the execution plan of SQL Server query with subquery in where condition
There are two tables one with the actual fact data(table1) and the other table(table2) with more like a tracker information on date.
I am trying to get all the fact data from table1 where the date ...
0
votes
1
answer
810
views
NOT Equal(<>) in Oracle SQL is ignoring Index and NULL valued rows
I have one query where need to filter based on NOT Equal values in Oracle SQL. Can not use EQUAL to condition as the possible values fo the column in unknown.
Query:
SELECT * from employee where name &...
3
votes
2
answers
512
views
How do I get a PostgreSQL execution plan with parameters?
My application has a myriad of queries and I wanted to get the execution plan of some of them. Most if not
all of the queries have multiple parameters and I can't find how to get the execution plan ...
- 936
0
votes
1
answer
75
views
How to understand expain analyze output for query?
I have this query in my database:
INSERT INTO value2s(organization,
location,
device,
measurement,
timestamp,
interval,
average,
...
- 33k
1
vote
1
answer
106
views
Why does Oracle execute both TABLE SCAN and INDEX UNIQUE SCAN on the same table in one query?
I am not looking for a optimization of the following, just an explanation. I have this query:
SELECT COUNT(LARGE_A.id_a), SUM(LARGE_A.b_integer)
FROM LARGE_A
INNER JOIN MEDIUM_A ON LARGE_A.id_a = ...
- 2,011
1
vote
1
answer
78
views
Similar Query different execution plan and performance
This is the script I use to generate 10M rows on Clients table from 200 different cities. (It took 11 minutes on a desktop PC)
*Important note at the end
-- Crear la tabla Clientes
CREATE TABLE ...
- 126
1
vote
1
answer
83
views
Why does the location of the SUM() in an IF statement matter in a GROUP BY scenario when the grouped by column is changed? (BigQuery)?
I have some electrical meter data that I want to group by meter and convert all data to baseline units. The below query does that:
SELECT meter,
IF(unit="kWh" OR unit="kW", &...
- 109
1
vote
1
answer
303
views
SQL Server hash match when joining to view
I have a subquery, which always return 1500 rows, because of TOP 1500 on the beginning. It gets left joined to a view, which returns 15mil rows if called outside any context. And in execution plan I'm ...
- 359
6
votes
1
answer
423
views
How to see the execution plan of a multi-statement table valued function in SQL Server?
I have a view in SQL Server that calls a function. When I show the actual execution plan for querying the view, what happens inside the function is completely opaque though.
As a small example, I made ...
- 3,653
0
votes
3
answers
372
views
How to improve slow query performance?
I have a multi-join query that targeting the hospital's chart database.
this takes 5~10 seconds or more.
This is the visual expain using mysql workbench.
The query is below.
select sc.CLIENT_ID as '...
- 163
0
votes
0
answers
56
views
Almost equal table with different running time
I’m using oracle. I have two table A and B
Table A has 8000 rows and 5 five columns
Table B has 5500 rows and same 5 columns
All of 5500 rows in table B are contained in Table A and they are the same
...
- 1
0
votes
1
answer
187
views
How to prevent SQL Server from running the same subquery multiple times
I have a query that follows the following structure:
SELECT *
FROM
... <generated code> ...
(SELECT <fields>,
CASE(SELECT TOP 1 ID FROM [Configuration] WHERE IsDefault=...
- 2,647
0
votes
0
answers
67
views
SQL INNER JOIN vs WHERE IN big performance difference
I have read multiple sources and still don't understand where a big difference comes from in a query I have for Microsoft SQL Server.
I need to count different alerts linked to vehicles (IdMateriel is ...
- 73
0
votes
1
answer
130
views
SQL Server 2022 : unique index with two columns not working correctly
I have a table with users with over 50000000 entries. I want to check for the presence of already registered users on the server and I execute this request.
SELECT *
FROM
(SELECT TOP (1000) *
...
0
votes
2
answers
372
views
How to fine tune SQL Server query execution plan generation?
I have a large "Deals" table (1.3 million rows) that needs to be displayed on a paginated grid in my application, the application also includes filters to help the user search through those ...
- 2,647
0
votes
0
answers
68
views
Why doesn't computed column calculation show up in the the Execution Plan?
I have a table with a computed column. It is not persisted.
create table dbo.Cases (
...
CaseName varchar(20) not null,
NickName AS (dbo.fn_ParseCaseNickName(CaseName)),
...
)
When I execute ...
- 61.1k