نگاهی عمقی به عبارت include در ایندکس‌های SQL

برخی پایگاه‌های داده مانند Microsoft SQL Server، IBM Db2 و همچنین PostgreSQL از نسخه ۱۱ به بعد، یک عبارت include را در دستور create index ارائه می‌دهند. اضافه شدن این ویژگی به PostgreSQL بهانه ارائه این توضیح طولانی و دیرهنگام درباره عبارت include است.

پیش از ورود به جزئیات، بیایید با یک مرور کوتاه درباره نحوه کار ایندکس‌های B-tree (غیر خوشه‌ای) و اسکن تنها ایندکس (index-only scan) آغاز کنیم.

مرور: ایندکس‌های B-tree

برای درک عبارت include، ابتدا باید بدانید که استفاده از یک ایندکس می‌تواند تا سه لایه از ساختارهای داده را تحت تأثیر قرار دهد:

B-tree
لیست پیوندی دوطرفه در سطح نود برگ B-tree
جدول

دو ساختار اول با هم یک ایندکس را تشکیل می‌دهند، بنابراین می‌توان آن‌ها را به یک واحد ترکیب کرد، یعنی “ایندکس B-tree”. با این حال، ترجیح می‌دهم آن‌ها را جداگانه بررسی کنم، زیرا نیازهای متفاوتی را برآورده می‌کنند و تأثیرات متفاوتی بر عملکرد دارند. علاوه بر این، توضیح عبارت include نیازمند این تمایز است.

به طور کلی، نرم‌افزار پایگاه داده ابتدا با پیمایش B-tree اولین مقدار دارای تطابق را در سطح نود برگ پیدا می‌کند (۱). سپس لیست پیوندی دوطرفه را دنبال می‌کند تا تمام وارده‌های‌ مطابق را پیدا کند (۲) و در نهایت هر یک از این ورودی‌های مطابق را از جدول بازیابی می‌کند (۳). در واقع، دو مرحله آخر می‌توانند با هم انجام شوند، اما این برای درک مفهوم کلی اهمیت ندارد.

فرمول‌های زیر به شما ایده‌ای تقریبی از تعداد عملیات‌های خواندن موردنیاز هر یک از این مراحل می‌دهند. مجموع این سه مؤلفه، میزان کار برای دسترسی به ایندکس را نشان می‌دهد:

B-tree: log₁₀₀(<تعداد رکوردها در جدول>)، که معمولاً کمتر از ۵ است
لیست پیوندی دوطرفه: <تعداد رکوردهای خوانده شده از ایندکس> / 100
جدول: <تعداد رکوردهای خوانده شده از جدول>

هنگام بارگذاری تعداد کمی رکورد، B-tree بیشترین سهم را در مقدار کار کلی دارد. به محض اینکه نیاز باشد تنها چند رکورد از جدول بازیابی شوند، این مرحله پیشی می‌گیرد. در هر دو حالت—چه تعداد کم و یا زیاد رکورد—لیست پیوندی دوطرفه معمولاً عامل جزئی است، زیرا رکوردهای با مقادیر مشابه را در کنار یکدیگر ذخیره می‌کند، به‌طوری‌که یک عملیات خواندن می‌تواند ۱۰۰ یا حتی بیشتر رکورد را بازیابی کند. این فرمول این موضوع را با مقسوم‌علیه مربوطه نشان می‌دهد.

ایده کلی و اصلی بهینه‌سازی این است که با انجام کار کمتر، به همان هدف برسیم. وقتی صحبت از دسترسی به ایندکس می‌شود، این بدان معناست که نرم‌افزار پایگاه داده اگر نیازی به بعضی داده‌ها نداشته باشد، از دسترسی به آن ساختار داده صرف نظر می‌کند.

در مورد شیوه کار ایندکس B-tree می‌توانید فصل ۱ «Anatomy of an SQL Index» در SQL Performance Explained را بخوانید

مرور: اسکن تنها ایندکس

اسکن تنها ایندکس (Index-only scan) دقیقاً همین کار را انجام می‌دهد: اگر داده‌های موردنیاز در لیست پیوندی دوطرفه ایندکس موجود باشند، دسترسی به جدول را حذف می‌کند.

به ایندکس و پرس‌وجوی زیر توجه کنید که از مقاله “Index-Only Scan: Avoiding Table Access” گرفته شده است.

				
					
CREATE INDEX idx
    ON sales
     ( subsidiary_id, eur_value )
     
SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?

در نگاه اول ممکن است تعجب کنید که چرا ستون eur_value در تعریف ایندکس وجود دارد—این ستون در عبارت where ذکر نشده است.

ایندکس‌های B-tree به چندین عبارت کمک می‌کنند

یک تصور اشتباه رایج این است که ایندکس‌ها فقط به عبارت where کمک می‌کنند.

ایندکس‌های B-tree می‌توانند به عبارت‌های order by، group by، select و دیگر عبارت‌ها نیز کمک کنند. تنها بخش B-tree از یک ایندکس است (و نه لیست پیوندی دوطرفه) که نمی‌تواند توسط سایر عبارت‌ها مورد استفاده قرار گیرد.

نکته کلیدی در این مثال این است که ایندکس B-tree تمام ستون‌های موردنیاز را دارد—نرم‌افزار پایگاه داده نیازی به دسترسی به جدول ندارد. این همان چیزی است که ما به آن اسکن تنها ایندکس می‌گوییم.

با اعمال فرمول‌های بالا، اگر تعداد کمی رکورد شرایط where را برآورده کنند، مزیت عملکردی این روش بسیار کم است. از سوی دیگر، اگر عبارت where تعداد زیادی رکورد را انتخاب کند، مثلاً میلیون‌ها، تعداد عملیات‌های خواندن اساساً با یک ضریب ۱۰۰ کاهش می‌یابد.

غیرمعمول نیست که اسکن تنها ایندکس عملکرد را به میزان یک یا دو مرتبه نمایی، بهبود بخشد.

مثال بالا از این واقعیت استفاده می‌کند که لیست پیوندی دوطرفه—نودهای برگ B-tree—شامل ستون eur_value است. اگرچه سایر نودهای B-tree نیز این ستون را ذخیره می‌کنند، این پرس‌وجو هیچ نیازی به اطلاعات موجود در این نودها ندارد.

عبارت Include

عبارت include به ما این امکان را می‌دهد که بین ستون‌هایی که می‌خواهیم در کل ایندکس داشته باشیم (ستون‌های کلید) و ستون‌هایی که فقط در نودهای برگ نیاز داریم (ستون‌های include) تمایز قائل شویم. به این معنا که این امکان را فراهم می‌کند تا ستون‌هایی که در نودهای غیر برگ نیازی به آن‌ها نداریم را حذف کنیم.

“ایندکس پوششی”

اصطلاح “ایندکس پوششی” گاهی در زمینه اسکن تنها ایندکس یا عبارت include به کار می‌رود. از آنجا که این اصطلاح اغلب با معانی متفاوتی استفاده می‌شود، معمولاً از به‌کارگیری آن اجتناب می‌کنم.

آنچه اهمیت دارد این است که آیا یک ایندکس مشخص می‌تواند از یک پرس‌وجوی مشخص با استفاده از اسکن تنها ایندکس پشتیبانی کند یا خیر. اینکه ایندکس شامل عبارت include باشد یا تمام ستون‌های جدول را در برگیرد، مرتبط نیست.

با استفاده از عبارت include، می‌توانیم ایندکس را برای این پرس‌وجو بهبود دهیم:

				
					CREATE INDEX idx
    ON sales ( subsidiary_id )
     INCLUDE ( eur_value )

این پرس‌وجو همچنان می‌تواند از این ایندکس برای یک اسکن تنها ایندکس استفاده کند و به همین دلیل عملکرد مشابهی ارائه می‌دهد.

علاوه بر تفاوت‌های آشکار در تصویر، یک تفاوت ظریف‌تر نیز وجود دارد: ترتیب ورودی‌های نود برگ شامل ستون‌های include نمی‌شود. ایندکس فقط بر اساس ستون‌های کلید مرتب شده است. این امر دو پیامد دارد: ستون‌های include نمی‌توانند برای جلوگیری از مرتب‌سازی استفاده شوند و همچنین در بررسی یگانگی (uniqueness) در نظر گرفته نمی‌شوند.

محدودیت‌های طول

حداکثر طول یک وارده ایندکس محدود است. به این معنا که نمی‌توانید همیشه تمام ستون‌هایی که می‌خواهید را در یک ایندکس قرار دهید.

برخی محصولات (به ویژه SQL Server)، بر بخش کلید ایندکس، محدودیت‌های سخت‌گیرانه‌تری نسبت به کل طول ایندکس با ستون‌های include اعمال می‌کنند. در چنین سیستم‌هایی، اگر نتوانید تمام ستون‌های موردنیاز را در کلید ایندکس قرار دهید، می‌توانید به عبارت include روی بیاورید. اگرچه این ستون‌ها نمی‌توانند به‌عنوان پیش‌نیازهای دسترسی استفاده شوند، اما همچنان امکان بررسی شرایط روی این ستون‌ها بدون دسترسی به جدول را فراهم می‌کنند.

در مقایسه با تعریف ایندکس اصلی، تعریف جدید با عبارت include چند مزیت دارد:

درخت ممکن است سطوح کمتری داشته باشد (~40% کمتر)
از آنجا که نودهای بالای لیست پیوندی دوطرفه شامل ستون‌های include نیستند، پایگاه داده می‌تواند شاخه‌های بیشتری را در هر بلوک ذخیره کند و در نتیجه ممکن است درخت سطوح کمتری داشته باشد.
ایندکس کمی کوچک‌تر است (~3% کمتر)
از آنجا که نودهای غیر برگ درخت شامل ستون‌های include نیستند، اندازه کلی ایندکس کمی کمتر است. با این حال، سطح نود برگ ایندکس بیشترین فضا را اشغال می‌کند، بنابراین صرفه‌جویی بالقوه در نودهای باقی‌مانده بسیار کم است.
مقصود خود را مستند می‌کند
این قطعاً یکی از کم‌ارزش‌ترین مزایای عبارت include است: دلیل وجود ستون در ایندکس در تعریف خود ایندکس مستند می‌شود.

اجازه دهید مورد آخر را بیشتر بررسی کنیم.

هنگام گسترش یک ایندکس موجود، بسیار مهم است که دقیقاً بدانید چرا ایندکس به شکل فعلی تعریف شده است. آزادی‌هایی که در تغییر ایندکس بدون آسیب رساندن به سایر پرس‌وجوها دارید، نتیجه مستقیم این دانش است.

پرس‌وجوی زیر این موضوع را نشان می‌دهد:

				
					
SELECT *
  FROM sales
  WHERE subsidiary_id = ?
  ORDER BY ts DESC
  FETCH FIRST 1 ROW ONLY

همان‌طور که قبلاً توضیح داده شد، این کوئری برای یک شرکت تابعه مشخص، جدیدترین رکورد sales (ts برای زمان ثبت) را استخراج می‌کند.

برای بهینه‌سازی این کوئری، داشتن یک ایندکس که با ستون‌های کلید (subsidiary_id, ts) شروع شود، ایده‌آل خواهد بود. با این ایندکس، نرم‌افزار پایگاه داده می‌تواند مستقیماً به جدیدترین رکورد آن شرکت تابعه دسترسی پیدا کرده و آن را فوراً بازگرداند. نیازی به خواندن و مرتب‌سازی تمام رکوردهای آن شرکت تابعه نیست، زیرا لیست دوطرفه بر اساس کلید ایندکس مرتب شده است، یعنی آخرین رکورد برای هر شرکت تابعه باید دارای بالاترین مقدار ts برای آن شرکت تابعه باشد. با این روش، کوئری تقریباً به سرعت یک جستجوی primary key انجام می‌شود. برای جزئیات بیشتر درباره این تکنیک، به “Indexing Order By” و “Querying Top-N Rows” مراجعه کنید.

قبل از اضافه کردن یک ایندکس جدید برای این کوئری، باید بررسی کنیم که آیا ایندکسی موجود است که بتوان آن را تغییر داد (گسترش داد) تا از این ترفند پشتیبانی کند یا خیر. این معمولاً یک روش خوب است زیرا گسترش یک ایندکس نسبت به اضافه کردن یک ایندکس جدید، تأثیر کمتری بر هزینه نگهداری دارد . با این حال، هنگام تغییر یک ایندکس موجود، باید مطمئن شویم که آن ایندکس برای سایر کوئری‌ها غیر مفید نشود.

اگر به تعریف اصلی ایندکس نگاه کنیم، با یک مشکل مواجه می‌شویم:

				
					CREATE INDEX idx
    ON sales
     ( subsidiary_id, eur_value )

برای اینکه این ایندکس از دستور order by در کوئری بالا پشتیبانی کند، باید ستون ts را بین دو ستون موجود اضافه کنیم:

				
					
CREATE INDEX idx
    ON sales
    ( subsidiary_id, ts, eur_value )

با این حال، این تغییر ممکن است فایده این ایندکس را برای کوئری‌هایی که ستون eur_value را در موقعیت دوم نیاز دارند (مثلاً اگر این ستون در دستور where یا order by باشد)، کمتر کند. تغییر این ایندکس ریسک قابل توجهی دارد: خراب کردن سایر کوئری‌ها (مگر اینکه بدانیم چنین کوئری‌هایی وجود ندارند). اگر اطلاعی نداریم، معمولاً بهتر است ایندکس را به همان صورت نگه داریم و یک ایندکس جدید برای کوئری جدید ایجاد کنیم.

اما اگر به ایندکس با عبارت include نگاه کنیم، تصویر کاملاً تغییر می‌کند.

				
					
CREATE INDEX idx
    ON sales ( subsidiary_id )
      INCLUDE ( eur_value )

از آنجا که ستون eur_value در عبارت include قرار دارد، این ستون در گره‌های غیر برگ قرار ندارد و در نتیجه نه برای پیمایش درخت و نه برای مرتب‌سازی مفید است. اضافه کردن یک ستون جدید به انتهای بخش کلید نسبتاً ایمن است.

				
					
CREATE INDEX idx
    ON sales ( subsidiary_id, ts )
     INCLUDE ( eur_value )

اگرچه همچنان ریسک کمی برای تأثیرات منفی روی سایر کوئری‌ها وجود دارد، معمولاً ارزش پذیرش این ریسک را دارد.

از دیدگاه تکامل ایندکس، اگر تغییر دیگری نیاز نباشد، اضافه کردن ستون به عبارت include بسیار مفید است. ستون‌هایی که فقط برای امکان اسکن تنها ایندکس اضافه می‌شوند، بهترین گزینه‌ها برای این کار هستند.

فیلتر کردن بر روی ستون‌های Include

تا اینجا بر روی این موضوع تمرکز کردیم که چگونه عبارت include می‌تواند اسکن‌های تنها ایندکس را ممکن کند. بیایید مورد دیگری را بررسی کنیم که در آن داشتن یک ستون اضافی در ایندکس مفید است.

				
					SELECT *
  FROM sales
 WHERE subsidiary_id = ?
   AND notes LIKE '%search term%'

من عبارت جستجو را به یک مقدار ثابت تبدیل کردم تا نمایش دهنده کاراکترهای wildcard در ابتدا و انتهای عبارت باشد—البته شما در کد خود از یک bind parameter استفاده خواهید کرد.

حالا بیایید در مورد ایندکس مناسب برای این کوئری فکر کنیم. به طور واضح، ستون subsidiary_id باید در موقعیت اول باشد. اگر ایندکس قبلی را در نظر بگیریم، این شرط را برآورده می‌کند:

				
					
CREATE INDEX idx
    ON sales ( subsidiary_id, ts )
     INCLUDE ( eur_value )

نرم‌افزار پایگاه داده می‌تواند از این ایندکس با استفاده از روش سه‌مرحله‌ای توضیح داده‌شده در ابتدا بهره ببرد: (1) از B-tree برای یافتن اولین وارده ایندکس برای شرکت تابعه مشخص استفاده می‌کند؛ (2) از لیست دوطرفه برای یافتن تمام رکوردهای فروش آن شرکت تابعه پیروی می‌کند؛ (3) تمام رکوردهای فروش مرتبط را از جدول واکشی کرده، آن‌هایی را که الگوی like در ستون notes با آن‌ها مطابقت ندارد حذف کرده و ردیف‌های باقی‌مانده را برمی‌گرداند.

مشکل در مرحله آخر این روش نهفته است: دسترسی به جدول ردیف‌ها را بدون دانستن اینکه آیا به نتیجه نهایی اضافه می‌شوند یا خیر بارگذاری می‌کند. اغلب، دسترسی به جدول بزرگترین عامل در میزان کار برای اجرای یک کوئری است. بارگذاری داده‌هایی که حتی انتخاب نمی‌شوند، به شدت عملکرد را کاهش می‌دهد.

مهم
از بارگذاری داده‌هایی که بر نتیجه کوئری تأثیری ندارند اجتناب کنید.

چالش این کوئری خاص این است که از یک الگوی like با in-fix استفاده می‌کند. ایندکس‌های عادی B-tree جستجو برای چنین الگوهایی را پشتیبانی نمی‌کنند. با این حال، ایندکس‌های B-tree همچنان از فیلتر کردن بر روی چنین الگوهایی پشتیبانی می‌کنند. تاکید می‌کنم: جستجو در مقابل فیلتر کردن.

به عبارت دیگر، اگر ستون notes در لیست دوطرفه وجود داشت، نرم‌افزار پایگاه داده می‌توانست الگوی like را قبل از واکشی آن ردیف از جدول اعمال کند (در PostgreSQL این موضوع صدق نمی‌کند، به توضیح ادامه مقاله توجه کنید). این امر دسترسی به جدول را در صورتی که الگوی like تطابق نداشته باشد، جلوگیری می‌کند. اگر جدول دارای ستون‌های بیشتری باشد، همچنان دسترسی به جدول برای واکشی آن ستون‌ها برای ردیف‌هایی که شرط where را برآورده می‌کنند انجام می‌شود—به دلیل select *.

				
					
CREATE INDEX idx
    ON sales ( subsidiary_id, ts )
     INCLUDE ( eur_value, notes )

اگر ستون‌های بیشتری در جدول وجود داشته باشند، ایندکس امکان اسکن تنها ایندکس را فراهم نمی‌کند. با این حال، می‌تواند عملکرد را به اسکن تنها ایندکس نزدیک کند، اگر درصد ردیف‌هایی که با الگوی like مطابقت دارند بسیار کم باشد. در حالت مخالف—اگر تمام ردیف‌ها با الگو مطابقت داشته باشند—عملکرد کمی به دلیل اندازه افزایش‌یافته ایندکس کاهش می‌یابد. با این حال، رسیدن به نقطه تعادل آسان است: برای بهبود کلی عملکرد، اغلب کافی است که فیلتر like درصد کمی از ردیف‌ها را حذف کند. تأثیر شما بسته به اندازه ستون‌های درگیر متفاوت خواهد بود.

ایندکس‌های یکتا با عبارت Include

در نهایت، جنبه کاملاً متفاوتی از عبارت include وجود دارد: ایندکس‌های یکتا با عبارت include فقط ستون‌های کلید را برای یکتایی در نظر می‌گیرند.

این قضیه، امکان این را فراهم می‌کند که ایندکس‌های یکتایی ایجاد کنیم که ستون‌های اضافی در گره‌های برگ دارند، مثلاً برای یک اسکن تنها ایندکس.

				
					
CREATE UNIQUE INDEX …
    ON … ( id )
 INCLUDE ( payload )

این ایندکس از ورود مقادیر تکراری در ستون id جلوگیری می‌کند، اما در عین حال از اسکن تنها ایندکس برای کوئری بعدی پشتیبانی می‌کند.

				
					
SELECT payload
  FROM …
 WHERE id = ?

توجه داشته باشید که عبارت include برای این رفتار به‌طور کامل ضروری نیست: پایگاه‌های داده‌ای که بین قیدهای یکتا (unique constraints) و ایندکس‌های یکتا (unique indexes) تفاوت قائل می‌شوند، فقط به یک ایندکس با ستون‌های کلید یکتا به‌عنوان چپ‌ترین ستون نیاز دارند—اضافه کردن ستون‌های دیگر مشکلی ندارد.

برای پایگاه داده Oracle، سینتکس مربوطه به این شکل است:

				
					
CREATE INDEX …
    ON … ( id, payload )
    
ALTER TABLE … ADD UNIQUE ( id )
      USING INDEX …

سازگاری با پایگاه‌داده‌های مختلف

PostgreSQL: عدم فیلترگذاری قبل از بررسی مشاهده‌پذیری

پایگاه داده PostgreSQL محدودیتی در اعمال فیلترها در سطح ایندکس دارد. خلاصه این است که این کار را انجام نمی‌دهد، مگر در چند مورد خاص. بدتر اینکه، برخی از این موارد تنها زمانی کار می‌کنند که داده مربوطه در بخش کلید ایندکس ذخیره شده باشد، نه در عبارت include. این بدان معناست که انتقال ستون‌ها به عبارت include ممکن است عملکرد را کاهش دهد، حتی اگر منطق توصیف‌شده فوق همچنان اعمال شود.

داستان کامل با این واقعیت آغاز می‌شود که PostgreSQL نسخه‌های قدیمی ردیف‌ها را در جدول نگه می‌دارد تا زمانی که برای تمام تراکنش‌ها نامرئی شوند و فرآیند vacuum آن‌ها را در آینده حذف کند. برای دانستن اینکه آیا یک نسخه ردیف، برای یک تراکنش مشخص قابل مشاهده است یا خیر، هر جدول دو ویژگی اضافی دارد که نشان می‌دهند یک نسخه ردیف چه زمانی ایجاد و چه زمانی حذف شده است: xmin و xmax. ردیف تنها در صورتی قابل مشاهده است که تراکنش جاری در محدوده xmin/xmax قرار گیرد.

متأسفانه، مقادیر xmin/xmax در ایندکس‌ها ذخیره نمی‌شوند.

این بدان معناست که هر زمان PostgreSQL به یک وارده ایندکس نگاه می‌کند، نمی‌تواند تعیین کند که آیا آن وارده برای تراکنش جاری قابل مشاهده است یا خیر. ممکن است وارده حذف شده باشد یا وارده‌ای باشد که هنوز تأیید نشده است. روش استاندارد برای فهمیدن این موضوع، نگاه کردن به جدول و بررسی مقادیر xmin/xmax است.

نتیجه این است که چیزی به نام اسکن تنها ایندکس (index-only scan) در PostgreSQL وجود ندارد. فرقی نمی‌کند چند ستون در یک ایندکس قرار دهید، PostgreSQL همیشه نیاز به بررسی مشاهده‌پذیری دارد که در ایندکس موجود نیست.

با این حال عملیات Index Only Scan در PostgreSQL وجود دارد—هرچند که همچنان نیاز دارد مشاهده‌پذیری هر نسخه ردیف را با دسترسی به داده‌های خارج از ایندکس بررسی کند. به جای رفتن به جدول، Index Only Scan ابتدا نقشه مشاهده‌پذیری (visibility map) را بررسی می‌کند. این نقشه بسیار فشرده است، بنابراین تعداد عملیات خواندن (امیدواریم) کمتر از دریافت xmin/xmax از جدول باشد. با این حال، نقشه مشاهده‌پذیری همیشه پاسخ قطعی نمی‌دهد: نقشه مشاهده‌پذیری یا اعلام می‌کند که ردیف قابل مشاهده است، یا اینکه مشاهده‌پذیری آن مشخص نیست. در حالت دوم، Index Only Scan همچنان باید xmin/xmax را از جدول بازیابی کند (در explain analyze با عنوان “Heap Fetches” نمایش داده می‌شود).

پس از این توضیح کوتاه در مورد مشاهده‌پذیری، می‌توانیم به فیلترگذاری در سطح ایندکس بازگردیم.

SQL اجازه استفاده از عبارات پیچیده در عبارت where را می‌دهد. این عبارات ممکن است خطاهای زمان اجرا مانند “تقسیم بر صفر” ایجاد کنند. اگر PostgreSQL چنین عبارتی را قبل از تأیید مشاهده‌پذیری ورودی مربوطه ارزیابی کند، حتی ردیف‌های نامرئی نیز می‌توانند باعث چنین خطاهایی شوند. برای جلوگیری از این مسئله، PostgreSQL معمولاً مشاهده‌پذیری را قبل از ارزیابی چنین عبارات بررسی می‌کند.

یک استثنا برای این قانون کلی وجود دارد. از آنجا که مشاهده‌پذیری را نمی‌توان هنگام جستجوی ایندکس بررسی کرد، عملگرهایی که می‌توانند برای جستجو استفاده شوند، باید همیشه امن باشند. این عملگرها همان‌هایی هستند که در کلاس عملگر مربوطه تعریف شده‌اند. اگر یک فیلتر ساده از یک عملگر موجود در کلاس عملگر استفاده کند، PostgreSQL می‌تواند آن فیلتر را قبل از بررسی مشاهده‌پذیری اعمال کند زیرا می‌داند این عملگرها ایمن هستند. نکته اینجاست که تنها ستون‌های کلید یک کلاس عملگر مرتبط دارند. ستون‌های موجود در عبارت include چنین کلاسی ندارند—فیلترهایی که بر اساس آن‌ها هستند، قبل از تأیید مشاهده‌پذیری اعمال نمی‌شوند. این برداشت من از یک بحث در mailing list توسعه‌دهندگان PostgreSQL است.

برای نمایش این موضوع، ایندکس و کوئری قبلی را در نظر بگیرید:

				
					
CREATE INDEX idx
    ON sales ( subsidiary_id, ts )
     INCLUDE ( eur_value, notes )
     
SELECT *
  FROM sales
 WHERE subsidiary_id = ?
   AND notes LIKE '%search term%'

طرح اجرای کوئری—برای اختصار ویرایش شده—می‌تواند به این شکل باشد:

				
					
                QUERY PLAN
----------------------------------------------
Index Scan using idx on sales (actual rows=16)
  Index Cond: (subsidiary_id = 1)
  Filter: (notes ~~ '%search term%')
  Rows Removed by Filter: 240
  Buffers: shared hit=54

فیلتر like در بخش Filter نمایش داده شده است، نه در Index Cond. این به این معناست که فیلتر در سطح جدول اعمال شده است. همچنین، تعداد shared hits برای بازیابی 16 ردیف نسبتاً زیاد است.

در یک Bitmap Index/Heap Scan این پدیده واضح‌تر می‌شود.

				
					
                  QUERY PLAN
-----------------------------------------------
Bitmap Heap Scan on sales (actual rows=16)
  Recheck Cond: (idsubsidiary_id= 1)
  Filter: (notes ~~ '%search term%')
  Rows Removed by Filter: 240
  Heap Blocks: exact=52
  Buffers: shared hit=54
  -> Bitmap Index Scan on idx (actual rows=256)
       Index Cond: (subsidiary_id = 1)
       Buffers: shared hit=2

در اسکن Bitmap Index، فیلتر like اصلاً ذکر نشده است. در عوض، این اسکن 256 ردیف برمی‌گرداند—بسیار بیشتر از 16 ردیفی که شرط where را برآورده می‌کنند.

توجه داشته باشید که این موضوع مختص ستون‌های include در این مورد نیست. انتقال ستون‌های include به کلید ایندکس نیز همین نتیجه را خواهد داشت.

				
					CREATE INDEX idx
    ON sales ( subsidiary_id, ts, eur_value, notes)

				
					                  QUERY PLAN
-----------------------------------------------
Bitmap Heap Scan on sales (actual rows=16)
  Recheck Cond: (subsidiary_id = 1)
  Filter: (notes ~~ '%search term%')
  Rows Removed by Filter: 240
  Heap Blocks: exact=52
  Buffers: shared hit=54
  -> Bitmap Index Scan on idx (actual rows=256)
       Index Cond: (subsidiary_id = 1)
       Buffers: shared hit=2

این به این دلیل است که عملگر like بخشی از کلاس عملگر نیست، بنابراین ایمن در نظر گرفته نمی‌شود.

اگر از یک عملگر از کلاس عملگر، مانند equals، استفاده کنید، طرح اجرای کوئری تغییر می‌کند.

				
					
SELECT *
  FROM sales
 WHERE subsidiary_id = ?
   AND notes = 'search term'

اکنون در اسکن Bitmap Index، تمامی شرایط موجود در عبارت where اعمال می‌شوند و تنها 16 ردیف باقی‌مانده به Bitmap Heap Scan ارسال می‌شوند.

				
					                 QUERY PLAN
----------------------------------------------
Bitmap Heap Scan on sales (actual rows=16)
  Recheck Cond: (subsidiary_id = 1
             AND notes = 'search term')
  Heap Blocks: exact=16
  Buffers: shared hit=18
  -> Bitmap Index Scan on idx (actual rows=16)
       Index Cond: (subsidiary_id = 1
                AND notes = 'search term')
       Buffers: shared hit=2

توجه داشته باشید که این موضوع نیازمند آن است که ستون مربوطه یک ستون کلید باشد. اگر ستون notes را دوباره به عبارت include منتقل کنید، دیگر کلاس عملگر مرتبطی نخواهد داشت، بنابراین عملگر equals دیگر ایمن در نظر گرفته نمی‌شود. در نتیجه، PostgreSQL اعمال این فیلتر را به دسترسی جدول موکول می‌کند، آن هم پس از اینکه مشاهده‌پذیری بررسی شده باشد.

				
					                 QUERY PLAN
-----------------------------------------------
Bitmap Heap Scan on sales (actual rows=16)
  Recheck Cond: (id = 1)
  Filter: (notes = 'search term')
  Rows Removed by Filter: 240
  Heap Blocks: exact=52
  Buffers: shared hit=54
  -> Bitmap Index Scan on idx (actual rows=256)
       Index Cond: (id = 1)
       Buffers: shared hit=2