← กลับ
PostgreSQLDatabasePerformance

PostgreSQL Index ทำให้ query เร็วขึ้นยังไง

query ช้าเพราะ table ใหญ่ขึ้น? เพิ่ม index ตัวเดียวอาจเร็วขึ้น 100 เท่า อ่านบทความนี้แล้วใช้ EXPLAIN ดูเป็น

2025-12-25อ่าน 6 นาทีใหม่

ทำไม query ช้าเมื่อ data เยอะขึ้น

ตอน user 1,000 คน — query SELECT * FROM users WHERE email = ? ใช้เวลาไม่ถึง ms

ตอน user 1,000,000 คน — query เดียวกันอาจใช้ 500ms

เพราะ Postgres ไม่มี index ก็ต้อง scan ทั้ง table — เปิดทุก row เช็คว่า email ตรงไหม

Index คือ "สมุดสารบัญ" ที่ทำให้ Postgres รู้ว่า email ตัวนี้อยู่ row ที่เท่าไหร่ — ไม่ต้องเปิดทุกหน้า

EXPLAIN — ดูว่า query ทำงานยังไง

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

ผลที่ได้ (ก่อนมี index):

Seq Scan on users  (cost=0.00..18334.00 rows=1 width=...)
  Filter: (email = '[email protected]')
  Rows Removed by Filter: 999999
Planning Time: 0.1 ms
Execution Time: 240.5 ms

Seq Scan = full table scan = ช้า

หลัง CREATE INDEX idx_users_email ON users(email);:

Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=...)
  Index Cond: (email = '[email protected]')
Planning Time: 0.1 ms
Execution Time: 0.3 ms

Index Scan = ใช้ index = เร็ว ลดจาก 240ms → 0.3ms

คำสั่งสร้าง index

-- index ปกติ
CREATE INDEX idx_users_email ON users(email);

-- index แบบ unique (ห้าม duplicate)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- ไม่ block table ตอนสร้าง (สำคัญใน production)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- ลบ index
DROP INDEX idx_users_email;

-- ดู index ทั้งหมดของ table
\d users    -- ใน psql

CONCURRENTLY ใช้นานกว่า แต่ไม่ lock table — สำคัญมากใน production ที่มี traffic

จะ index column ไหน

ใส่ index ที่:

  • Column ที่ใช้ใน WHERE, JOIN ... ON, ORDER BY
  • Foreign key column (Postgres ไม่สร้าง index ให้ FK อัตโนมัติ)
  • Column ที่ filter ลด row เยอะ (high selectivity)

ไม่ต้องใส่ index ที่:

  • Table เล็ก (ไม่กี่ร้อย row) — full scan เร็วกว่า
  • Column ที่ value ซ้ำเยอะ เช่น is_active (true/false) — ใช้ partial index แทน
  • Column ที่ไม่เคยใช้ใน query

Anti-pattern: index ทุก column "เผื่อ" — slow ตอน INSERT/UPDATE เพราะต้อง update index ทุกตัวด้วย

Multi-column index

ถ้า query มีหลาย condition:

SELECT * FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC;

สร้าง compound index เรียงตามลำดับที่มีในที่เลือก:

CREATE INDEX idx_orders_user_status_created
  ON orders(user_id, status, created_at DESC);

ลำดับสำคัญ — index นี้ช่วย:

  • WHERE user_id = ? ✓
  • WHERE user_id = ? AND status = ? ✓
  • WHERE user_id = ? AND status = ? ORDER BY created_at ✓

ไม่ช่วย:

  • WHERE status = ? ✗ (ไม่ใช่ left-most)

หลัก: ใส่ column ที่ filter เยอะที่สุดก่อน

Partial index

ถ้า query แค่ subset เช่น "หา user ที่ active":

-- index ทั้ง table เปลือง space
CREATE INDEX idx_users_email ON users(email);

-- partial index — เก็บแค่ active user
CREATE INDEX idx_users_email_active
  ON users(email)
  WHERE is_active = true;

เล็กกว่า + เร็วกว่า ถ้า query ส่วนใหญ่เจาะ active user

Expression index

index บนผลของ function:

-- ค้นหา case-insensitive
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- index แบบ lower
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

ใช้ได้กับ JSON column ด้วย:

CREATE INDEX idx_metadata_country
  ON users((metadata->>'country'));

SELECT * FROM users WHERE metadata->>'country' = 'TH';

ทำไมบางครั้ง index ไม่ช่วย

Postgres เลือกใช้ index แบบ cost-based — ถ้า optimizer คำนวณว่า full scan เร็วกว่า ก็ไม่ใช้ index

เช่น:

SELECT * FROM users WHERE is_active = true;

ถ้า user 90% active, index ไม่ช่วย — Postgres ก็ scan ทั้ง table อยู่ดี

ตรวจสอบ statistics:

ANALYZE users;        -- update statistics

หลัง insert/update ข้อมูลเยอะ ควร run ANALYZE อย่างน้อยเดือนละครั้ง (Autovacuum ทำให้อยู่แล้วถ้าเปิด)

Index types ที่ Postgres รองรับ

  • B-tree — default ทุก case ใช้ได้
  • Hash — เร็วกว่า B-tree เฉพาะ = (rare ใช้)
  • GIN — สำหรับ array, JSONB, full-text search
  • GIST — geometry, range, full-text
  • BRIN — ตารางใหญ่ที่ value เรียงตามลำดับธรรมชาติ (เช่น timestamp)

ตัวอย่าง GIN สำหรับ JSONB:

CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

-- query JSON เร็ว
SELECT * FROM users WHERE metadata @> '{"premium": true}';

Connection Pooling

อีกเรื่องที่กระทบ performance — connection ของ Postgres "หนัก" เปิด-ปิดทุก request ช้า

ใช้ PgBouncer หรือ pool ใน app:

// pg pool ใน Node.js
import { Pool } from 'pg'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                  // max connection
  idleTimeoutMillis: 30000,
})

const result = await pool.query('SELECT * FROM users WHERE id = $1', [id])

production scale ใหญ่ — ใส่ PgBouncer คั่นระหว่าง app กับ Postgres connection ต่อ pool ของ app กับ pool ของ PgBouncer (transaction mode) ใช้ connection จริงๆ น้อยมาก

Slow query log

ตั้งให้ Postgres log query ที่ช้ากว่า X ms:

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

ดู log:

sudo tail -f /var/log/postgresql/postgresql-16-main.log

ใช้ tool อย่าง pg_stat_statements ดู query ที่ใช้เวลารวมเยอะที่สุด:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

เช็คลิสต์เวลา query ช้า

  1. รัน EXPLAIN ANALYZE — ดูว่า Seq Scan ที่ไหน
  2. ถ้า Seq Scan บน column ที่ filter — สร้าง index
  3. ใช้ CONCURRENTLY ตอน production
  4. หลังสร้างรัน ANALYZE แล้ว explain ใหม่ ดูว่าใช้ index แล้ว
  5. ถ้ายังช้า — ตรวจ JOIN, subquery, missing index บน FK
  6. หากยัง — ดู connection pool, lock, vacuum

สรุป

99% ของปัญหา DB ช้า แก้ด้วย index ตัวเดียว

ใช้ EXPLAIN ANALYZE บ่อยๆ จนคุ้น — ก็เริ่มเห็น pattern อ่าน plan ได้เอง

อ่านต่อ: Backup Database อัตโนมัติ — backup ก่อนทุกครั้งที่จะแก้ schema ใหญ่ๆ

← ดูบทความอื่น