ڈیٹا بیس کے اشاریہ جات کیسے کام کرتے ہیں – PostgreSQL مثالوں کے ساتھ ایک عملی گائیڈ

ہر ڈویلپر کو آخر کار سست سوالات کا سامنا کرنا پڑتا ہے۔ میزیں سیکڑوں قطاروں سے بڑھ کر لاکھوں قطاروں تک پہنچ گئی ہیں، اور آپریشنز جو پہلے ملی سیکنڈ لیتے تھے اب سیکنڈ یا اس سے زیادہ وقت لگتے ہیں۔

ترمیم عام طور پر اشاریہ جات ہیں۔

ڈیٹا بیس انڈیکس ایک ڈیٹا ڈھانچہ ہے جو پورے ٹیبل کو اسکین کیے بغیر ڈیٹا بیس کو تیزی سے قطاریں تلاش کرنے میں مدد کرتا ہے۔ یہ نصابی کتاب کے پچھلے حصے میں انڈیکس کی طرح کام کرتا ہے۔ موضوع تلاش کرنے کے لیے ہر صفحہ کو پڑھنے کے بجائے، اشاریہ میں اس صفحہ نمبر کو دیکھیں اور اس پر جائیں۔

اس ٹیوٹوریل میں، آپ سیکھیں گے کہ اشاریہ جات اندرونی طور پر کیسے کام کرتے ہیں، PostgreSQL میں اشاریہ جات کو مؤثر طریقے سے کیسے بنایا اور استعمال کیا جائے، اور ان عام غلطیوں سے کیسے بچنا ہے جو اشاریہ جات کو بیکار یا نقصان دہ بنا سکتی ہیں۔

انڈیکس

شرطیں

مثال کی پیروی کرنے کے لیے، آپ کو ضرورت ہو گی:

• ایس کیو ایل کا بنیادی علم (منتخب کریں، داخل کریں، اپ ڈیٹ کریں، حذف کریں، کہاں، جوائن کریں)

• چل رہا ہے PostgreSQL مثال (ورژن 12 یا اس سے اوپر)

• SQL کلائنٹ مندرجہ ذیل ہے: psqlpgAdmin یا DBeaver

اگر آپ کے پاس پوسٹگری ایس کیو ایل مقامی طور پر انسٹال نہیں ہے، تو آپ نیون یا سوپا بیس جیسی سروسز سے مفت کلاؤڈ ہوسٹڈ مثالیں استعمال کر سکتے ہیں۔

ہمیں اشاریہ جات کی ضرورت کیوں ہے؟

اگر آپ درج ذیل استفسار چلاتے ہیں: SELECT * FROM users WHERE email="jane@example.com"ڈیٹا بیس کو مماثل قطاریں تلاش کرنا ہوں گی۔ اگر کوئی انڈیکس نہیں ہے تو، PostgreSQL مندرجہ ذیل کام کرتا ہے: ترتیب وار اسکین – ٹیبل میں ہر ایک قطار کو پڑھیں اور چیک کریں: email کالم ملتے ہیں۔

یہ 100 قطاروں والی میز کے لیے ٹھیک ہے۔ 10 ملین قطاروں والی میزوں کے لیے، یہ بہت سست ہے۔

اشاریہ جات اس مسئلے کو ایک علیحدہ ترتیب شدہ ڈیٹا ڈھانچہ بنا کر حل کرتے ہیں جو کالم کی قدروں کو قطار کی پوزیشنوں پر نقشہ بناتا ہے۔ 10 ملین قطاروں کو اسکین کرنے کے بجائے، PostgreSQL انڈیکس میں قدروں کو دیکھ سکتا ہے اور براہ راست مماثل قطاروں میں جا سکتا ہے۔ یہ استفسار کے اوقات کو سیکنڈ سے ملی سیکنڈ تک کم کر سکتا ہے۔

تاہم، انڈیکس آزاد نہیں ہیں۔ اس کے فوائد اور نقصانات ہیں جنہیں آپ کو کہیں بھی شامل کرنے سے پہلے سمجھنا ہوگا۔ آپ اس ٹیوٹوریل میں ان فوائد اور نقصانات کے بارے میں سیکھیں گے۔

اشاریہ جات اندرونی طور پر کیسے کام کرتے ہیں۔

PostgreSQL میں ڈیفالٹ انڈیکس کی قسم ہے۔ بی درخت (متوازن درخت)۔ B-trees کے کام کرنے کے طریقہ کو سمجھنا آپ کو کب اور کیسے انڈیکس کرنے کے بارے میں بہتر فیصلے کرنے میں مدد کرے گا۔

B-trees ڈیٹا کو ترتیب شدہ درجہ بندی کی تین سطحوں میں ترتیب دیتے ہیں۔

1. جڑ نوڈ – درختوں کی چوٹی۔ اس میں کئی اقدار ہیں جو ڈیٹا کو وسیع رینج میں تقسیم کرتی ہیں۔

2. اندرونی نوڈ – ہر ایک دائرہ کار میں تنگ ہو جاتا ہے۔

3. لیف نوڈ – سب سے نچلی سطح۔ یہ ٹیبل میں متعلقہ قطار کے پوائنٹر کے ساتھ اصل اشاریہ کی قدر رکھتا ہے۔

جب PostgreSQL ایک قدر تلاش کرنے کے لیے B-tree انڈیکس کا استعمال کرتا ہے، تو یہ جڑ سے شروع ہوتا ہے اور ایک ایسے راستے کی پیروی کرتا ہے جو ہدف کی قدر سے مماثل ہو، اندرونی نوڈس سے گزرتا ہے جب تک کہ یہ درست لیف نوڈ تک نہ پہنچ جائے۔ اس راستے کو اے کہا جاتا ہے۔ درخت کا راستہیہاں تک کہ لاکھوں قطاروں والی میزوں کے لیے، عام طور پر صرف 3 یا 4 قدموں کی ضرورت ہوتی ہے۔

اسے فون بک کی طرح سوچیں۔ یہ پہلے صفحے سے شروع نہیں ہوتا اور تمام نام پڑھتا ہے۔ موٹے طور پر، آپ دائیں حصے (جڑ) کو کھولتے ہیں، اسے دائیں صفحہ (اندرونی نوڈ) تک محدود کرتے ہیں، اور پھر اس صفحہ (لیف نوڈ) پر موجود اشیاء کو اسکین کرتے ہیں۔

یہ ترتیب شدہ ڈھانچہ بھی یہی وجہ ہے کہ بی ٹری انڈیکس رینج کے سوالات کے لیے اچھی طرح کام کرتے ہیں جیسے: WHERE price > 50 AND price < 100. ڈیٹا بیس درخت میں ایک نقطہ آغاز تلاش کرتا ہے اور پھر پہلے سے ترتیب دیے گئے لیف نوڈس کے ذریعے آگے کو اسکین کرتا ہے۔

اپنا پہلا انڈیکس کیسے بنائیں

آئیے ایک عملی مثال بنائیں۔ آئیے ایک ٹیبل بناتے ہیں، اس میں ڈیٹا لوڈ کرتے ہیں، اور دیکھتے ہیں کہ اشاریہ جات میں کیا فرق پڑتا ہے۔

مرحلہ 1 - ٹیبل بنائیں اور نمونہ ڈیٹا داخل کریں۔

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

اب بڑی تعداد میں قطاریں ڈالیں تاکہ آپ کارکردگی کا فرق دیکھ سکیں۔ یہ نمونہ ڈیٹا کی 500,000 قطاریں بناتا ہے۔

INSERT INTO customers (first_name, last_name, email, city)
SELECT
    'User' || gs,
    'Last' || gs,
    'user' || gs || '@example.com',
    (ARRAY['Lagos', 'London', 'New York', 'Berlin', 'Tokyo'])[1 + (gs % 5)]
FROM generate_series(1, 500000) AS gs;

مرحلہ 2 - انڈیکس کے بغیر سوال

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email="user250000@example.com";

آپ کو مندرجہ ذیل کی طرح آؤٹ پٹ نظر آئے گا:

Seq Scan on customers  (cost=0.00..11374.00 rows=1 width=52) (actual time=45.123..91.456 rows=1 loops=1)
  Filter: ((email)::text="user250000@example.com"::text)
  Rows Removed by Filter: 499999
Planning Time: 0.085 ms
Execution Time: 91.502 ms

یہاں اہم تفصیلات یہ ہیں: Seq Scan - PostgreSQL نے ایک میچ تلاش کرنے کے لیے تمام 500,000 قطاروں کو اسکین کیا۔ 499,999 قطاریں فلٹر کی گئیں۔ کیا کام کی بربادی.

مرحلہ 3 - انڈیکس بنائیں

CREATE INDEX idx_customers_email ON customers (email);

یہ ایک بی ٹری انڈیکس بناتا ہے۔ email گرمی نام idx_customers_email عام ناموں کے رواجوں پر عمل کریں۔ idx_ اس کے بعد سابقہ، ٹیبل کا نام، اور کالم کا نام آتا ہے۔

مرحلہ 4 - انڈیکس کے لحاظ سے سوال

دوبارہ وہی استفسار چلائیں۔

EXPLAIN ANALYZE
SELECT * FROM customers WHERE email="user250000@example.com";

اب آپ کچھ اس طرح دیکھیں گے:

Index Scan using idx_customers_email on customers  (cost=0.42..8.44 rows=1 width=52) (actual time=0.034..0.036 rows=1 loops=1)
  Index Cond: ((email)::text="user250000@example.com"::text)
Planning Time: 0.112 ms
Execution Time: 0.058 ms

اسکین کی قسم اس سے تبدیل ہوئی: Seq Scan کو Index Scan. عمل درآمد کا وقت ~91ms سے ~0.06ms تک کم ہو گیا۔ یہ SQL کی ایک لائن پر تقریباً 1,500x کارکردگی میں بہتری ہے۔

استعمال کرنے کا طریقہ EXPLAIN ANALYZE کارکردگی کی پیمائش

EXPLAIN ANALYZE یہ سمجھنے کا سب سے اہم ٹول ہے کہ PostgreSQL سوالات کو کیسے انجام دیتا ہے۔ ہم اسے پچھلے حصے میں پہلے ہی دیکھ چکے ہیں، لیکن آئیے اسے توڑتے ہیں کہ آؤٹ پٹ کا کیا مطلب ہے۔

EXPLAIN ANALYZE SELECT * FROM customers WHERE city = 'Lagos';

آؤٹ پٹ کچھ معلومات دکھاتا ہے:

• اسکین کی قسم — آیا PostgreSQL نے ترتیب وار اسکین، انڈیکس اسکین، بٹ میپ انڈیکس اسکین، یا رسائی کا دوسرا طریقہ استعمال کیا ہے۔

• خرچ - صوابدیدی اکائیوں میں تخمینہ لاگت۔ پہلا نمبر ابتدائی لاگت ہے اور دوسرا نمبر کل لاگت ہے۔

• لائن — PostgreSQL کو کتنی قطاریں ملنے کی توقع تھی اور اسے درحقیقت کتنی قطاریں ملیں۔

• حقیقی وقت - اصل وقت (ملی سیکنڈ میں) استفسار پر عمل کرنے میں لگتا ہے۔

• قطاریں فلٹر کے ذریعے ہٹا دی گئیں۔ - سکین کی گئی قطاروں کی تعداد لیکن معیار سے مماثل نہیں۔

اگر آپ دیکھتے ہیں Seq Scan ایک اختیاری WHERE شق کے ساتھ بڑی جدولوں میں، یہ عام طور پر اس بات کی علامت ہے کہ ایک اشاریہ کی ضرورت ہے۔ اگر آپ دیکھتے ہیں Index Scan یا Index Only Scanانڈیکس کام کر رہا ہے۔

ایک بات ذہن میں رکھیں: EXPLAIN بغیر ANALYZE یہ حقیقت میں استفسار پر عمل کیے بغیر منصوبہ دکھاتا ہے۔ EXPLAIN ANALYZE استفسار کو چلائیں اور وقت کا اصل ڈیٹا ڈسپلے کریں۔ ہمیشہ استعمال کریں۔ EXPLAIN ANALYZE کارکردگی کی جانچ کرتے وقت، تباہ کن سوالات پر نگاہ رکھیں۔ EXPLAIN ANALYZE DELETE FROM ... دراصل قطار کو حذف کر دیتا ہے۔ اسے ایک لین دین میں لپیٹ کر واپس رول کریں۔

BEGIN;
EXPLAIN ANALYZE DELETE FROM customers WHERE city = 'Berlin';
ROLLBACK;

PostgreSQL میں انڈیکس کی اقسام

PostgreSQL مختلف استفسار کے نمونوں کے لیے موزوں کئی انڈیکس اقسام کو سپورٹ کرتا ہے۔

بی ٹری (پہلے سے طے شدہ)

B-trees پہلے سے طے شدہ انڈیکس کی قسم ہیں اور زیادہ تر استعمال کے معاملات کا احاطہ کرتی ہیں۔ مساوات کی جانچ کی حمایت کرتا ہے (=) رینج کا سوال (<, >, <=, >=, BETWEENsort(ORDER BY)، اور IS NULL / IS NOT NULL چیکر

-- These are equivalent – B-tree is the default
CREATE INDEX idx_name ON customers (last_name);
CREATE INDEX idx_name ON customers USING btree (last_name);

B-trees استعمال کریں جب کوئی اور چیز استعمال کرنے کی کوئی خاص وجہ نہ ہو۔

چرس

ہیش اشاریہ جات صرف مساوات کے موازنہ کے لیے موزوں ہیں (=)۔ رینج کے سوالات یا چھانٹنے کی حمایت نہیں کرتا ہے۔ درحقیقت، B-trees مساوات کی جانچ کو اتنی تیزی سے سنبھالتے ہیں کہ ہیش انڈیکس کی ضرورت کم ہی ہوتی ہے۔

CREATE INDEX idx_email_hash ON customers USING hash (email);

ہیش اشاریہ جات پر صرف اس صورت میں غور کریں جب آپ کے پاس ایک بہت بڑی میز ہے جس میں بار بار مساوات صرف نظر آتی ہے اور آپ انڈیکس کی کچھ جگہ بچانا چاہتے ہیں۔

GIN (عمومی الٹا انڈیکس)

GIN اشاریہ جات ان اقدار کے لیے ڈیزائن کیے گئے ہیں جن میں ایک سے زیادہ عناصر ہوتے ہیں، جیسے کہ arrays، JSONB دستاویزات، یا فل ٹیکسٹ سرچ ویکٹر۔ ہر قطار میں ایک واحد قدر کو انڈیکس کرنے کے بجائے، GIN ایک قدر کے اندر موجود تمام عناصر کو انڈیکس کرتا ہے۔

-- Add a JSONB column
ALTER TABLE customers ADD COLUMN preferences JSONB DEFAULT '{}';

-- Index the JSONB column
CREATE INDEX idx_preferences ON customers USING gin (preferences);

-- Now this query uses the GIN index
SELECT * FROM customers WHERE preferences @> '{"newsletter": true}';

JSONB ڈیٹا کے اندر استفسار کرتے وقت GIN استعمال کریں اور یہ استعمال کرتے ہوئے صفوں کو بازیافت کریں: @> یا &&یا استعمال کرکے مکمل متن کی تلاش کریں۔ tsvector.

عام تلاش کا درخت (GiST)

جی ایس ٹی انڈیکس ہندسی ڈیٹا، رینج، اور مکمل متن کی تلاش کو سپورٹ کرتے ہیں۔ یہ عام طور پر جغرافیائی استفسارات کے لیے PostGIS کے ساتھ استعمال ہوتا ہے۔

-- Range type example
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    duration TSRANGE
);

CREATE INDEX idx_event_duration ON events USING gist (duration);

-- Find overlapping events
SELECT * FROM events WHERE duration && '[2025-01-01, 2025-01-31]'::tsrange;

جب آپ مقامی ڈیٹا، رینج کی اقسام کے ساتھ کام کرتے ہیں، یا آپریٹرز پر مشتمل گھونسلے کی ضرورت ہو تو GiST استعمال کریں۔

BRIN (بلاک رینج انڈیکس)

BRIN اشاریہ جات بہت چھوٹے ہوتے ہیں اور بڑی میزوں پر اچھی طرح کام کرتے ہیں جہاں فزیکل قطار ترتیب کو انڈیکس شدہ کالموں کی قدروں کے ساتھ جوڑا جاتا ہے۔ ایک عام مثال صرف ضمیمہ ٹیبل میں ٹائم اسٹیمپ کالم ہے جہاں نئی ​​قطاروں میں ہمیشہ بعد کا ٹائم اسٹیمپ ہوتا ہے۔

CREATE INDEX idx_created_at_brin ON customers USING brin (created_at);

ہر قطار کو انفرادی طور پر انڈیکس کرنے کے بجائے، BRIN قطاروں کے ہر بلاک کے لیے خلاصہ معلومات (کم سے کم/زیادہ سے زیادہ اقدار) اسٹور کرتا ہے۔ یہ انڈیکس کو بی ٹری سے بہت چھوٹا بناتا ہے، لیکن صرف اس صورت میں بہتر کام کرتا ہے جب ڈیٹا کو قدرتی طور پر ترتیب دیا گیا ہو۔

BRIN کا استعمال بہت بڑی صرف اپنڈ ٹیبلز کے لیے کریں جن میں قدرتی طور پر ترتیب دیا گیا ڈیٹا ہوتا ہے، جیسے لاگز، ایونٹس، یا ٹائم سیریز ڈیٹا۔

ایک جامع انڈیکس کیسے بنایا جائے۔

ایک کمپوزٹ انڈیکس (جسے ملٹی کالم انڈیکس بھی کہا جاتا ہے) میں ایک سے زیادہ کالم ہوتے ہیں۔ یہ مفید ہے اگر آپ کے سوالات کثرت سے فلٹر کریں یا ایک ساتھ متعدد کالموں کو ترتیب دیں۔

CREATE INDEX idx_city_lastname ON customers (city, last_name);

ایک جامع اشاریہ میں کالموں کی ترتیب اہم ہے۔ PostgreSQL اس انڈیکس کو ان سوالات میں استعمال کر سکتا ہے جو فلٹر کرتے ہیں: city تنہا یا دونوں city اور last_name. لیکن یہ ہے نہیں کر سکتے سوالات کو فلٹر کرنے کے لیے اس انڈیکس کو مؤثر طریقے سے استعمال کریں۔ last_name.

اسے ایک فون بک کی طرح سوچیں، پہلے شہر کے لحاظ سے اور پھر ہر شہر میں صنف کے لحاظ سے ترتیب دیا گیا ہے۔ آپ لاگوس میں کسی کو بھی آسانی سے تلاش کر سکتے ہیں۔ آپ لاگوس میں "Adeyemi" نام کے ساتھ ہر کسی کو بھی تلاش کر سکتے ہیں۔ لیکن ہر شہر میں "Adeyemi" نامی ہر شخص کو تلاش کرنے کے لیے، آپ کو پوری کتاب کو اسکین کرنا پڑے گا۔

اس اصول کو کہتے ہیں۔ سب سے بایاں سابقہ ​​اصول: PostgreSQL ان سوالات کے لیے جامع اشاریہ جات کا استعمال کر سکتا ہے جن میں انڈیکس کا سب سے بائیں کالم شامل ہوتا ہے، لیکن ان سوالات کے لیے نہیں جو اسے چھوڑ دیتے ہیں۔

-- ✅ Uses the index (matches leftmost column)
SELECT * FROM customers WHERE city = 'Lagos';

-- ✅ Uses the index (matches both columns, left to right)
SELECT * FROM customers WHERE city = 'Lagos' AND last_name="Adeyemi";

-- ❌ Cannot use this index efficiently (skips the leftmost column)
SELECT * FROM customers WHERE last_name="Adeyemi";

اپنے کالم کی ترتیب کا تعین کرتے وقت، سب سے زیادہ منتخب کالم پہلے رکھیں، یعنی وہ جو آپ کے نتائج کو سب سے زیادہ تنگ کرتے ہیں۔

جزوی انڈیکس کیسے بنایا جائے۔

ایک جزوی انڈیکس ٹیبل میں قطاروں کا صرف ذیلی سیٹ پر مشتمل ہوتا ہے۔ آپ اپنی انڈیکس کی تعریف میں WHERE شق کا استعمال کرکے ذیلی سیٹوں کی وضاحت کرتے ہیں۔

ڈیٹا کے صرف مخصوص حصوں سے استفسار کرتے وقت یہ مفید ہے۔ مثال کے طور پر، orders آپ اکثر زیر التواء آرڈرز کے لیے اپنے ٹیبل سے استفسار کرتے ہیں، لیکن مکمل شدہ آرڈرز شاذ و نادر ہی دیکھتے ہیں۔

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Only index rows where status is 'pending'
CREATE INDEX idx_orders_pending ON orders (customer_id)
WHERE status="pending";

یہ انڈیکس مکمل انڈیکس سے چھوٹا ہے کیونکہ یہ ان تمام قطاروں کو چھوڑ دیتا ہے جو WHERE حالت سے مماثل نہیں ہیں۔ چھوٹے اشاریہ جات کم ڈسک کی جگہ اور میموری استعمال کرتے ہیں، اور لکھنے کے دوران برقرار رکھنے کے لیے تیز تر ہوتے ہیں۔

ایک اشاریہ استعمال کرنے کے لیے، استفسار کی WHERE شق کا اشاریہ کے حالات سے مماثل ہونا چاہیے۔

-- ✅ Uses the partial index
SELECT * FROM orders WHERE status="pending" AND customer_id = 42;

-- ❌ Cannot use the partial index (different status)
SELECT * FROM orders WHERE status="shipped" AND customer_id = 42;

ایکسپریشن انڈیکس کیسے بنایا جائے۔

بعض صورتوں میں، آپ کو خام کالم کی قدروں کے بجائے کسی فنکشن یا اظہار کا نتیجہ انڈیکس کرنے کی ضرورت پڑ سکتی ہے۔ ایکسپریشن انڈیکس (جسے فنکشن انڈیکس بھی کہا جاتا ہے) اسے سنبھالتے ہیں۔

ایک عام منظر نامہ کیس غیر حساس ای میل سوالات ہے۔ اگر آپ کا استفسار استعمال کرتا ہے: LOWER(email)جنرل انڈیکس email مددگار نہیں — PostgreSQL فنکشن کالز کو دوسرے اظہار کی طرح سمجھتا ہے۔

-- Regular index on email – won't help with LOWER() queries
CREATE INDEX idx_email ON customers (email);

-- This query does NOT use the index above
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

اسے ٹھیک کرنے کے لیے، اظہار پر ہی ایک انڈیکس بنائیں۔

CREATE INDEX idx_email_lower ON customers (LOWER(email));

اب استعمال کرتے ہوئے ایک سوال: LOWER(email) WHERE شق میں درج ذیل انڈیکس کا استعمال کریں:

-- ✅ Uses the expression index
SELECT * FROM customers WHERE LOWER(email) = 'user100@example.com';

اصول سادہ ہیں۔ استفسار کا اظہار انڈیکس میں موجود اظہار سے بالکل مماثل ہونا چاہیے۔ جب انڈیکسنگ آن ہوتی ہے۔ LOWER(email)اسے استفسارات میں بھی استعمال کیا جانا چاہیے۔ LOWER(email).

ایک منفرد انڈیکس کیسے بنایا جائے۔

ایک منفرد انڈیکس اس بات کو یقینی بناتا ہے کہ انڈیکس شدہ کالم پر کوئی بھی دو قطاریں ایک جیسی قدر (یا اقدار کا مجموعہ) نہیں رکھتی ہیں۔ یہ دو مقاصد کو پورا کرتا ہے: اس کا مطلب ہے ڈیٹا کی سالمیت کو بڑھانا اور تیزی سے تلاش کرنا۔

CREATE UNIQUE INDEX idx_customers_email_unique ON customers (email);

اگر آپ ڈپلیکیٹ ویلیو داخل کرنے کی کوشش کرتے ہیں، تو PostgreSQL آپریشن سے انکار کر دیتا ہے۔

INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Test', 'User', 'user1@example.com', 'Lagos');
-- ERROR: duplicate key value violates unique constraint "idx_customers_email_unique"

آپ سوچ رہے ہوں گے کہ یہ ایک منفرد رکاوٹ سے کیسے مختلف ہے۔ اندرونی طور پر، PostgreSQL ایک منفرد انڈیکس بنا کر منفرد رکاوٹ کو نافذ کرتا ہے۔ دونوں عملی طور پر ایک جیسے ہیں۔

فرق نیت کا ہے۔ انوکھی رکاوٹیں ڈیٹا کی سالمیت کے اصولوں کا اظہار کرتی ہیں، جبکہ منفرد اشاریہ جات واضح طور پر استفسار کی کارکردگی پر توجہ مرکوز کرتے ہیں، بونس کے طور پر انفرادیت کے ساتھ۔

اشاریہ جات کا انتظام کیسے کریں۔

جیسے جیسے آپ کا ڈیٹا بیس بڑھتا ہے، آپ کو اشاریہ جات کا معائنہ، نگرانی اور برقرار رکھنے کی ضرورت ہوتی ہے۔

میز پر تمام اشاریہ جات کی فہرست کیسے بنائیں

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename="customers";

یہ میز پر تمام اشاریہ جات کے نام اور مکمل تعریفیں دکھاتا ہے۔

انڈیکس کا سائز کیسے چیک کریں۔

SELECT
    pg_size_pretty(pg_relation_size('idx_customers_email')) AS index_size;

تمام اشاریہ جات اور ان کے سائز کے وسیع تر نظارے کے لیے:

SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname="customers"
ORDER BY pg_relation_size(indexrelid) DESC;

غیر استعمال شدہ اشاریہ جات کو کیسے تلاش کریں۔

اشاریہ جات جو کبھی استعمال نہیں ہوتے ہیں ڈسک کی جگہ ضائع کرتے ہیں اور لکھتے ہیں سست۔ آپ اسے چیک کرکے تلاش کرسکتے ہیں۔ pg_stat_user_indexes:

SELECT
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname="customers"
AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

اگر کوئی انڈیکس ہے۔ idx_scan = 0 یہ عام استعمال کی مدت کے بعد ہٹانے کے تابع ہے. کاروبار کا پورا دور دیکھیں۔ کچھ اشاریے صرف ماہانہ رپورٹوں میں یا موسمی کارروائیوں کے دوران استعمال ہوتے ہیں۔

انڈیکس کو کیسے حذف کریں۔

DROP INDEX IF EXISTS idx_customers_email;

پروڈکشن ٹیبل پر انڈیکس چھوڑنے اور رائٹ لاکنگ کو روکنے کے لیے، استعمال کریں: CONCURRENTLY:

DROP INDEX CONCURRENTLY IF EXISTS idx_customers_email;

انڈیکس کو دوبارہ کیسے بنایا جائے۔

وقت گزرنے کے ساتھ، اشاریہ جات پھولے ہوئے ہو سکتے ہیں کیونکہ قطاریں ڈالی جاتی ہیں، اپ ڈیٹ ہوتی ہیں اور حذف ہوتی ہیں۔ آپ جگہ دوبارہ حاصل کرنے کے لیے انڈیکس کو دوبارہ بنا سکتے ہیں۔

REINDEX INDEX idx_customers_email;

یا، میز پر تمام اشاریہ جات کو دوبارہ بنائیں۔

REINDEX TABLE customers;

پیداواری نظام میں ہم استعمال کرتے ہیں: REINDEX CONCURRENTLY (PostgreSQL 12+) ٹیبل لاکنگ کو روکنے کے لیے:

REINDEX INDEX CONCURRENTLY idx_customers_email;

جب اشاریے مددگار ہونے کے بجائے خراب ہو جاتے ہیں۔

اشاریہ جات مفت نہیں ہیں۔ ہر اشاریہ جو آپ شامل کرتے ہیں اس پر لاگت آتی ہے۔

1. اوپر لکھیں۔ - کسی بھی INSERT، UPDATE، یا DELETE کے لیے میز پر موجود تمام اشاریہ جات کو بھی اپ ڈیٹ کرنا چاہیے۔ اگر کسی ٹیبل میں 10 اشاریہ جات ہیں اور آپ ایک قطار داخل کرتے ہیں، تو PostgreSQL 11 تحریریں انجام دیتا ہے (ایک فی ٹیبل اور ایک فی انڈیکس)۔ بہت زیادہ تحریری سرگرمی کے ساتھ میزوں پر، بہت زیادہ اشاریہ جات کا ہونا ڈیٹا میں ترمیم کو نمایاں طور پر سست کر سکتا ہے۔

2. اسٹوریج کی لاگت - اشاریہ جات ڈسک کی جگہ استعمال کرتے ہیں۔ بڑی میزوں پر، اشاریہ جات خود میز جتنی جگہ لے سکتے ہیں، اور بعض اوقات زیادہ۔ آپ اسے چیک کر سکتے ہیں۔ pg_relation_size.

3. میموری کی کھپت - PostgreSQL میموری میں اکثر استعمال ہونے والے اشاریہ جات کیش کرتا ہے۔ مزید اشاریہ جات میموری پر زیادہ دباؤ ڈال سکتے ہیں، مفید ڈیٹا کو کیشے سے باہر دھکیل سکتے ہیں اور دیگر سوالات کو سست کر سکتے ہیں۔

4. دیکھ بھال کا بوجھ - اشاریہ جات کو وقتاً فوقتاً دیکھ بھال کی ضرورت ہوتی ہے (خالی کرنا، دوبارہ اشاریہ بنانا) اور اسکیما منتقلی زیادہ پیچیدہ ہو جاتی ہے۔

پوچھنا سوال یہ نہیں ہے کہ "کیا مجھے انڈیکس شامل کرنا چاہئے؟" بلکہ، "کیا پڑھنے کی کارکردگی کا فائدہ اس ٹیبل کے کام کے بوجھ کے لیے تحریری کارکردگی کی لاگت کا جواز پیش کرتا ہے؟"

عام غلطیاں جو آپ کو انڈیکس استعمال کرنے سے روکتی ہیں۔

آپ کے پاس ایک بہترین انڈیکس ہو سکتا ہے، لیکن PostgreSQL پھر بھی اسے نظر انداز کر سکتا ہے۔ سب سے عام وجوہات ہیں:

فنکشنز میں انڈیکس شدہ کالموں کو لپیٹنا

-- Index on email
CREATE INDEX idx_email ON customers (email);

-- ❌ PostgreSQL cannot use the index because of LOWER()
SELECT * FROM customers WHERE LOWER(email) = 'user1@example.com';

-- ✅ Fix: create an expression index on LOWER(email)
CREATE INDEX idx_email_lower ON customers (LOWER(email));

WHERE شق میں انڈیکس کالم پر لاگو فنکشن معیاری اشاریہ جات کے استعمال کو روکتے ہیں۔ فنکشن سے مماثل ایک ایکسپریشن انڈیکس درکار ہے۔

مضمر قسم کاسٹنگ

-- id is an INTEGER column with an index
-- ❌ Passing a string forces a type cast, which may prevent index usage
SELECT * FROM customers WHERE id = '42';

-- ✅ Use the correct type
SELECT * FROM customers WHERE id = 42;

اگر استفسار میں قدر کی قسمیں کالم کی اقسام سے مماثل نہیں ہیں، تو PostgreSQL کالموں کو مماثلت کے لیے کاسٹ کر سکتا ہے، انڈیکس کے استعمال کو روکتا ہے۔

متعدد کالموں پر OR شرائط استعمال کریں۔

-- ❌ OR across different columns can prevent index usage
SELECT * FROM customers WHERE email="user1@example.com" OR city = 'Lagos';

-- ✅ Rewrite as UNION for better index utilization
SELECT * FROM customers WHERE email="user1@example.com"
UNION
SELECT * FROM customers WHERE city = 'Lagos';

LIKE کے استفسار میں سر فہرست وائلڈ کارڈ

-- ❌ Leading wildcard cannot use a B-tree index
SELECT * FROM customers WHERE email LIKE '%@example.com';

-- ✅ Trailing wildcard CAN use a B-tree index
SELECT * FROM customers WHERE email LIKE 'user1%';

بی ٹری انڈیکس کو بائیں سے دائیں ترتیب دیا گیا ہے۔ معروف وائلڈ کارڈ (%something) کا مطلب ہے کہ ڈیٹا بیس ترتیب شدہ ڈھانچہ استعمال نہیں کر سکتا اور ایک ترتیب وار اسکین پر واپس آجائے گا۔ اگر آپ کو لاحقہ یا ذیلی اسٹرنگ کے ذریعہ تلاش کرنے کی ضرورت ہے، تو اس کے ساتھ ایک GIN انڈیکس پر غور کریں: pg_trgm توسیع

کم انتخاب

اگر کسی کالم میں قطاروں کی تعداد (کم سلیکٹیوٹی) کے نسبت کچھ منفرد قدریں ہیں، تو PostgreSQL فیصلہ کر سکتا ہے کہ ترتیب وار اسکین انڈیکس استعمال کرنے سے زیادہ تیز ہے۔

مثال کے طور پر، status ایک کالم میں صرف تین ممکنہ اقدار ہیں ('pending', 'shipped', 'delivered') ہر قدر ٹیبل کا تقریباً 1/3 حصہ لیتی ہے، status اکیلے، بہت کم فائدہ ہے. PostgreSQL کو ابھی بھی ٹیبل کا ایک بڑا حصہ پڑھنا پڑتا ہے، اور اضافی انڈیکس دیکھنے میں اوور ہیڈ شامل ہوتا ہے۔

ان صورتوں میں، جزوی اشاریہ جات اکثر ایک بہتر حل ہوتے ہیں۔

اشاریہ سازی کے بہترین طرز عمل

پیروی کرنے کے کلیدی اصولوں کا خلاصہ کرنے کے لیے:

1. انڈیکس کالم جو WHERE، JOIN، اور ORDER BY شقوں میں ظاہر ہوتا ہے۔ یہ وہ کالم ہے جس پر ڈیٹا بیس کو تلاش کرنا، میچ کرنا یا ترتیب دینا چاہیے۔ ان سوالات کے ساتھ شروع کریں جو اکثر چلتے ہیں یا سب سے زیادہ وقت لیتے ہیں۔

2. EXPLAIN NALYZE کے ساتھ پہلے اور بعد کی پیمائش کریں۔ اندازے کی بنیاد پر اشاریہ جات شامل نہ کریں۔ استفسار کو استعمال کرتے ہوئے چلائیں: EXPLAIN ANALYZEانڈیکس شامل کریں اور دوبارہ چلائیں۔ اگر عملدرآمد کے وقت میں کوئی معنی خیز بہتری نہیں آتی ہے تو، انڈیکس مدد نہیں کر رہا ہے۔

3. ہر کالم کو انڈیکس نہ کریں۔ ہر اشاریہ سست ہو جاتا ہے لکھتا ہے اور ذخیرہ کرتا ہے۔ احتیاط سے فیصلہ کریں کہ آپ کے اصل استفسار کے نمونوں کی بنیاد پر کن کالموں کو انڈیکس کرنا ہے۔

4. ملٹی کالم فلٹرز جامع اشاریہ جات کا استعمال کرتے ہیں۔ اگر آپ کا استفسار عام طور پر فلٹر کرتا ہے: city اور last_name ایک ساتھ، ایک جامع اشاریہ (city, last_name) دو الگ الگ سنگل کالم انڈیکس سے زیادہ موثر۔

5. ایک جامع انڈیکس سب سے زیادہ منتخب کالموں کو پہلے رکھتا ہے۔ وہ کالم جو نتائج کو سب سے زیادہ تنگ کرتا ہے پہلے آنا چاہیے۔

6. جب آپ اپنے ڈیٹا کے صرف ذیلی سیٹ سے استفسار کر رہے ہوں تو جزوی اشاریہ جات کا استعمال کریں۔ جب 90% سوالات درج ذیل قطاروں کو نشانہ بناتے ہیں: status="active"اس سب سیٹ پر ایک جزوی انڈیکس مکمل انڈیکس سے چھوٹا اور تیز ہوتا ہے۔

7. انڈیکس کے استعمال کی باقاعدگی سے نگرانی کریں۔ سوال pg_stat_user_indexes غیر استعمال شدہ اشاریہ جات تلاش کریں اور ہٹا دیں۔

8. وقتا فوقتا پھولے ہوئے اشاریہ جات کو دوبارہ بنائیں۔ بہت ساری اپ ڈیٹ/ڈیلیٹ سرگرمی والی میزوں پر، اشاریہ جات پھولے ہوئے ہو سکتے ہیں۔ استعمال کریں REINDEX CONCURRENTLY پیداوار کے نظام کو.

نتیجہ

اس ٹیوٹوریل میں، آپ نے سیکھا کہ ڈیٹا بیس انڈیکس کیا ہیں اور وہ استفسار کی کارکردگی کے لیے کیوں اہم ہیں۔ ہم نے دریافت کیا کہ کس طرح B-tree اشاریہ جات اندرونی طور پر کام کرتے ہیں اور کئی قسم کے اشاریہ جات (سنگل کالم، جامع، جزوی، اظہار، اور منفرد) بنائے اور استعمال کرتے ہیں۔ EXPLAIN ANALYZE اثر کی پیمائش کریں۔

آپ نے اشاریہ جات کے فوائد اور نقصانات کے بارے میں بھی سیکھا، بشمول رائٹ اوور ہیڈ، سٹوریج کے اخراجات، اور میموری پریشر کے ساتھ ساتھ وہ عام غلطیاں جو خود بخود آپ کو PostgreSQL میں انڈیکس استعمال کرنے سے روکتی ہیں۔

بنیادی اصول سادہ ہے۔ خیال یہ ہے کہ جان بوجھ کر اصل استفسار کے نمونوں کی بنیاد پر اشاریہ جات بنائیں، نتائج کی پیمائش کریں، اور غیر اہم اشیاء کو ہٹا دیں۔

اگر آپ کو یہ ٹیوٹوریل کارآمد لگا، تو آپ میری مزید تحریریں freeCodeCamp پر تلاش کر سکتے ہیں اور LinkedIn پر مجھ سے رابطہ کر سکتے ہیں۔