ایندکس‌گذاری حرفه‌ای در PostgreSQL

ایندکس‌ها قلب performance دیتابیس هستند. PostgreSQL بیش از ۶ نوع ایندکس مختلف دارد – B-Tree، Hash، GIN، GiST، BRIN و SP-GiST – هرکدام برای مورد خاصی. در این فصل با همه آن‌ها، Partial Index، Expression Index و Covering Index آشنا می‌شویم.

چرا ایندکس مهم است؟

-- جدول 10 میلیون رکورد بدون ایندکس
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    status TEXT,
    total NUMERIC(10,2),
    created_at TIMESTAMPTZ
);

-- query بدون ایندکس - Sequential Scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;
-- Seq Scan on orders ... (cost=0.00..165000 rows=1000) 
-- Execution Time: 850ms

-- با ایندکس - Index Scan
CREATE INDEX idx_orders_user ON orders(user_id);

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;
-- Index Scan using idx_orders_user ...
-- Execution Time: 0.5ms       ← 1700 برابر سریع‌تر!

B-Tree – ایندکس پیش‌فرض

B-Tree ایندکس متعادل (balanced) است که برای equality (=) و range (<، >، BETWEEN) عالی کار می‌کند.

-- ایندکس ساده
CREATE INDEX idx_users_email ON users(email);

-- ایندکس multi-column (مرکب)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- ایندکس unique
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- یا با constraint
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE         -- خودکار ایندکس می‌سازد
);

ترتیب ستون‌ها در multi-column

ترتیب ستون‌ها بسیار مهم است:

CREATE INDEX idx ON orders(user_id, status, created_at);

-- ✅ این query‌ها از ایندکس استفاده می‌کنند:
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending' AND created_at > '2026-01-01';

-- ❌ این‌ها نمی‌توانند از ایندکس استفاده کنند:
SELECT * FROM orders WHERE status = 'pending';                    -- بدون user_id
SELECT * FROM orders WHERE created_at > '2026-01-01';             -- بدون prefix
SELECT * FROM orders WHERE status = 'pending' AND created_at ...; -- بدون user_id

DESC و ترتیب

-- اگر ORDER BY ... DESC رایج است:
CREATE INDEX idx ON orders(user_id, created_at DESC);

-- اگر ORDER BY مختلف داریم:
CREATE INDEX idx ON orders(user_id, created_at DESC, status);
-- این برای WHERE user_id = ? ORDER BY created_at DESC کار می‌کند

Hash Index

فقط برای equality (=) – معمولاً B-Tree بهتر است:

CREATE INDEX idx_session_id ON sessions USING HASH (session_id);

-- مزیت: کوچک‌تر از B-Tree برای ستون‌های با مقدار بزرگ (مثل UUID/hash)
-- معایب: فقط = پشتیبانی می‌کند، نه <، >، ORDER BY

GIN – Generalized Inverted Index

GIN برای ستون‌هایی که چند مقدار دارند: array، JSONB، tsvector، trigram.

روی JSONB

CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- حالا این query‌ها سریع می‌شوند:
SELECT * FROM products WHERE attributes @> '{"color": "قرمز"}';
SELECT * FROM products WHERE attributes ? 'discount';
SELECT * FROM products WHERE attributes ?| ARRAY['size', 'weight'];

jsonb_path_ops – سریع‌تر و کوچک‌تر

-- اگر فقط @> می‌خواهید (نه ? یا ?|)
CREATE INDEX idx_products_attrs ON products USING GIN (attributes jsonb_path_ops);
-- ~3 برابر کوچک‌تر و سریع‌تر برای @>

روی Array

CREATE INDEX idx_products_tags ON products USING GIN (tags);

SELECT * FROM products WHERE tags @> ARRAY['پشمی'];
SELECT * FROM products WHERE 'دستباف' = ANY(tags);

روی Full-Text Search

CREATE INDEX idx_articles_fts ON articles USING GIN (to_tsvector('simple', content));

SELECT * FROM articles 
WHERE to_tsvector('simple', content) @@ to_tsquery('simple', 'فرش & کاشان');

pg_trgm – جستجوی fuzzy

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- LIKE با wildcard در ابتدا - معمولاً ایندکس نمی‌شود
SELECT * FROM products WHERE name LIKE '%کاشان%';   -- با gin_trgm_ops سریع!

-- شباهت
SELECT * FROM products WHERE similarity(name, 'کاشان') > 0.3;
SELECT * FROM products WHERE name % 'کشان';   -- شباهت حدودی - حتی غلط املایی

GiST – Generalized Search Tree

GiST برای داده‌هایی که ترتیب طبیعی ندارند: مکان (geometry)، Range، tsvector، انواع سفارشی.

روی Range

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    period TSTZRANGE
);

CREATE INDEX idx_reservations_period ON reservations USING GIST (period);

-- جستجوی overlap
SELECT * FROM reservations 
WHERE period && '[2026-05-01, 2026-05-10)'::tstzrange;

روی Geometry (با PostGIS)

CREATE EXTENSION postgis;

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOGRAPHY(POINT)
);

CREATE INDEX idx_cities_location ON cities USING GIST (location);

-- 100 شهر نزدیک تهران
SELECT name, ST_Distance(location, ST_GeographyFromText('POINT(51.4 35.7)')) AS dist
FROM cities
ORDER BY location <-> ST_GeographyFromText('POINT(51.4 35.7)')
LIMIT 100;

Exclusion Constraint با GiST

CREATE TABLE reservations (
    room_id INTEGER,
    period TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);
-- جلوگیری از رزرو همزمان یک اتاق

BRIN – Block Range Index

BRIN ایندکسی فوق‌العاده کوچک برای جداول بزرگ که داده در آن مرتب وارد می‌شود (مثل log‌ها):

CREATE TABLE access_logs (
    id BIGSERIAL,
    accessed_at TIMESTAMPTZ DEFAULT NOW(),
    ip_address INET,
    user_id INTEGER,
    page TEXT
);

-- B-Tree روی timestamp برای 1 میلیارد ردیف:
-- ~30 GB!

-- BRIN روی timestamp:
CREATE INDEX idx_logs_time_brin ON access_logs USING BRIN (accessed_at);
-- ~50 MB ! (600 برابر کوچک‌تر)

-- query‌های range همچنان سریع
SELECT * FROM access_logs 
WHERE accessed_at BETWEEN '2026-01-01' AND '2026-01-31';

Partial Index – ایندکس جزئی

ایندکس فقط روی زیرمجموعه‌ای از ردیف‌ها:

-- معمولاً 99% ردیف‌ها active هستند
CREATE INDEX idx_users_email ON users(email);  -- بزرگ

-- فقط user‌های banned را ایندکس می‌کند (کوچک‌تر، سریع‌تر)
CREATE INDEX idx_users_banned ON users(email) 
WHERE status = 'banned';

-- query‌هایی که condition را دارند، از ایندکس استفاده می‌کنند:
SELECT * FROM users WHERE status = 'banned' AND email = 'x@y.com';

کاربردهای رایج

-- فقط orders فعال
CREATE INDEX idx_active_orders ON orders(user_id) 
WHERE status IN ('pending', 'processing');

-- فقط non-NULL
CREATE INDEX idx_users_phone ON users(phone) 
WHERE phone IS NOT NULL;

-- soft delete
CREATE INDEX idx_products_active ON products(name) 
WHERE deleted_at IS NULL;

Expression Index – روی محاسبه

-- جستجو case-insensitive کند است
SELECT * FROM users WHERE LOWER(email) = LOWER('Ali@Example.com');

-- ایندکس روی expression
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- حالا سریع است:
SELECT * FROM users WHERE LOWER(email) = LOWER('Ali@Example.com');

-- ایندکس روی تاریخ بدون ساعت
CREATE INDEX idx_logs_date ON logs(DATE(created_at));
SELECT * FROM logs WHERE DATE(created_at) = '2026-05-01';

-- ایندکس روی JSON expression
CREATE INDEX idx_products_color 
ON products ((attributes->>'color'));

-- ایندکس روی محاسبه
CREATE INDEX idx_orders_total_with_tax 
ON orders ((subtotal + tax));

Covering Index – INCLUDE

گاهی می‌خواهیم ستون‌هایی برای خواندن در ایندکس باشند، بدون اینکه scan روی جدول لازم باشد (Index-Only Scan):

-- بدون INCLUDE
CREATE INDEX idx_orders_user ON orders(user_id);

SELECT user_id, total FROM orders WHERE user_id = 1;
-- باید جدول را برای total بخواند ← Heap Fetch

-- با INCLUDE
CREATE INDEX idx_orders_user_inc ON orders(user_id) INCLUDE (total, status);

SELECT user_id, total, status FROM orders WHERE user_id = 1;
-- Index-Only Scan ← همه‌چیز در ایندکس

Unique Index پیچیده

-- یکتا فقط در ردیف‌های فعال
CREATE UNIQUE INDEX idx_active_email 
ON users(email) 
WHERE deleted_at IS NULL;

-- اجازه می‌دهد چند کاربر deleted با همان email باشد

-- یکتا case-insensitive
CREATE UNIQUE INDEX idx_email_ci 
ON users(LOWER(email));

چه زمانی ایندکس نگذاریم؟

• جداول کوچک (< 1000 ردیف) – sequential scan سریع‌تر است
• ستون‌هایی که زیاد UPDATE می‌شوند – ایندکس هم باید بروز شود
• ستون‌های با کاردینالیتی پایین (مثل bool) – مگر با partial index
• جداول write-heavy با خواندن کم

-- ایندکس روی gender با cardinality 2 - تقریباً بی‌فایده
CREATE INDEX idx_users_gender ON users(gender);  -- ❌

-- اما این مفید است:
CREATE INDEX idx_users_female ON users(id) WHERE gender = 'female';

EXPLAIN – بررسی استفاده از ایندکس

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE user_id = 100 AND status = 'pending';

-- خروجی:
-- Bitmap Heap Scan on orders (cost=4.45.....)
--   Recheck Cond: ((user_id = 100) AND (status = 'pending'))
--   ->  Bitmap Index Scan on idx_orders_user_status
-- 
-- Index Scan using idx_... ← خوب
-- Seq Scan ← بد (ایندکس مفید نیست یا وجود ندارد)

نگهداری ایندکس‌ها

-- بازسازی ایندکس‌های bloat شده
REINDEX INDEX idx_orders_user;
REINDEX TABLE orders;

-- در پروداکشن - بدون lock
REINDEX INDEX CONCURRENTLY idx_orders_user;

-- لیست ایندکس‌ها و سایز
SELECT 
    schemaname, tablename, indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan AS times_used
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- ایندکس‌های unused
SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
-- این‌ها شاید قابل حذف باشند

Index Bloat

با گذشت زمان و UPDATE/DELETE، ایندکس‌ها bloat می‌شوند (فضای مرده). راه‌حل:

-- بررسی bloat (نیاز به extension pgstattuple)
CREATE EXTENSION pgstattuple;

SELECT * FROM pgstatindex('idx_orders_user');
-- index_size, leaf_pages, density, ...

-- REINDEX CONCURRENTLY بهترین راه‌حل (PG 12+)
REINDEX INDEX CONCURRENTLY idx_orders_user;

CREATE INDEX CONCURRENTLY

-- ساخت ایندکس بدون lock کردن جدول (مهم در پروداکشن)
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);

-- اگر شکست خورد:
SELECT * FROM pg_indexes WHERE indexname = 'idx_orders_user';
-- ایندکس INVALID خواهد بود
DROP INDEX idx_orders_user;
-- و دوباره بسازید

بهترین شیوه‌ها

• برای equality و range: B-Tree
• برای JSONB، array، FTS: GIN
• برای range types، geometry: GiST
• برای جداول log بزرگ مرتب: BRIN
• ترتیب ستون‌ها در multi-column مهم است
• Partial Index برای ردیف‌های کم اما پرکار
• Expression Index برای محاسبات تکراری
• INCLUDE برای Index-Only Scan
• در پروداکشن همیشه CONCURRENTLY
• ایندکس‌های unused را حذف کنید (overhead دارند)

جمع‌بندی

• B-Tree: پیش‌فرض، برای = و <، >
• Hash: فقط =، معمولاً B-Tree بهتر
• GIN: array، JSONB، FTS، trigram
• GiST: range، geometry، exclusion constraints
• BRIN: time-series، log‌های مرتب، حافظه کم
• Partial: فقط روی subset
• Expression: روی محاسبه
• Covering (INCLUDE): Index-Only Scan