14.2 쿼리 안티패턴

-- 안티패턴
SELECT * FROM orders;

SELECT id, user_id, total FROM orders;

-- 안티패턴
SELECT * FROM users
 WHERE id NOT IN (SELECT user_id FROM banned_users);

SELECT * FROM users u
 WHERE NOT EXISTS (SELECT 1 FROM banned_users b WHERE b.user_id = u.id);

-- 안티패턴
SELECT * FROM orders
 WHERE user_id = 1 OR user_id = 2 OR user_id = 3 OR user_id = 4 OR user_id = 5;

WHERE country = 'KR' OR phone LIKE '+82%';
-- 두 컬럼에 따로 인덱스 있어도 함께 활용 어려움

-- 1. IN으로 묶음
WHERE user_id IN (1, 2, 3, 4, 5)

-- 2. UNION ALL
SELECT * FROM orders WHERE country = 'KR'
UNION ALL
SELECT * FROM orders WHERE country <> 'KR' AND phone LIKE '+82%';

-- 안티패턴
WHERE LOWER(email) = 'a@b.com';            -- 인덱스 못 씀
WHERE created_at::date = '2026-05-23';     -- partition pruning 안 됨
WHERE EXTRACT(year FROM created_at) = 2026; -- 같음

-- expression index
CREATE INDEX ON users(LOWER(email));

-- 범위 비교
WHERE created_at >= '2026-05-23' AND created_at < '2026-05-24';

WHERE name LIKE '%kim%';

CREATE EXTENSION pg_trgm;
CREATE INDEX ON users USING gin (name gin_trgm_ops);
-- 이제 인덱스 활용
WHERE name ILIKE '%kim%';

users = db.query("SELECT * FROM users")
for u in users:
    orders = db.query("SELECT * FROM orders WHERE user_id = %s", u.id)

users = db.query("""
  SELECT u.*, json_agg(o.*) AS orders
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
   GROUP BY u.id
""")

-- 안티패턴 — 깊은 페이지
SELECT * FROM orders ORDER BY id DESC OFFSET 100000 LIMIT 20;

-- 첫 페이지
SELECT * FROM orders ORDER BY id DESC LIMIT 20;
-- 마지막 id 저장

-- 다음 페이지
SELECT * FROM orders WHERE id < :last_id ORDER BY id DESC LIMIT 20;

-- 안티패턴 — 큰 테이블에서
SELECT count(*) FROM events;   -- seq scan

SELECT reltuples::bigint AS approx_rows FROM pg_class WHERE relname = 'events';

-- 안티패턴
SELECT user_id, count(*)
  FROM events
 GROUP BY user_id;
-- application에서 100만 row 받아 sort·filter

SELECT user_id, count(*) AS cnt
  FROM events
 GROUP BY user_id
HAVING count(*) > 100
 ORDER BY cnt DESC
 LIMIT 10;

-- 안티패턴 — 단순 조회에도
BEGIN;
SELECT * FROM orders WHERE id = 42 FOR UPDATE;
COMMIT;

SELECT * FROM orders WHERE id = 42;   -- MVCC가 일관성 보장 (3.2)

-- orders.id is bigint
WHERE id = '42';   -- text vs bigint

WHERE id = 42;   -- 같은 타입

WITH big_set AS (
  SELECT * FROM events WHERE created_at > now() - '1 hour'
)
SELECT count(*) FROM big_set WHERE user_id = 42;

WITH big_set AS NOT MATERIALIZED (...)   -- 명시 inline
WITH big_set AS MATERIALIZED (...)        -- 명시 구체화 (옛 동작)

-- 안티패턴 — JOIN으로 중복 생긴 후
SELECT DISTINCT user_id FROM users u JOIN orders o ON o.user_id = u.id;

SELECT u.id FROM users u
 WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 안티패턴
WHERE payload->>'dynamic_field_42' = 'X';

WHERE id IN (1, 2, 3, ..., 10000);    -- 1만 개

WITH ids(id) AS (VALUES (1),(2),(3),...)
SELECT * FROM orders WHERE id IN (SELECT id FROM ids);

WHERE id = ANY('{1,2,3,...,10000}'::bigint[]);

SELECT
  CASE status
    WHEN 'paid' THEN ...
    WHEN 'pending' THEN ...
    ...50 cases...
  END
FROM orders;

-- 안티패턴 — 대용량 정렬
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;
-- created_at에 인덱스 없으면 전체 정렬