13주차 코드 10항목 — 같은 조회를 인덱스 전후로 재고 증거로 남기기

services:
  db:
    image: postgres:17.10-alpine
    ports:
      - "${FCL_DB_PORT:-5432}:5432"
    environment:
      POSTGRES_DB: financial_core
      POSTGRES_USER: app
      POSTGRES_PASSWORD: ${FCL_DB_PASSWORD:?set FCL_DB_PASSWORD in this terminal}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d financial_core"]
      interval: 2s
      timeout: 2s
      retries: 30
    volumes:
      - financial-core-db:/var/lib/postgresql/data

volumes:
  financial-core-db:

\set ON_ERROR_STOP on
DROP SCHEMA IF EXISTS perf_w13 CASCADE;
CREATE SCHEMA perf_w13;
CREATE TABLE perf_w13.account(id bigint PRIMARY KEY,owner_id varchar(64) NOT NULL,balance bigint NOT NULL CHECK(balance>=0));
CREATE TABLE perf_w13.business_tx(id uuid PRIMARY KEY,status varchar(16) NOT NULL);
CREATE TABLE perf_w13.ledger_entry(id bigint PRIMARY KEY,business_tx_id uuid NOT NULL REFERENCES perf_w13.business_tx(id),account_id bigint NOT NULL REFERENCES perf_w13.account(id),entry_type varchar(32) NOT NULL,signed_amount bigint NOT NULL,created_at timestamptz NOT NULL);
INSERT INTO perf_w13.account SELECT g,'perf-owner-'||g,0 FROM generate_series(1,1000) g;
INSERT INTO perf_w13.business_tx SELECT md5('perf-'||g)::uuid,'COMPLETED' FROM generate_series(1,100000) g;
INSERT INTO perf_w13.ledger_entry
SELECT g,md5('perf-'||g)::uuid,CASE WHEN g<=90000 THEN 1 ELSE 2+((g-90001)%999) END,'PERF_CREDIT',1,timestamptz '2026-01-01 00:00:00+00'+g*interval '1 second'
FROM generate_series(1,100000) g;
UPDATE perf_w13.account a SET balance=x.n FROM (SELECT account_id,sum(signed_amount)::bigint n FROM perf_w13.ledger_entry GROUP BY account_id)x WHERE x.account_id=a.id;
ANALYZE perf_w13.account; ANALYZE perf_w13.business_tx; ANALYZE perf_w13.ledger_entry;

EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON)
SELECT id,entry_type,signed_amount,created_at FROM perf_w13.ledger_entry
WHERE account_id=1 ORDER BY created_at DESC,id DESC LIMIT 50;

CREATE INDEX idx_ledger_account_created_id ON perf_w13.ledger_entry(account_id,created_at DESC,id DESC);
ANALYZE perf_w13.ledger_entry;

DO $gate$ DECLARE a bigint;t bigint;l bigint;h bigint;m bigint; BEGIN
 SELECT count(*) INTO a FROM perf_w13.account; SELECT count(*) INTO t FROM perf_w13.business_tx; SELECT count(*) INTO l FROM perf_w13.ledger_entry; SELECT count(*) INTO h FROM perf_w13.ledger_entry WHERE account_id=1;
 SELECT count(*) INTO m FROM perf_w13.account x LEFT JOIN(SELECT account_id,sum(signed_amount) b FROM perf_w13.ledger_entry GROUP BY account_id)y ON y.account_id=x.id WHERE x.balance<>coalesce(y.b,0);
 IF a<>1000 OR t<>100000 OR l<>100000 OR h<>90000 OR m<>0 THEN RAISE EXCEPTION 'W13 counts a=% t=% l=% h=% m=%',a,t,l,h,m; END IF;
 IF (SELECT count(*) FROM pg_indexes WHERE schemaname='perf_w13' AND indexname='idx_ledger_account_created_id')<>1 THEN RAISE EXCEPTION 'W13 index missing'; END IF;
END $gate$;
SELECT 'W13_ASSERT 1000|100000|100000|90000|0';