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
75
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
...
- 61k
1
vote
1
answer
84
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
44
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,326
0
votes
0
answers
64
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
81
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
129
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
379
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,120
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,120
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
155
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
349
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')= '...
- 41
0
votes
1
answer
283
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 ...
