لینک کوتاه مطلب : https://hsgar.com/?p=5296

ترتیب ستون در PostgreSQL مهم است

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

ایجاد یک میز بزرگ

سوال اول این است: چگونه می توانیم یک جدول حاوی ستون های متعدد ایجاد کنیم؟ ساده ترین راه این است که به سادگی تولید کنید CREATE TABLE عبارت با استفاده ازgene_series:

test=# SELECT 'CREATE TABLE t_broad ('

|| string_agg('t_' || x

|| ' varchar(10) DEFAULT ''a'' ', ', ')

|| ' )'

FROM generate_series(1, 4) AS x;

?column?       ----------------------------------------------------------

CREATE TABLE t_broad (

t_1 varchar(10) DEFAULT 'a' ,

t_2 varchar(10) DEFAULT 'a' ,

t_3 varchar(10) DEFAULT 'a' , t_4 v

archar(10) DEFAULT 'a'  )

(1 row)

test=# gexec

CREATE TABLE

برای سادگی، من فقط از 4 ستون در اینجا استفاده کرده ام. هنگامی که دستور تولید شد، می توانیم از gexec برای اجرای رشته ای که به تازگی کامپایل کرده ایم استفاده کنیم. gexec یک چیز واقعا قدرتمند است: نتیجه قبلی را به عنوان ورودی SQL در نظر می گیرد که دقیقاً همان چیزی است که ما در اینجا می خواهیم. جدولی حاوی 4 ستون برای ما باقی می گذارد.

با این حال، بیایید جدول را رها کنیم و یک جدول واقعا بزرگ ایجاد کنیم.


test=# DROP TABLE t_broad ;

DROP TABLE

یک جدول بسیار گسترده ایجاد کنید

عبارت زیر جدولی حاوی 1500 ستون ایجاد می کند. توجه داشته باشید که حد بالایی 1600 ستون است:

test=# SELECT 'CREATE TABLE t_broad ('

|| string_agg('t_' || x

|| ' varchar(10) DEFAULT ''a'' ', ', ') || ' )'

FROM generate_series(1, 1500) AS x;

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

بیایید جدول را پر کنیم و 1 میلیون ردیف اضافه کنیم:


test=# timing

Timing is on.

test=# INSERT INTO t_broad

SELECT 'a' FROM generate_series(1, 1000000);

INSERT 0 1000000

Time: 67457,107 ms (01:07,457)

test=# VACUUM ANALYZE ;

VACUUM

Time: 155935,761 ms (02:35,936)

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

جدولی که به تازگی ایجاد کرده ایم تقریباً 4 گیگابایت است که به راحتی می توان با استفاده از خط زیر تعیین کرد:


test=# SELECT pg_size_pretty(pg_total_relation_size('t_broad'));

pg_size_pretty

----------------

3907 MB

(1 row)

دسترسی به ستون های مختلف

PostgreSQL داده ها را در ردیف ها ذخیره می کند. همانطور که می دانید داده ها را می توان به صورت ستونی یا ردیفی ذخیره کرد. بسته به مورد استفاده شما ممکن است یک یا آن گزینه سودمند باشد. در مورد OLTP یک رویکرد مبتنی بر ردیف معمولاً بسیار کارآمدتر است.

بیایید انجام دهیم count(*) و ببینید چقدر طول می کشد:


test=# SELECT count(*) FROM t_broad;

count

---------

1000000

(1 row)



Time: 416,732 ms

ما می توانیم پرس و جو را در حدود 400 میلی ثانیه اجرا کنیم که کاملاً خوب است. همانطور که انتظار می رود، بهینه ساز به سراغ اسکن متوالی موازی می رود:


test=# explain SELECT count(*) FROM t_broad;

QUERY PLAN

--------------------------------------------------------------------

Finalize Aggregate (cost=506208.55..506208.56 rows=1 width=8)

-> Gather (cost=506208.33..506208.54 rows=2 width=8)

Workers Planned: 2

-> Partial Aggregate

(cost=505208.33..505208.34 rows=1 width=8)

-> Parallel Seq Scan on t_broad

(cost=0.00..504166.67 rows=416667 width=0)

JIT:

Functions: 4

Options: Inlining true, Optimization true, Expressions true,

Deforming true

(8 rows)

بیایید این را با شمارش در ستون اول مقایسه کنیم. تفاوت کوچکی در عملکرد خواهید دید. دلیل این است که count(*) باید وجود ردیف while را بررسی کند count(column) باید بررسی کند که آیا a NULL ارزش به کل داده می شود یا خیر. در صورت NULL مقدار باید نادیده گرفته شود:

test=# SELECT count(t_1) FROM t_broad;

count

---------

1000000

(1 row)



Time: 432,803 ms

اما، بیایید ببینیم اگر به ستون شماره 100 دسترسی پیدا کنیم چه اتفاقی می افتد؟ زمان انجام این کار به طور قابل توجهی متفاوت خواهد بود:

test=# SELECT count(t_100) FROM t_broad;

count

---------

1000000

(1 row)



Time: 857,897 ms

زمان اجرا اساساً دو برابر شده است. اگر روی ستون شماره 1000 حساب کنیم، عملکرد حتی بدتر است:


test=# SELECT count(t_1000) FROM t_broad;

count

---------

1000000

(1 row)



Time: 8570,238 ms (00:08,570)

وای، ما در حال حاضر 20 برابر کندتر از قبل هستیم. این یک تفاوت کوچک نیست، بلکه یک مشکل اساسی است که باید درک شود.

رفع مشکلات عملکرد PostgreSQL: ترتیب ستون

برای درک اینکه چرا مشکل در وهله اول اتفاق می افتد، باید نگاهی به نحوه ذخیره داده ها توسط PostgreSQL بیندازیم: بعد از هدر تاپلی که در هر ردیف وجود دارد، چند عدد دریافت کردیم. varchar ستون ها. ما فقط استفاده کردیم varchar اینجا برای اثبات موضوع مشکلات مشابه با انواع داده های دیگر اتفاق می افتد – مشکل به سادگی آشکارتر است varchar چون از نظر داخلی پیچیده تر از مثلاً integer.

PostgreSQL چگونه به یک ستون دسترسی پیدا می کند؟ سطر را واکشی می کند و سپس این تاپل را تشریح می کند تا موقعیت ستون مورد نظر در داخل سطر را محاسبه کند. بنابراین اگر بخواهیم به ستون #1000 دسترسی داشته باشیم، به این معنی است که باید بفهمیم که 999 ستون اول قبل از ستون انتخابی ما واقعا چقدر طول دارند. این می تواند بسیار پیچیده باشد. برای integer ما به سادگی باید 4 را اضافه کنیم، اما در صورت وجود varchar، این عمل به چیزی واقعاً گران تبدیل می شود. بیایید بررسی کنیم که PostgreSQL چگونه ذخیره می شود varchar (فقط ببینم چرا اینقدر گران است):

  • 1 بیت نشان دهنده کوتاه (127 بایت) در مقابل رشته طولانی (> 127 بیت)
  • طول 7 بیت یا 31 بیت (بسته به بیت اول)
  • “data” + (برای پایان دادن به رشته)
  • تراز (برای اطمینان از اینکه ستون بعدی با مضرب طول کلمه CPU شروع می شود)

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

سرانجام …

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

اگر به راه‌های دیگری برای بهبود عملکرد علاقه دارید، وبلاگ من را در CLUSTER بخوانید.

لینک منبع

ارسال یک پاسخ

آدرس ایمیل شما منتشر نخواهد شد.