报表查询性能优化求助：单表大量读取及多期间余额查询

Got it, let's work through this together—you're dealing with a slow query for annual period balance reports, and you've narrowed the core issue down to the main table. Your two key needs are: grabbing the last accounting period record for each account, and pulling all account records up to that final period. Here's how to restructure and optimize this:

First: Isolate the Main Table Logic #

Since you mentioned extra joins are less critical, start by stripping those out and optimizing the main table logic first. Once that's snappy, you can reintroduce other tables one by one to avoid muddying the performance picture.

1. Get the Last Period Record for Each Account #

Avoid inefficient subqueries or NOT EXISTS checks that scan the table repeatedly. These two methods are far faster:

Option A: Use Window Functions to Flag the Latest Period

Window functions let you mark the most recent period per account in a single pass:

WITH account_latest_period AS (
    SELECT 
        account_id,
        period,
        balance,
        -- Assigns 1 to the most recent period for each account
        ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY period DESC) AS period_rank
    FROM account_balances
)
-- Pull only the top-ranked (latest) record per account
SELECT * FROM account_latest_period WHERE period_rank = 1;

If an account has multiple records for the same latest period, swap ROW_NUMBER() with RANK() to keep all of them.

Option B: Aggregate to Find Max Period, Then Join Back

This works especially well if you have an index on (account_id, period):

WITH account_max_period AS (
    SELECT account_id, MAX(period) AS last_period
    FROM account_balances
    GROUP BY account_id
)
SELECT ab.* 
FROM account_balances ab
INNER JOIN account_max_period amp 
    ON ab.account_id = amp.account_id 
    AND ab.period = amp.last_period;

2. Pull All Records Up to the Final Period #

Adjust based on which scenario fits your needs:

If You Need All Accounts Up to the Global Last Period

First grab the latest period across the entire table, then filter:

WITH global_last_period AS (
    SELECT MAX(period) AS final_period FROM account_balances
)
SELECT ab.* 
FROM account_balances ab
CROSS JOIN global_last_period glp
WHERE ab.period <= glp.final_period;

If You Need Each Account's Records Up to Its Own Last Period

Use a window function to attach each account's latest period to all its records, then filter:

WITH account_all_periods AS (
    SELECT 
        *,
        MAX(period) OVER (PARTITION BY account_id) AS account_final_period
    FROM account_balances
)
SELECT * 
FROM account_all_periods
WHERE period <= account_final_period;

This avoids re-scanning the table to filter each account's history.

Second: Indexes Make All the Difference #

Without proper indexes, even the best query will crawl. Add these to your main table:

• A composite index for account + period (critical for both above methods):

  CREATE INDEX idx_account_period_desc ON account_balances(account_id, period DESC);
  

• If you frequently filter by period ranges alone, add a single-column index on period as well.
• Avoid over-indexing—only add indexes for fields used in WHERE, JOIN, or ORDER BY clauses.

Third: Trim the Fat #

• Ditch SELECT *—only query the fields you actually need for the report. Less data = faster transfer and processing.
• When reintroducing other tables, use INNER JOIN instead of LEFT JOIN if you don't need null records from the joined tables.
• For massive datasets, consider precomputing balances with a materialized view that refreshes periodically (instead of running the full query every time).

Fourth: Diagnose with Execution Plans #

Run EXPLAIN ANALYZE on your query to spot bottlenecks:

EXPLAIN ANALYZE 
-- Paste your full query here

Look for:

• Seq Scan (full table scans): Fix these by adding the indexes mentioned above.
• Slow join types like Nested Loop: For large datasets, Hash Join or Merge Join are usually more efficient.

If you can share the exact table structure scripts and original query, we can tweak this even more specifically—but these steps should give you a solid start to boost performance.

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