All Questions
Tagged with subquery execution-plan
12 questions
1
vote
2
answers
90
views
Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?
This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
- 111
2
votes
2
answers
2k
views
Postgres slow exists subquery
I have a queue system that has two tables, jobs and jobs_queues.
jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are ...
3
votes
1
answer
1k
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 ...
- 457
1
vote
1
answer
585
views
Getting the "flat" query corresponding to a view using nested views?
I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many ...
- 111
2
votes
0
answers
628
views
Correlated subquery with common table expressions in SQLite
Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
- 131
2
votes
0
answers
94
views
Is it possible to replace count distinct inside Correlated sub-queries?
I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)...
Is it ...
- 21
3
votes
0
answers
2k
views
Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
VALUES
('80512e52-a406-4a9b-a8df-...
- 256
4
votes
2
answers
18k
views
conditional subquery
I have the following query:
SELECT id,
email,
first_name as "firstName",
last_name as "lastName",
is_active as "isActive",
password,
access,
CASE
WHEN access < 3 THEN (
...
4
votes
1
answer
481
views
Should I use a subquery to help SQL Server find the correct plan
I have a query (which has components which are built on the fly depending on the selections a user makes in the interface) that runs in SQL Server 2008-R2 that runs in about 100 databases. Users are ...
- 191
4
votes
2
answers
8k
views
Improve performance with the WHERE NOT IN sub-select clause
In the following query, I have to count transactions for each customer. However, I have to exclude from the result set entirely, customers that have a transaction older than one year.
Shouldn't the ...
- 185
3
votes
2
answers
529
views
Query taking too much time
My query is taking too much time to execute. Please help me here.
mysql> explain
select pcm.catalog_id
from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c
...
- 707
4
votes
1
answer
588
views
How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?
Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the ...
- 1,935