شاخص کل در مسیر تصحیح بود

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

یکی از مؤلفه های این آمار تعداد کل ورودی ها در هر جدول و فهرست و همچنین تعداد بلوک های دیسک اشغال شده توسط هر جدول و شاخص است. این اطلاعات در جدول PG_CLASS ، در ستون ها Reltuples و Relpages نگهداری می شود. ما می توانیم با سؤالاتی شبیه به این مورد به آن نگاه کنیم:

relname ، relkind ، reltuples ، relpages از pg_class را انتخاب کنید که در آن نام مانند "tenk1 ٪" ؛نامگذاری |Relkind |reltuples |RELPAGES ----------------------+---------+-----------+---------- TENK1 |r |10000 |358 TENK1_HUNDED |من |10000 |30 TENK1_THOUS_TENTHOUS |من |10000 |30 tenk1_unique1 |من |10000 |30 tenk1_unique2 |من |10000 |30 (5 ردیف)

در اینجا می توانیم ببینیم که Tenk1 شامل 10000 ردیف است ، مانند شاخص های آن ، اما شاخص ها (با کمال تعجب) بسیار کوچکتر از جدول هستند.

به دلایل کارآیی ، Reltuples و Relpages به صورت پرواز به روز نمی شوند و بنابراین معمولاً حاوی مقادیر تا حدودی خارج از تاریخ هستند. آنها با خلاء ، تجزیه و تحلیل و چند دستور DDL مانند ایجاد فهرست به روز می شوند. یک عمل خلاء یا تجزیه و تحلیل که کل جدول را اسکن نمی کند (که معمولاً مورد آن است) به طور تدریجی شمارش reltuples را بر اساس بخشی از جدول که اسکن کرده است به روز می کند و در نتیجه یک مقدار تقریبی ایجاد می شود. در هر صورت ، برنامه ریز مقادیر مورد نظر خود را در PG_CLASS برای مطابقت با اندازه جدول فیزیکی فعلی ، مقیاس می کند ، بنابراین تقریب نزدیکتر را بدست می آورد.

بیشتر نمایش داده ها فقط بخشی از ردیف ها را در یک جدول بازیابی می کنند ، به دلیل جایی که بندهایی که ردیف های مورد بررسی را محدود می کنند. بنابراین ، برنامه ریز باید تخمین هایی از انتخاب بندها ، یعنی کسری از ردیف هایی که با هر شرایط در بند WHERE مطابقت دارند ، تخمین کند. اطلاعات مورد استفاده برای این کار در کاتالوگ سیستم pg_statistic ذخیره می شود. ورودی های PG_STATISTIC توسط دستورات Analyze و Vacuum به روز می شوند و حتی در صورت بروزرسانی تازه تقریباً تقریبی هستند.

به جای اینکه به طور مستقیم به pg_statistic نگاه کنیم ، بهتر است هنگام بررسی آمار به صورت دستی به نمای آن نگاه کنید. PG_STATS به گونه ای طراحی شده است که راحت تر قابل خواندن باشد. علاوه بر این ، pg_stats توسط همه قابل خواندن است ، در حالی که pg_statistic فقط توسط یک سوپر قابل خواندن است.(این امر باعث می شود تا کاربران غیرمجاز از آمار چیزی در مورد محتوای جداول افراد دیگر یاد بگیرند. نمای PG_STATS محدود شده است تا فقط ردیف هایی را در مورد جداول نشان دهد که کاربر فعلی می تواند آن را بخواند.) برای مثال ، ما ممکن است انجام دهیم:

attname ، ارثی ، n_distinct ، array_to_string (most_common_vals ، e ' n') را به عنوان most_common_vals از pg_stats که در آن tableename = 'Road' ؛attname |ارثی |n_distinct |most_common_vals -------+-----------+------------+-------------------------------------- نام |f |-0. 363388 |I- 580 رمپ+ |||I- 880 رمپ+ |||راه آهن SP + |||I- 580 + |||I- 680 نام رمپ |t |-0. 284859 |I- 880 رمپ+ |||I- 580 رمپ+ |||I- 680 رمپ+ |||I- 580 + |||سطح شیب دار Hwy 13 (2 ردیف)

توجه داشته باشید که دو ردیف برای همان ستون نمایش داده می شود ، یکی مطابق با سلسله مراتب میراث کامل که از جدول جاده شروع می شود (وراثت = t) ، و دیگری شامل فقط جدول جاده (ارثی = F).

مقدار اطلاعات ذخیره شده در pg_statistic توسط Analyze ، به ویژه حداکثر تعداد ورودی ها در آرایه های most_common_vals و histogram_bounds برای هر ستون ، می تواند با استفاده از دستور statistics alter table یا در سطح جهانی ، بر اساس ستون به ستون تنظیم شود. متغیر پیکربندی Default_Statistics_Target. حد پیش فرض در حال حاضر 100 ورودی است. بالا بردن این حد ممکن است باعث شود تخمین های دقیق تر برنامه ریز ، به ویژه برای ستون هایی با توزیع داده های نامنظم ، با قیمت مصرف فضای بیشتر در PG_STATISTIC و کمی بیشتر برای محاسبه تخمین ها انجام شود. برعکس ، ممکن است برای ستون هایی با توزیع داده های ساده ، یک حد پایین تر کافی باشد.

جزئیات بیشتر در مورد استفاده از برنامه ریز از آمار را می توان در فصل 75 یافت.

14. 2. 2. آمار گسترده

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

از آنجا که تعداد ترکیبات ستون ممکن بسیار بزرگ است ، محاسبه آمار چند متغیره به طور خودکار غیر عملی است. درعوض ، اشیاء آماری گسترده ، که بیشتر آنها فقط اشیاء آماری نامیده می شوند ، می توانند به سرور آموزش دهند تا آمار را در مجموعه های جالب ستون ها بدست آورد.

اشیاء آماری با استفاده از دستور ایجاد آمار ایجاد می شوند. ایجاد چنین شیء صرفاً یک کاتالوگ را ایجاد می کند که ابراز علاقه به آمار می کند. جمع آوری داده های واقعی توسط Analyze (یا یک دستور دستی ، یا پس زمینه Auto-Analyze) انجام می شود. مقادیر جمع آوری شده را می توان در کاتالوگ pg_statistic_ext_data بررسی کرد.

تجزیه و تحلیل آمار گسترده را بر اساس همان نمونه از ردیف های جدول که برای محاسبه آمار منظم تک ستونی لازم است ، محاسبه می کند. از آنجا که اندازه نمونه با افزایش هدف آمار برای جدول یا هر یک از ستون های آن افزایش می یابد (همانطور که در بخش قبلی توضیح داده شده است) ، یک هدف آمار بزرگتر به طور معمول منجر به آمار دقیق تر و همچنین زمان بیشتری برای محاسبه آنها می شود.

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

14. 2. 2. 1. وابستگی های عملکردی

ساده ترین نوع آمار گسترده ، وابستگی های عملکردی را دنبال می کند ، مفهومی که در تعاریف اشکال طبیعی پایگاه داده استفاده می شود. ما می گوییم که ستون B از نظر عملکردی وابسته به ستون A است اگر دانش از مقدار A برای تعیین مقدار B کافی باشد ، یعنی هیچ دو ردیف وجود ندارد که دارای یک مقدار یکسان از مقادیر متفاوت B باشند. در یک بانک اطلاعاتی کاملاً عادی ، وابستگی های عملکردی فقط باید در کلیدهای اولیه و سوپر کلیدها وجود داشته باشد. با این حال ، در عمل بسیاری از مجموعه داده ها به دلایل مختلف به طور کامل عادی نمی شوند. تقاضای عمدی به دلایل عملکرد یک نمونه مشترک است. حتی در یک بانک اطلاعاتی کاملاً عادی ، ممکن است بین برخی ستون ها همبستگی جزئی وجود داشته باشد ، که می تواند به عنوان وابستگی عملکردی جزئی بیان شود.

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

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

در اینجا نمونه ای از جمع آوری آمار وابسته به عملکرد:

CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes; ANALYZE zipcodes; SELECT stxname, stxkeys, stxddependencies FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) WHERE stxname = 'stts'; stxname | stxkeys | stxddependencies ---------+---------+------------------------------------------ stts | 1 5 | 5": 1.000000, "5 => 1": 0.423130>(1 ردیف)

در اینجا مشاهده می شود که ستون 1 (کد پستی) ستون 5 (شهر) را به طور کامل تعیین می کند ، بنابراین ضریب 1. 0 است ، در حالی که شهر فقط کد پستی را در حدود 42 ٪ از زمان تعیین می کند ، به این معنی که بسیاری از شهرها (58 ٪) وجود دارند که وجود داردبا بیش از یک کد پستی واحد نشان داده شده است.

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

14. 2. 2. 1. 1. محدودیت وابستگی های عملکردی

وابستگی های عملکردی در حال حاضر فقط در هنگام بررسی شرایط برابری ساده که ستون ها را با مقادیر ثابت و در بندهایی با مقادیر ثابت مقایسه می کنند ، اعمال می شود. آنها برای بهبود تخمین برای شرایط برابری با مقایسه دو ستون یا مقایسه یک ستون با یک عبارت و نه برای بندهای دامنه ، مانند یا هر نوع شرایط دیگر استفاده نمی شوند.

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

* را از Zipcodes که در آن شهر = 'سان فرانسیسکو' و ZIP = '94105' انتخاب کنید ، انتخاب کنید.

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

* را از Zipcodes که در آن City = 'San Francisco' و Zip = '90210' انتخاب کنید ؛

حتی اگر واقعاً ردیف های صفر وجود داشته باشد که این پرس و جو را برآورده کنند. آمار وابستگی عملکردی اطلاعات کافی را برای نتیجه گیری ارائه نمی دهد.

در بسیاری از شرایط عملی ، این فرض معمولاً راضی است. به عنوان مثال ، ممکن است یک GUI در برنامه وجود داشته باشد که فقط امکان انتخاب مقادیر سازگار شهر و کد پستی را در یک پرس و جو فراهم می کند. اما اگر اینگونه نباشد ، وابستگی های عملکردی ممکن است گزینه ای مناسب نباشد.

14. 2. 2. 2. شمارش چند متغیره n

آمار تک ستونی تعداد مقادیر متمایز را در هر ستون ذخیره می کند. برآورد تعداد مقادیر متمایز هنگام ترکیب بیش از یک ستون (به عنوان مثال ، برای گروه توسط A ، B) اغلب اشتباه می شوند وقتی که برنامه ریز فقط داده های آماری یک ستون داشته باشد و باعث انتخاب برنامه های بد شود.

برای بهبود چنین تخمین هایی ، تجزیه و تحلیل می تواند آمار n- دور را برای گروه های ستون جمع کند. مانند گذشته ، انجام این کار برای هر گروه بندی ستون ممکن غیر عملی است ، بنابراین داده ها فقط برای گروه های ستون هایی که در یک شیء آماری که با گزینه ndistinct تعریف شده اند ، جمع می شوند. داده ها برای هر ترکیب ممکن از دو یا چند ستون از مجموعه ستون های ذکر شده جمع آوری می شود.

در ادامه مثال قبلی ، تعداد N-Distinct در یک جدول از کدهای زیپ ممکن است مانند موارد زیر باشد:

ایجاد آمار stts2 (ndistinct) در شهر ، ایالت ، زیپ از Zipcode. تجزیه و تحلیل Zipcodes ؛stxKeys را به عنوان k ، stxdndistinct به عنوان nd از pg_statistic_ext به pg_statistic_ext_data on (oid = stxoid) انتخاب کنید که در آن stxname = 'stts2' ؛-[رکورد 1] ---------------------------------------------------------k |1 2 5 nd |(1 ردیف)

این نشان می دهد که سه ترکیب از ستون وجود دارد که 33178 مقادیر متمایز دارند: کد پستی و حالت ؛کد پستی و شهر ؛و کد پستی ، شهر و ایالت (این واقعیت که همه آنها برابر هستند با توجه به اینکه کد پستی به تنهایی در این جدول بی نظیر است). از طرف دیگر ، ترکیب شهر و ایالت فقط 27435 ارزش متمایز دارد.

توصیه می شود فقط در مورد ترکیب ستون هایی که در واقع برای گروه بندی استفاده می شوند ، اشیاء آماری غیرقانونی ایجاد کنید و برای آنها تخمین غلط تعداد گروه ها منجر به برنامه های بد شود. در غیر این صورت ، چرخه های تجزیه و تحلیل فقط هدر می روند.

14. 2. 2. 3. لیست های MCV چند متغیره

نوع دیگری از آماری که برای هر ستون ذخیره شده است ، لیست های متداول ترین است. این امکان تخمین های بسیار دقیق برای ستون های جداگانه را فراهم می کند ، اما ممکن است منجر به سوء استفاده قابل توجهی برای نمایش داده شود با شرایط در ستون های مختلف.

برای بهبود چنین تخمین ها ، تجزیه و تحلیل می تواند لیست های MCV را در ترکیب ستون ها جمع آوری کند. به طور مشابه با وابستگی های عملکردی و ضرایب n- dist ، انجام این کار برای هر گروه بندی ستون ممکن غیر عملی است. حتی بیشتر در این حالت ، به عنوان لیست MCV (بر خلاف وابستگی های عملکردی و ضرایب n- dist) مقادیر ستون مشترک را ذخیره می کند. بنابراین داده ها فقط برای آن دسته از ستونهای موجود در یک شیء آماری که با گزینه MCV تعریف شده اند ، جمع آوری می شود.

در ادامه مثال قبلی ، لیست MCV برای یک جدول از کدهای زیپ ممکن است مانند موارد زیر باشد (برخلاف انواع ساده تر آمار ، عملکردی برای بازرسی از محتوای MCV لازم است):

ایجاد آمار STTS3 (MCV) در شهر ، ایالت از Zipcodes. تجزیه و تحلیل Zipcodes ؛m.* از pg_statistic_ext به pg_statistic_ext_data on (oid = stxoid) ، pg_mcv_list_items (stxdmcv) m که در آن stxname = 'stts3' ؛فهرست |مقادیر |NULLS |فرکانس |base_frequency -------+------------------------+-------+-----------+---------------- 0 |||0. 003467 |2. 7E-05 1 |||0. 003067 |1. 9E-05 2 |||0. 002167 |0. 000133 3 |||0. 002 |0. 000113 4 |||0. 001967 |0. 000114 5 |||0. 001633 |3. 3E-05 6 |||0. 001433 |7. 8E-05 7 |||0. 0014 |6E-05 8 |||0. 001367 |8. 8e-05 9 |||0. 001333 |5. 1E-05.(99 ردیف)

این نشان می دهد که رایج ترین ترکیب شهر و ایالت واشنگتن در دی سی است ، با فرکانس واقعی (در نمونه) حدود 0. 35 ٪. فرکانس پایه این ترکیب (همانطور که از فرکانس های ساده در هر بخش محاسبه می شود) تنها 0. 0027 ٪ است که در نتیجه دو مرتبه از قدر زیر تخمین ها ایجاد می شود.

توصیه می شود فقط در ترکیب ستون هایی که در واقع در شرایط با هم مورد استفاده قرار می گیرند ، اشیاء آماری MCV ایجاد کنید و برای آنها نادرست تعداد گروه ها منجر به برنامه های بد شود. در غیر این صورت ، چرخه های تجزیه و تحلیل و برنامه ریزی فقط هدر رفته است.

تصحیح

اگر در اسناد و مدارک صحیح مشاهده می کنید ، تجربه شما را با ویژگی خاص مطابقت ندارد یا نیاز به توضیح بیشتر دارد ، لطفاً از این فرم برای گزارش یک موضوع مستند استفاده کنید.

کپی رایت © 1996-2023 گروه توسعه جهانی PostgreSQL