Skip to main content

All Questions

Filter by
Sorted by
Tagged with
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 ...
MattB's user avatar
  • 111
2 votes
2 answers
3k 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 ...
William Oliveira's user avatar
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 ...
HeyJude's user avatar
  • 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 ...
TaylanKammer's user avatar
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 ...
nalzok's user avatar
  • 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 ...
inux's user avatar
  • 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-...
blubb's user avatar
  • 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 ( ...
Oliver Gibson's user avatar
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 ...
Guy Schalnat's user avatar
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 ...
jcalfee314's user avatar
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 ...
ashuthosh's user avatar
  • 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 ...
MattyZDBA's user avatar
  • 1,935