请求简化执行成本为58的SQL查询（含表结构说明）

Let's break down your original query and streamline it step by step to cut down execution cost while retaining all your intended logic.

Original Query Inefficiencies #

Your current query has a few redundant parts that are driving up the cost:

• Duplicate table scans: You're joining ct.tab1 and ct.tab2 twice (once in the IN subquery, once again in the outer subquery), forcing the database to process these tables multiple times.
• Unnecessary outer join: The final join to ct.tab1 a is redundant—your test subquery already includes all the required fields from ct.tab1.
• Overly nested logic: Multiple nested subqueries make the query harder to read and less efficient for the database optimizer to handle.

Simplified Query #

Here's a trimmed version that keeps all your original requirements but eliminates redundant work:

SELECT 
  tID, 
  sor_acct_id, 
  pmt, 
  status 
FROM (
  SELECT 
    a.tID, 
    a.sor_acct_id, 
    a.pmt, 
    b.status,
    ROW_NUMBER() OVER (PARTITION BY a.tID ORDER BY b.dueDate DESC) AS rn
  FROM ct.tab1 a
  INNER JOIN ct.tab2 b ON a.tID = b.tID
  WHERE 
    a.status = 'E' 
    AND a.pmt IS NOT NULL 
    AND a.pmt <> '{}'
    -- Combined date conditions (intersection of your original two ranges)
    AND b.dueDate > CURRENT_DATE - 1 
    AND b.dueDate < CURRENT_DATE
) test
WHERE rn = 1 
  AND status IN ('X', 'Z')

Key Optimizations Made #

• Removed duplicate joins: We merged the IN subquery's filter conditions directly into the main join, so the database only scans ct.tab1 and ct.tab2 once.
• Cut redundant outer join: All needed fields from ct.tab1 are already included in the subquery, so no extra join is required at the end.
• Simplified date logic: Your original query had overlapping date filters for b.dueDate—we combined them into a single clear range, making it easier for the optimizer to pick efficient execution paths.
• Reduced nesting: We trimmed from three nested levels to two, improving readability and lowering execution overhead.

Bonus Indexing Tip #

Since ct.tab2 uses a composite key on (dueDate, tID), ensure this index is enabled. The ROW_NUMBER() operation (partitioning by tID and sorting by dueDate DESC) will leverage this index to avoid extra sorting, further reducing cost.

内容的提问来源于stack exchange，提问作者user9882610