مؤشر التغطية ليس مجرد ميزة أخرى قد تكون مفيدة. هذا الشيء عملي بحت. بدونها ، قد لا يعطي Index Only Scan الفوز. على الرغم من أن مؤشر التغطية في المواقف المختلفة فعال بطرق مختلفة.
لا يتعلق الأمر بالفعل بتغطية الفهارس: بالمعنى الدقيق للكلمة ، ظهرت الفهارس الشاملة المزعومة في Postgres. لكن بالترتيب: فهرس التغطية هو فهرس يحتوي على جميع قيم الأعمدة المطلوبة بواسطة الاستعلام ؛ ومع ذلك ، لم يعد الوصول إلى الجدول نفسه مطلوبًا. تقريبا. يمكنك أن
تقرأ عن "تقريبًا" والفروق الدقيقة الأخرى في مقال بقلم
إيجور روغوف ، المتضمن في سلسلة فهرسه المكونة من 10 أجزاء (!). ويتم إنشاء
الفهرس الشامل على وجه التحديد للبحث في استعلامات نموذجية: تتم إضافة قيم الحقول التي لا يمكن البحث فيها إلى فهرس البحث ، فهي مطلوبة فقط حتى لا يتم الرجوع إلى الجدول مرة أخرى. يتم تشكيل هذه الفهارس باستخدام الكلمة الأساسية INCLUDE.
أنهت Anastasia Lubennikova (Postgres Professional) طريقة btree بحيث يمكن إدراج أعمدة إضافية في الفهرس. تم تضمين هذا التصحيح في برنامج PostgreSQL 11. لكن لم يكن لدى تصحيحات طرق الوصول إلى GiST / SP-GiST وقت لتنضج قبل إصدار هذا الإصدار. بحلول عيد الميلاد المجيد الثاني عشر.
نشأت رغبة بناءة في الحصول على فهارس شاملة لـ GiST منذ فترة طويلة: تم تقديم تصحيح اختبار من Andrey Borodin
إلى المجتمع مرة أخرى في منتصف أبريل 2018. لقد قام بكل الأعمال الأساسية الصعبة للغاية.
في أوائل أغسطس 2019 ، أضاف ألكساندر كوروتكوف تحسينات تجميلية وارتكب التصحيح.
للتظاهر وبعض الأبحاث ، سنقوم بإنشاء مجموعة من 3 ملايين مستطيل. في الوقت نفسه ، بضع كلمات حول نوع المربع ، لأنه ليس كل التلاعب بها بديهية.
كان نوع الصندوق - أي المستطيل - طويلًا في بوستجرس ، ويتم تعريفه بنقطتين (نقطة الكتابة الهندسية) - الرؤوس المعاكسة للمستطيل (أي ، المستطيل لا يمكن أن يكون مائلًا ، مبعثرًا على الجانب). نقرأ في
الوثائق : "تتم كتابة قيم مربع النوع في أحد النماذج التالية:
( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1 , y1 , x2 , y2
في الممارسة العملية ، عليك أن تكتب ، مثل ، مثل هذا:
SELECT box('1,2', '3,4'); box
أولاً ، يُظهر لنا Postgres القمة اليمنى العليا ، ثم أسفل اليسار. إذا نكتب مثل هذا ،
SELECT box('5,2', '3,4'); box
ثم سنتأكد من أن بوستجرس لم يعطِ القمم التي قدموها له. قام بحساب اليمين العلوي والسفلي الأيسر من اليسار العلوي والسفلي الأيمن. هذه خاصية ملائمة عندما يكون موقع القمم غير معروف مسبقًا - في حالة التوليد العشوائي ، على سبيل المثال. التدوين "1،2" ، "3،4" يعادل النقطة (1،2) ، النقطة (3،4). هذا النموذج هو في بعض الأحيان أكثر ملاءمة.
للعمل: ابحث في 3 ملايين مستطيل
CREATE TABLE boxes(id serial, thebox box, name text);
سنقوم بتوليد 3 ملايين مستطيل عشوائي. نريد توزيعًا طبيعيًا ، ولكن حتى لا نستخدم امتداد
tablefunc ، نستخدم الطريقة "السيئة": نحن نستخدم عشوائي () - عشوائي () ، والذي يعطي أيضًا صورة لطيفة (انظر الشكل). مع المستطيلات ، كلما كانت المستطيلات أكبر ، كلما كانت المسافة أقرب إلى المنتصف. مراكز الجاذبية الخاصة بهم هي أيضا عشوائية. هذه التوزيعات هي سمة لبعض أنواع بيانات المدينة الحقيقية. وأولئك الذين يريدون الخوض في قوانين الإحصاء أو تحديث الذكريات يمكنهم أن يقرؤوا عن اختلاف المتغيرات العشوائية ، على سبيل المثال ،
هنا .
INSERT INTO boxes(thebox, name) SELECT box( point( random()-random(), random()-random() ), point( random()-random(), random()-random() ) ), 'box no.' || x FROM generate_series(1,3000000) AS g(x);
حجم الجدول الذي يعرض
\dt+ هو 242 ميغابايت. الآن يمكنك البدء في البحث.
نحن نبحث بدون فهرس:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
نرى أن هناك مسح تسلسلي متوازي - المسح المتسلسل (وإن كان متوازياً).
قم بإنشاء فهرس منتظم وغير شامل:
CREATE INDEX ON boxes USING gist(thebox);
حجم فهرس
boxes_thebox_idx ، والذي يعرض
\di+ ، 262 ميجابايت. استجابة لنفس الطلب ، حصلنا على:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
تم تقليل وقت البحث بعامل ثلاثة ، وبدلاً من Parallel Seq Scan ، حصلوا على مسح فهرس الصور النقطية. لا تتوازى ، لكنها تعمل بشكل أسرع.
الآن اقتل الفهرس القديم وقم بإنشاء فهرس شامل:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name);
فهرس
boxes_thebox_name_idx البدانة: 356 ميغابايت. دعنا نذهب:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
يتم استخدام Index Only Scan ، لكن الصورة حزينة: الوقت أطول تقريبًا من الوقت بدونه. نقرأ كتيب مُنشئ المؤشرات ، في
الجزء الأول :
es لا تحتوي فهارس Rang PostgreSQL على معلومات تتيح لك الحكم على مدى رؤية الصفوف. لذلك ، تُرجع طريقة الوصول جميع إصدارات الصفوف التي تقع تحت شرط البحث ، بغض النظر عما إذا كانت مرئية للمعاملة الحالية أم لا. ومع ذلك ، إذا اضطرت آلية الفهرسة إلى البحث في الجدول في كل مرة لتحديد مدى الرؤية ، فلن تختلف طريقة المسح هذه عن المسح العادي للفهرسة. يتم حل المشكلة من خلال حقيقة أن PostgreSQL تدعم ما يسمى بخريطة الرؤية للجداول ، والتي تحدد فيها عملية الفراغ الصفحات التي لم تتغير فيها البيانات لفترة كافية حتى تراه جميع المعاملات ، بغض النظر عن وقت البدء ومستوى العزل. إذا كان معرف الصف الذي تم إرجاعه بواسطة الفهرس يشير إلى مثل هذه الصفحة ، فلا يمكن التحقق من إمكانية الرؤية. ››
نحن نفعل فراغ. نكرر:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
مسألة مختلفة تماما! ضعف الربح مقارنة بالمؤشر غير الشامل.
الانتقائية والكسب
يعتمد أداء الفهارس الشاملة على انتقائية الشروط في الاستعلامات. للتحقيق في هذا الاعتماد قليلاً ، سنحل المشكلة العكسي: سنقوم بإنشاء تسمية مع فهرس لنقطة الكتابة ، وسوف نبحث عن عدد النقاط التي ستقع في المربع المحدد. نشر النقاط مربعة بالتساوي.
CREATE TABLE test_covergist(id serial, tochka point, name text);
INSERT INTO test_covergist(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,3000000) AS g(x);
حجم الجدول هو 211 ميغابايت.
CREATE INDEX on test_covergist USING gist(tochka);
الحجم 213 ميغابايت.
من الواضح أننا سنأخذ جميع النقاط المتاحة في مربع:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
طلبنا شرح لإظهار المخازن المؤقتة. وسوف يأتي في متناول اليدين. الآن وقت تنفيذ الطلب أكثر من ثانيتين ، يمكن أن نرى أن المخازن المؤقتة: المشتركة قراءة = 54287. في موقف آخر ، يمكن أن نرى مزيجًا من القراءة المشتركة والمشاركة المشتركة - أي ، تتم قراءة بعض المخازن المؤقتة من القرص (أو من ذاكرة التخزين المؤقت لنظام التشغيل) ، وبعض من ذاكرة التخزين المؤقت المخزن المؤقت. نحن نعرف الحجم التقريبي للجدول والفهارس ، لذلك سنحمي أنفسنا من خلال تعيين مخازن مؤقتة مشتركة بحيث يناسب كل شيء - أعد تشغيل بوستجرس مع الخيار
-o "-c shared_buffers=1GB"
الآن:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
وهذا هو ، أصبحت القراءة المشتركة نجاحًا مشتركًا ، وتم تقليل الوقت ثلاث مرات.
تفصيل مهم آخر في EXPLAIN: يتم إرجاع 3 ملايين نقطة ، والتنبؤ بعدد السجلات التي تم إرجاعها هو 3 آلاف. Spoiler: لن يتغير هذا الرقم مع أي انتقائية. لا يعرف المُحسّن كيفية تقييم العلاقة الأساسية عند العمل مع أنواع المربعات أو النقاط. ولن تتغير الخطة: لأي حجم للمستطيل ، سيكون هناك فهرس مسح نقطي على test_covergist_tochka_idx.
فيما يلي قياسان آخران مع عدد السجلات الصادرة ، يختلفان حسب أوامر الحجم:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
تقوم بإرجاع سجلات أقل بـ 10 مرات (العدد الفعلي ... الصفوف = 269882) ، وقد انخفض الوقت بنحو 5 مرات.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','30000,30000') @> tochka; QUERY PLAN
يتم احتساب محتويات مربع 30K × 30K (2780) في 16 مللي ثانية فقط. وعندما يكون هناك عشرات السجلات ، يتم استخراجها بالفعل في أجزاء من ms ، وهذه القياسات غير موثوقة للغاية.
أخيرًا ، قم بقياس الشيء نفسه باستخدام الفهرس الشامل:
CREATE INDEX on test_covergist USING gist(tochka) INCLUDE(name);
الحجم 316 ميغابايت.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
الوقت هو نفسه تقريبا كما هو الحال مع الفهرس التقليدي ، على الرغم من المسح الضوئي فقط.
ولكن:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
وكان 151 مللي. وبناء على ذلك:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
هذا بالفعل جزء صغير من مللي لنفس السجلات نقطة 2780.
المخازن المؤقتة مثل البنادق
يمكن التماس التفسير وإيجاده في بندقية لم تطلق النار بعد لكن ذلك كان معلقًا على الحائط: عدد الكتل المقروءة. في حالة وجود فهرس شامل ، تتم قراءة كتل الفهرس نفسها فقط (Heap Fetches: 0). في ثلاث حالات ، كانت هذه الأرقام 40492 و 3735 و 52. ولكن عند استخدام الفهرس العادي ، تتكون الكتل المقروءة من مجموع البتات المقروءة في فهرس Bitmap Heap Scan (54248 مع 3 ملايين سجل) وتلك التي يجب قراءتها من الكومة (27223) ، حيث لا يمكن استخراج حقل الاسم من فهرس عادي. 54248 + 27223 = 81471. كان الحصري 40492. لحالتين أخريين: 29534 + 2510 = 31044 و 2655 + 31 = 2686. في حالة وجود فهرس منتظم ، يتم قراءة المزيد من الكتل على أي حال ، ولكن مع تحسن في الانتقائية ، يبدأ عدد الكتل المقروءة في الاختلاف بترتيب الحجم بدلاً من مرتين بسبب حقيقة أن عدد الكتل الضرورية من الكومة يتناقص ببطء أكثر من قراءة كتل الفهرس.
ولكن ربما النقطة ليست الانتقائية على الإطلاق ، ولكن ببساطة حجم الجدول؟ في حالة تكرار ذلك ، نكرر نفس الخطوات ، حيث نقوم بإنشاء جدول به 300 ألف ، وليس 3 ملايين سجل:
CREATE TABLE test_covergist_small(id serial, tochka point, name text); INSERT INTO test_covergist_small(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,300000) AS g(x); CREATE INDEX ON test_covergist_small USING gist(tochka); EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist_small WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
بعد ذلك ، كرر الشيء نفسه بالنسبة للفهرس الشامل. وهنا النتائج:
في حالة تغطية النقاط بنسبة 100٪ ، كان الاستعلام أبطأ قليلاً من المؤشر المعتاد. علاوة على ذلك ، كما في حالة 3 ملايين ، كل شيء سقط في مكانه. وهذا هو ، الانتقائية مهمة.
قامت شركتنا باختبار مؤشرات GiST الشاملة على بيانات حقيقية - مجموعة بها عدة ملايين من المستطيلات على خريطة موسكو. الاستنتاج هو نفسه: في العديد من الحالات ، تسارع هذه الفهارس بشكل ملحوظ في الاستعلامات. لكن لا يمكن توضيح المقالة بصور وأرقام الاختبارات: هذه البيانات ليست في المجال العام.
بدلا من الاستنتاج
دعنا نعود للحظة إلى المستطيلات العشوائية. دعونا نحاول أن نفعل نفس الشيء مع spgist. يمكنك تذكر أو معرفة معنى فهم الاختلافات بين SP-GiST و GiST من خلال قراءة
فهارس المقالات
في PostgreSQL - 6 . إنشاء فهرس شامل:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name); ERROR: access method "spgist" does not support included columns
للأسف ، بالنسبة إلى SP-GiST ، لم يتم تطبيق الفهارس الشاملة بعد.
لذلك هناك مجال للتحسين!