Sorgu Planı ve Join Algoritmaları — Planner Neden Bu Yolu Seçti?

1
@Component
2
class ClearingFileLoader {
3

4
    private final DataSource dataSource;
5

6
    ClearingFileLoader(DataSource dataSource) {
7
        this.dataSource = dataSource;
8
    }
9

10
    void load(Path clearingFile) throws SQLException, IOException {
11
        try (Connection c = dataSource.getConnection();
12
             Reader in = Files.newBufferedReader(clearingFile)) {
13
            CopyManager copy = c.unwrap(PGConnection.class).getCopyAPI();
14
            copy.copyIn("""
15
                    COPY card_transaction (card_token, auth_code, amount, currency, clearing_date)
16
                    FROM STDIN (FORMAT csv, HEADER true)
17
                    """, in);
18

19
            // The planner's statistics still describe yesterday's table.
20
            // Autovacuum will refresh them later; the morning reconciliation will not wait.
21
            try (Statement s = c.createStatement()) {
22
                s.execute("ANALYZE card_transaction");
23
            }
24
        }
25
    }
26
}

1
-- Every cleared card transaction must match one line of the scheme's settlement file.
2
-- amount is NUMERIC(19,4) on both sides, so the equality is exact.
3
SELECT t.id, t.card_token, t.amount, t.currency
4
  FROM card_transaction t
5
  LEFT JOIN settlement_record s
6
         ON s.auth_code       = t.auth_code
7
        AND s.amount          = t.amount
8
        AND s.currency        = t.currency
9
        AND s.settlement_date = t.clearing_date
10
 WHERE t.clearing_date = :clearingDate
11
   AND s.id IS NULL;   -- unmatched rows go to the operations queue

EXPLAIN (ANALYZE, BUFFERS), after ANALYZE card_transaction. Expected shape:

Hash Anti Join
  Hash Cond: (t.auth_code = s.auth_code AND t.amount = s.amount
              AND t.currency = s.currency AND t.clearing_date = s.settlement_date)
  -> Seq Scan on card_transaction t
       Filter: (clearing_date = $1)
  -> Hash
       -> Seq Scan on settlement_record s

What to check on every node:
- estimated rows and actual rows are close
- loops=1: each table is read once, the join is one pass over the hash

The same query right after the load, without ANALYZE. Shape:

Nested Loop Anti Join
  -> Seq Scan on card_transaction t
       Filter: (clearing_date = $1)
       estimated rows: 1            <- statistics from when today's rows did not exist
       actual rows: the whole night's clearing file
  -> Seq Scan on settlement_record s
       loops: one per card transaction   <- the settlement table is scanned again and again

The plan is cheapest for one row. Nothing in the code changed;
only the planner's picture of the table is out of date.