فصل 6. نصائح لتحسين الأداء
6.1. جداول صغيرة ذات أشكال هندسية كبيرة
6.1.1. وصف المشكلة
تعاني إصدارات PostgreSQL الحالية (بما فيها 9.6) من ضعف في مُحسِّن الاستعلامات يتعلق بجداول TOAST. جداول TOAST نوع من «غرفة الامتداد» تُستخدم لتخزين القيم الكبيرة (من حيث حجم البيانات) التي لا تتسع لها صفحات البيانات العادية (مثل النصوص الطويلة أو الصور أو الأشكال الهندسية المعقدة ذات الرؤوس الكثيرة)، راجع توثيق PostgreSQL الخاص بـ TOAST لمزيد من المعلومات).
تظهر المشكلة إذا كان لديك جدول يحتوي على أشكال هندسية كبيرة نوعًا ما، لكن عدد صفوفه ليس كبيرًا (مثل جدول يحتوي على حدود جميع الدول الأوروبية بدقة عالية). عندئذٍ يكون الجدول نفسه صغيرًا، لكنه يستهلك مساحة TOAST كبيرة. في حالتنا المثال، كان الجدول نفسه يضم نحو 80 صفًا ويستخدم 3 صفحات بيانات فقط، بينما استخدم جدول TOAST عدد 8225 صفحة.
نفّذ الآن استعلامًا تستخدم فيه معامل الأشكال الهندسية && للبحث عن مستطيل محيط لا يطابق إلا عددًا قليلًا جدًا من تلك الصفوف. يرى مُحسِّن الاستعلامات حينها أن الجدول لا يضم إلا 3 صفحات و80 صفًا، فيُقدّر أن المسح التسلسلي لجدول بهذا الصغر أسرع بكثير من استخدام فهرس، فيقرر تجاهل فهرس GIST. عادةً يكون هذا التقدير صحيحًا، لكن في حالتنا يضطر المعامل && إلى جلب كل شكل هندسي من القرص لمقارنة المستطيلات المحيطة، فيقرأ بذلك جميع صفحات TOAST أيضًا.
لمعرفة ما إذا كنت تعاني من هذه المشكلة، استخدم أمر postgresql "EXPLAIN ANALYZE". ولمزيد من المعلومات والتفاصيل التقنية، يمكنك قراءة النقاش في القائمة البريدية للأداء الخاصة بـ PostgreSQL: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php
ونقاش أحدث حول PostGIS: https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html
6.1.2. حلول بديلة
يحاول مطوّرو PostgreSQL حل هذه المشكلة بجعل تقدير الاستعلامات مراعيًا لجداول TOAST. وحتى ذلك الحين، إليك حلّين بديلين:
الحل الأول هو إجبار مخطِّط الاستعلامات على استخدام الفهرس. أرسل "SET enable_seqscan TO off;" إلى الخادم قبل تنفيذ الاستعلام. يُجبر هذا مخطِّط الاستعلامات أساسًا على تجنّب المسح التسلسلي متى أمكن، فيستخدم فهرس GIST كالمعتاد. لكن هذا الإعداد يجب ضبطه في كل اتصال، ويتسبب في تقديرات خاطئة من مخطِّط الاستعلامات في حالات أخرى، لذا ينبغي تنفيذ "SET enable_seqscan TO on;" بعد الاستعلام.
الحل الثاني هو جعل المسح التسلسلي بالسرعة التي يظنها مخطِّط الاستعلامات. ويمكن تحقيق ذلك بإنشاء عمود إضافي «يخزّن مؤقتًا» المستطيل المحيط (bbox)، والمطابقة عليه. في مثالنا، تكون الأوامر على النحو التالي:
SELECT AddGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2');
UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(geom));
غيّر الآن استعلامك ليستخدم المعامل && على bbox بدلًا من geom_column، هكذا:
SELECT geom_column
FROM mytable
WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);
بالطبع، إذا عدّلت صفوف mytable أو أضفت إليها، فعليك إبقاء bbox «متزامنًا». الطريقة الأكثر شفافية لذلك هي القوادح (triggers)، لكن يمكنك أيضًا تعديل تطبيقك لإبقاء عمود bbox محدّثًا، أو تشغيل استعلام UPDATE أعلاه بعد كل تعديل.
6.2. التجميع (CLUSTER) وفق فهارس الأشكال الهندسية
بالنسبة إلى الجداول التي تكون في الغالب للقراءة فقط، ويُستخدم فيها فهرس واحد لأغلب الاستعلامات، يوفر PostgreSQL الأمر CLUSTER. يعيد هذا الأمر ترتيب جميع صفوف البيانات فعليًا بنفس ترتيب معايير الفهرس، مما يحقق ميزتين في الأداء: أولًا، في عمليات مسح نطاقات الفهرس، ينخفض عدد عمليات البحث في جدول البيانات انخفاضًا كبيرًا. ثانيًا، إذا تركّزت مجموعة العمل لديك في فترات صغيرة من الفهارس، فستحصل على تخزين مؤقت أكثر كفاءة لأن صفوف البيانات تتوزع على عدد أقل من صفحات البيانات. (ندعوك لقراءة توثيق الأمر CLUSTER في دليل PostgreSQL عند هذه النقطة.)
إلا أن PostgreSQL لا يسمح حاليًا بالتجميع وفق فهارس GIST الخاصة بـ PostGIS لأن فهارس GIST تتجاهل ببساطة قيم NULL، فتحصل على رسالة خطأ مثل:
lwgeom=# CLUSTER my_geom_index ON my_table; ERROR: cannot cluster when index access method does not handle null values HINT: You may be able to work around this by marking column "geom" NOT NULL.
كما تخبرك رسالة HINT، يمكن الالتفاف على هذا القصور بإضافة قيد "not null" إلى الجدول:
lwgeom=# ALTER TABLE my_table ALTER COLUMN geom SET not null; ALTER TABLE
بالطبع، لن يفلح هذا إذا كنت تحتاج فعلًا إلى قيم NULL في عمود الأشكال الهندسية. إضافة إلى ذلك، يجب استخدام الطريقة أعلاه لإضافة القيد، إذ إن استخدام قيد CHECK مثل "ALTER TABLE blubb ADD CHECK (geometry is not null);" لن ينجح.
6.3. تجنّب تحويل الأبعاد
قد يحدث أحيانًا أن تكون لديك بيانات ثلاثية أو رباعية الأبعاد في جدولك، لكنك تصل إليها دائمًا باستخدام الدالتين ST_AsText() أو ST_AsBinary() المتوافقتين مع OpenGIS، اللتين لا تُخرجان إلا أشكالًا هندسية ثنائية الأبعاد. تفعلان ذلك عبر استدعاء الدالة ST_Force2D() داخليًا، مما يضيف عبئًا كبيرًا على الأشكال الهندسية الكبيرة. ولتجنّب هذا العبء، قد يكون من المجدي إسقاط تلك الأبعاد الإضافية مسبقًا مرة واحدة وإلى الأبد:
UPDATE mytable SET geom = ST_Force2D(geom); VACUUM FULL ANALYZE mytable;
لاحظ أنك إذا أضفت عمود الأشكال الهندسية باستخدام AddGeometryColumn() فسيوجد قيد على بُعد الشكل الهندسي. ولتجاوزه ستحتاج إلى حذف القيد. تذكّر تحديث المُدخل في جدول geometry_columns وإعادة إنشاء القيد بعد ذلك.
في حالة الجداول الكبيرة، قد يكون من الحكمة تقسيم عملية UPDATE هذه إلى أجزاء أصغر بتقييد UPDATE بجزء من الجدول عبر عبارة WHERE والمفتاح الأساسي أو معيار مناسب آخر، وتشغيل "VACUUM;" بسيط بين عمليات UPDATE. يقلل هذا كثيرًا من الحاجة إلى مساحة قرص مؤقتة. إضافة إلى ذلك، إذا كانت لديك أشكال هندسية مختلطة الأبعاد، فإن تقييد UPDATE بـ "WHERE dimension(geom)>2" يتخطى إعادة كتابة الأشكال الهندسية التي هي ثنائية الأبعاد أصلًا.