هذه المقالة هي امتداد للقصة الجديدة في PostgreSQL 12. لقد قمنا بالفعل بتحليل SQL / JSON (JSONPath patch) في المقال
"ما الذي تجمد على تجميد الميزات 2019. الجزء الأول JSONPath" ، والآن حان دور CTE.
CTE
CTE هو التعبير الشائع عن الجدول - تعبيرات الجدول الشائعة ، يطلق عليها أيضًا WITH builds. في الواقع ، هذا هو إنشاء جداول مؤقتة ، ولكنها موجودة فقط لاستعلام واحد ، وليس للدورة. يمكن الوصول إليها داخل هذا الطلب. تتم قراءة هذا الطلب جيدًا ، وهو مفهوم ، ومن السهل تعديله إذا لزم الأمر. هذا شيء شائع جدًا ، وقد كان في PostgreSQL لفترة طويلة.
لكن وسائل الراحة يمكن أن تكون مكلفة. تتعلق المشاكل بتجسيد التعبير بعد AS داخل بنية WITH ... AS (). يُطلق عليه أيضًا تعبير داخلي ويتم حسابه قبل البدء في حساب الباقي ، ولا يمكن تضمينه في استعلام المستوى الأعلى (بدون تضمين). التخطيط لهذا التعبير لا يأخذ في الاعتبار بقية الطلب. هذا السلوك يسمى حاجز التحسين ، أو المبارزة. بالإضافة إلى ذلك ، يتطلب التجسيد نفسه work_mem. وإذا كانت العينة كبيرة ، فستبدأ المشاكل (على سبيل المثال ، هناك
تقرير مقدم من Ivan Frolkov في PGConf 2019).
لا يُعد إخفاء المحسن والبحث عنه ، والذي سنحلله أدناه ، خطأ بشكل عام ، ولكنه ميزة. بالطبع ، هناك مواقف عندما يلغي الحساب المبدئي لجزء من التعبير ، على سبيل المثال ، العمليات المتكررة غير الضرورية في الاستعلامات المتكررة. من ناحية أخرى ، استخدم العديد من المطورين CTE كطريقة عرض دون التفكير في الحاجز ، ونتيجة لذلك ، تم تنفيذ الطلبات المقدمة من CTE ليس فقط بشكل أبطأ من الطلبات المكافئة (ولكن الأكثر تطوراً) مع استعلامات فرعية ، ولكن أبطأ بأوامر من حيث الحجم. بعد تقييم إيجابيات وسلبيات ، اتخذ المجتمع خطوة حاسمة: لقد غير السلوك الافتراضي.
سنلاحظ عمل CTE على مثل هذه اللوحة:
CREATE TABLE xytable AS SELECT x, x AS y FROM generate_series(1,10000000) AS x; CREATE INDEX ON xytable(x,y);
Table "public.xytable" Column | Type | Collation | Nullable | Default --------------+---------+------------------+----------------+--------- x | integer | | | y | integer | | | Indexes: "xytable_x_y_idx" btree (x, y)
لنبدأ بطلب بسيط:
SELECT * FROM xytable WHERE x=2 AND y>1; QUERY PLAN
كل شيء يعتبر على الفور ، يتم استخدام الفهرس فقط.
استعلام مع استعلام فرعي يحسب نفسه ، ولكن مع بناء جملة أكثر تعقيدًا بقليل:
SELECT * FROM (SELECT * FROM xytable WHERE y>1) AS t WHERE x=2; QUERY PLAN
كل شيء في النظام ، وحساب مؤشر سريع جدا.
والآن طلب أكثر مكافئًا منطقيًا ، ولكن مع CTE:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
هذا التأخير مرئي للعين المجردة. لن تشرب القهوة ، ولكن هناك وقتًا كافيًا للنظر في البريد (عندما يكون لدينا الإصدار الحادي عشر أو إصدار سابق).
وإليك ما حدث: في حالة الاستعلامات الفرعية ، أدرك المحسّن على الفور أنه يمكن دمج الشروط x = 2 و y> 1 في مرشح واحد والبحث فيه عن طريق الفهرس. في حالة CTE ، ليس للمحسن أي خيار: يجب عليه أولاً التعامل مع الحالة داخل WITH ... AS ، بناء على النتيجة ، ثم العمل عليها.
وهنا لا تتمثل النقطة في أن التجسيد سوف يتطلب موارد: إذا كان الشرط y <3 ، فلن يتعين تحقيق ملايين السجلات ، ولكن فقط 2. يتم قضاء وقت هائل لاستعلام بسيط في البحث المتسلسل ، لا يمكن للمحسن استخدام بحث الفهرس بسبب بحيث يتم بناء الفهرس المركب على x ، وعندئذٍ فقط على y ، ولن يعرف أي شيء عن استعلام بالشرط x = 2 حتى يستوفي شرط CTE الداخلي. إنه خارج الحاجز.
لذلك ، قبل PostgreSQL 12 ، كان الإعداد الافتراضي هو التجسيد ، والآن غيابه. نطلق نفس الطلب بناءً على الإصدار الجديد. الحاجز ، كما كان ، يرى المحسن على الفور الطلب بالكامل:
WITH yy AS ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN ------------------------------------------ Index Only Scan using xytable_x_y_idx1 on xytable (cost=0.43..8.46 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.067 ms Execution Time: 0.029 ms (5 rows)
تعلم المحسن على الفور دمج الشروط بالترتيب الأمثل - كما كان الحال مع الاستعلامات الفرعية.
لكن الإعدادات الافتراضية هي افتراضات ، وللملكية الكاملة للموقف الآن ، في الإصدار 12 ، هناك تجسيد متحكم فيه يتم التحكم فيه من CTE:
WITH cte_name AS [NOT] MATERIALIZED
لنتحقق:
EXPLAIN ANALYZE WITH yy AS MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2;
QUERY PLAN --------------------------- CTE Scan on yy (cost=356423.68..581401.19 rows=49995 width=8) (actual time=661.038..3603.292 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 CTE yy -> Bitmap Heap Scan on cte (cost=187188.18..356423.68 rows=9999000 width=8) (actual time=661.032..2102.040 rows=9999999 loops=1) Recheck Cond: (y > 1) Heap Blocks: exact=44248 -> Bitmap Index Scan on xytable_x_y_idx1 (cost=0.00..184688.43 rows=9999000 width=0) (actual time=655.519..655.519 rows=9999999 loops=1) Index Cond: (y > 1) Planning Time: 0.086 ms Execution Time: 3612.840 ms (11 rows)
كل شيء كما هو الحال في 11 وقبل ذلك ، يمكنك إلقاء نظرة على البريد في وضع الاستعداد لنتائج الاستعلام. نحن نحظر تجسيد ، إزالة الحاجز:
EXPLAIN ANALYZE WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y>1) SELECT * FROM yy WHERE x=2; QUERY PLAN
مرة أخرى ، لا راحة: إنه مهم على الفور.
بقيت الفروق الدقيقة. لكن الفروق الدقيقة الهامة.
تتحقق تقنية CTE افتراضيًا إذا تم الوصول إليها أكثر من مرة.
للوهلة الأولى ، التجسيد في مثل هذه الحالات هو قرار معقول: لماذا حساب الشيء نفسه مرتين. في الممارسة العملية ، وهذا يؤدي في كثير من الأحيان إلى ما لاحظنا أعلاه. لفرض الرفض من التجسيد ، من الضروري أن تطلب بشكل صريح المحسن: غير مضروب.
نحن ننفذ دون طلب مواد مع ضعف حيث:
WITH yy AS ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=3922.274..3922.275 rows=1 loops=1) CTE yy -> Seq Scan on xytable (actual time=0.023..1295.262 rows=9999999 loops=1) Filter: (y > 1) Rows Removed by Filter: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=3109.687..3109.687 rows=1 loops=1) -> CTE Scan on yy (actual time=0.027..3109.682 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 InitPlan 3 (returns $2) -> Aggregate (actual time=812.580..812.580 rows=1 loops=1) -> CTE Scan on yy yy_1 (actual time=0.016..812.575 rows=1 loops=1) Filter: (x = 2) Rows Removed by Filter: 9999998 Planning Time: 0.136 ms Execution Time: 3939.848 ms (17 rows)
والآن سنكتب صراحة حظرًا على التجسيد:
WITH yy AS NOT MATERIALIZED ( SELECT * FROM xytable WHERE y > 1) SELECT ( SELECT count(*) FROM yy WHERE x=2), ( SELECT count(*) FROM yy WHERE x=2);
QUERY PLAN --------------------------------------------------------------------------- Result (actual time=0.035..0.035 rows=1 loops=1) InitPlan 1 (returns $0) -> Aggregate (actual time=0.024..0.024 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable (actual time=0.019..0.020 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 InitPlan 2 (returns $1) -> Aggregate (actual time=0.006..0.006 rows=1 loops=1) -> Index Only Scan using xytable_x_y_idx on xytable cte_1 (actual time=0.004..0.005 rows=1 loops=1) Index Cond: ((x = 2) AND (y > 1)) Heap Fetches: 1 Planning Time: 0.253 ms Execution Time: 0.075 ms (13 rows)
دائمًا ما يتم تنفيذ كتابة CTE ، ولا يتم الرجوع إليها أبدًا.
هذا واضح من الخطة: not_executed ليس فيه. هذا صحيح بالنسبة للإصدارات السابقة ، ولكن يجدر تذكر ذلك ، والبناء MATERIALIZED (NOT) ينطبق على التعبير القابل للتنفيذ في الإصدار 12.
EXPLAIN (COSTS OFF) WITH yy AS ( SELECT * FROM xytable WHERE y > 1), not_executed AS ( SELECT * FROM xytable), always_executed AS ( INSERT INTO xytable VALUES(2,2) RETURNING *) SELECT FROM yy WHERE x=2;
QUERY PLAN ----------------------------- CTE Scan on yy Filter: (x = 2) CTE yy -> Seq Scan on cte Filter: (y > 1) CTE always_executed -> Insert on cte cte_1 -> Result (5 rows)
وقاعدة أخرى واحدة:
استفسارات العودية مع تتجسد دائما.
هو دائما ، وليس افتراضيا. إذا طلبنا من المُحسِّن: لم يتم MATERIALIZED ، فلن يكون هناك أي خطأ ، وستظل التجسيد. هذا هو قرار المجتمع واعية.
سننظر في المثال التوضيحي الواجب المنزلي. هذا كل شيء لهذا اليوم.
يستخدم هذا الجزء من المراجعة المكرس للجديد في CTE أمثلة وشظايا من تقرير "Postgres 12 in Etudes" ، الذي قرأه أوليغ بارتونوف في Saint Highload ++ في سان بطرسبرج في 9 أبريل من هذا العام.في السلسلة التالية -
KNN .