GIN، GiST و BRIN: ایندکس مناسب برای jsonb، آرایه، بازه و جدول‌های عظیم

وقتی B-tree جواب نمی‌دهد

B-tree برای مقایسه‌ی مقدارهای مرتب‌شدنی (=، <، BETWEEN، ORDER BY) ساخته شده است. اما «کدام نقشه‌ها برچسب ابریشمی دارند؟»، «کدام بازرسی‌ها عیب رج‌کشی دارند؟» یا «کدام رزروها با این بازه هم‌پوشانی دارند؟» سؤال مرتب‌سازی نیستند؛ برای آن‌ها پستگرس ایندکس‌های دیگری دارد.

GIN برای jsonb و آرایه

GIN یک «ایندکس معکوس» است: برای هر کلید یا عضو، فهرست ردیف‌هایی که آن را دارند. مثل فهرست موضوعی انتهای کتاب.

-- آرایه‌ی برچسب نقشه‌ها
CREATE INDEX designs_tags_gin ON factory.designs USING gin (tags);
SELECT code FROM factory.designs WHERE tags @> ARRAY['ابریشم'];
SELECT code FROM factory.designs WHERE tags && ARRAY['کلاسیک', 'افشان'];

-- jsonb عیوب کنترل کیفیت؛ jsonb_path_ops کوچک‌تر و سریع‌تر برای @>
CREATE INDEX qc_defects_gin ON factory.qc_inspections USING gin (defects jsonb_path_ops);
SELECT id FROM factory.qc_inspections WHERE defects @> '{"رج_کشی": 2}';

کلاس پیش‌فرض jsonb_ops عملگرهای وجود کلید (?، ?|، ?&) را هم پشتیبانی می‌کند؛ jsonb_path_ops فقط @> و jsonpath (@?، @@) را، اما معمولاً دو تا سه برابر کوچک‌تر است. نکته‌ی مهم: defects->>'رج_کشی' = '2' از ایندکس GIN استفاده نمی‌کند؛ یا کوئری را با @> بنویسید یا یک expression index از نوع B-tree روی همان عبارت بسازید.

GiST برای بازه و نزدیکی

CREATE INDEX runs_during_gist ON factory.production_runs USING gist (during);
SELECT loom_id FROM factory.production_runs
WHERE during && tstzrange('2026-03-01 08:00+03:30', '2026-03-01 16:00+03:30');

قید EXCLUDE فصل ۳ هم پشت صحنه همین ایندکس GiST را می‌سازد. GiST «زیان‌ده» است: ممکن است ردیف‌های نامربوط را هم پیشنهاد دهد و پستگرس آن‌ها را دوباره بررسی (recheck) می‌کند.

BRIN برای جدول‌های لاگ

جدول قرائت حسگرهای دستگاه بافندگی روزی میلیون‌ها ردیف می‌گیرد و ردیف‌ها به ترتیب زمان اضافه می‌شوند. BRIN برای هر گروه صفحه (پیش‌فرض ۱۲۸ صفحه) فقط کمینه و بیشینه را نگه می‌دارد؛ ایندکسی چند کیلوبایتی برای جدول چند ده گیگابایتی:

CREATE INDEX sensor_ts_brin ON factory.loom_sensor_log USING brin (recorded_at)
WITH (pages_per_range = 64);

نکته‌هایی که کمتر کسی می‌داند

• GIN درج‌ها را ابتدا در یک «pending list» جمع می‌کند (fastupdate)؛ اگر یک INSERT گاهی بی‌دلیل کند است، احتمالاً نوبت تخلیه‌ی همین فهرست به آن افتاده. gin_pending_list_limit را کم کنید یا fastupdate را خاموش کنید.
• همبستگی ترتیب فیزیکی را در pg_stats.correlation ببینید؛ BRIN فقط وقتی مفید است که این عدد نزدیک 1 یا ‎-1 باشد. UPDATEهای پراکنده و حذف‌های قدیمی این همبستگی را خراب می‌کنند.
• کلاس brin minmax_multi_ops (نسخه‌ی 14 به بعد) چند بازه برای هر گروه نگه می‌دارد و با داده‌ای که کمی نامرتب است بهتر کنار می‌آید.
• برای ایندکس ترکیبی از ستون عادی و jsonb در یک GIN، اکستنشن btree_gin لازم است؛ مثل btree_gist برای GiST.
• GiST از جست‌وجوی «K نزدیک‌ترین» پشتیبانی می‌کند: ORDER BY location <-> point(51.43, 33.98) LIMIT 5 بدون محاسبه‌ی فاصله برای همه‌ی ردیف‌ها.