PostgreSQL Query Profiler៖ របៀបផ្គូផ្គងផែនការ និងសំណួរ

ជាច្រើនដែលកំពុងប្រើរួចហើយ ពន្យល់.tensor.ru - សេវាកម្មមើលឃើញផែនការ PostgreSQL របស់យើងប្រហែលជាមិនដឹងពីប្រទេសមហាអំណាចមួយរបស់វាទេ - ការប្រែក្លាយបំណែកដែលពិបាកអាននៃកំណត់ហេតុម៉ាស៊ីនមេ...

... ចូលទៅក្នុងសំណួរដែលបានរចនាយ៉ាងស្រស់ស្អាតជាមួយនឹងការណែនាំតាមបរិបទសម្រាប់ថ្នាំងផែនការដែលត្រូវគ្នា៖

នៅក្នុងប្រតិចារិកនៃផ្នែកទីពីរនៃរបស់គាត់។ របាយការណ៍នៅ PGConf.Russia 2020 ខ្ញុំនឹងប្រាប់អ្នកពីរបៀបដែលយើងគ្រប់គ្រងដើម្បីធ្វើរឿងនេះ។

ប្រតិចារឹកនៃផ្នែកទីមួយ ដែលឧទ្ទិសដល់បញ្ហាដំណើរការសំណួរធម្មតា និងដំណោះស្រាយរបស់ពួកគេ អាចរកបាននៅក្នុងអត្ថបទ "រូបមន្តសម្រាប់សំណួរ SQL ឈឺ".

ដំបូងយើងចាប់ផ្តើមលាបពណ៌ - ហើយយើងនឹងលែងពណ៌តាមផែនការ យើងបានលាបពណ៌វារួចហើយ យើងមានវាស្អាតហើយអាចយល់បាន ប៉ុន្តែសំណើមួយ។

វាហាក់ដូចជាពួកយើងថាជាមួយនឹង "សន្លឹក" ដែលមិនបានធ្វើទ្រង់ទ្រាយបែបនេះ សំណើដែលទាញចេញពីកំណត់ហេតុមើលទៅអាក្រក់ណាស់ ហើយដូច្នេះវាមានការរអាក់រអួល។


ជាពិសេសនៅពេលដែលអ្នកអភិវឌ្ឍន៍ "កាវបិទ" តួនៃសំណើនៅក្នុងកូដ (នេះជាការពិតណាស់, លំនាំមួយ, ប៉ុន្តែវាកើតឡើង) នៅក្នុងបន្ទាត់មួយ។ រន្ធត់ណាស់!

តោះគូរនេះកាន់តែស្អាត។


ហើយប្រសិនបើយើងអាចគូរវាយ៉ាងស្រស់ស្អាត នោះគឺជាការរុះរើ និងដាក់បញ្ចូលគ្នានូវតួនៃសំណើនោះ នោះយើងអាច "ភ្ជាប់" នូវតម្រុយមួយទៅកាន់វត្ថុនីមួយៗនៃសំណើនេះ - អ្វីដែលបានកើតឡើងនៅចំណុចដែលត្រូវគ្នានៅក្នុងផែនការ។

មែកធាងវាក្យសម្ព័ន្ធសំណួរ

ដើម្បីធ្វើដូច្នេះ សំណើត្រូវតែញែកជាដំបូង។


ដោយសារតែយើងមាន ស្នូលនៃប្រព័ន្ធដំណើរការលើ NodeJSបន្ទាប់មកយើងបង្កើតម៉ូឌុលសម្រាប់វា អ្នកអាចធ្វើបាន ស្វែងរកវានៅលើ GitHub. តាមការពិត ទាំងនេះត្រូវបានពង្រីក "ការចង" ទៅនឹងផ្នែកខាងក្នុងនៃកម្មវិធីញែក PostgreSQL ខ្លួនវាផ្ទាល់។ នោះគឺវេយ្យាករណ៍ត្រូវបានចងក្រងជាគោលពីរយ៉ាងសាមញ្ញ ហើយការចងត្រូវបានធ្វើឡើងពី NodeJS ។ យើង​យក​ម៉ូឌុល​របស់​អ្នក​ដទៃ​មក​ធ្វើ​ជា​មូលដ្ឋាន - វា​មិន​មាន​អាថ៌កំបាំង​អ្វី​ធំដុំ​នៅ​ទីនេះ​ទេ។

យើងបញ្ចូលតួនៃសំណើជាធាតុបញ្ចូលទៅក្នុងមុខងាររបស់យើង - នៅលទ្ធផល យើងទទួលបានមែកធាងវាក្យសម្ព័ន្ធដែលបានញែកក្នុងទម្រង់ជាវត្ថុ JSON ។


ឥឡូវនេះ យើងអាចរត់កាត់មែកធាងនេះក្នុងទិសដៅផ្ទុយ ហើយប្រមូលផ្តុំសំណើជាមួយនឹងការចូលបន្ទាត់ ការដាក់ពណ៌ និងទម្រង់ដែលយើងចង់បាន។ ទេ វា​មិន​អាច​ប្ដូរ​តាម​បំណង​បាន​ទេ ប៉ុន្តែ​វា​ហាក់​ដូច​ជា​យើង​ថា​វា​នឹង​មាន​ភាព​ងាយស្រួល។

ការគូសផែនទីសំណួរ និងគម្រោងថ្នាំង

ឥឡូវនេះសូមមើលពីរបៀបដែលយើងអាចបញ្ចូលគ្នានូវផែនការដែលយើងបានវិភាគក្នុងជំហានដំបូង និងសំណួរដែលយើងបានវិភាគក្នុងជំហានទីពីរ។

ចូរយកឧទាហរណ៍សាមញ្ញមួយ - យើងមានសំណួរដែលបង្កើត CTE ហើយអានពីវាពីរដង។ គាត់បង្កើតផែនការបែបនេះ។

CTE

ប្រសិនបើអ្នកក្រឡេកមើលវាដោយប្រុងប្រយ័ត្នរហូតដល់កំណែ 12 (ឬចាប់ផ្តើមពីវាដោយប្រើពាក្យគន្លឹះ MATERIALIZED) ការបង្កើត CTE គឺជាឧបសគ្គដាច់ខាតសម្រាប់អ្នករៀបចំផែនការ.


នេះមានន័យថាប្រសិនបើយើងឃើញជំនាន់ CTE នៅកន្លែងណាមួយនៅក្នុងសំណើ និងថ្នាំងនៅកន្លែងណាមួយនៅក្នុងផែនការ CTEបន្ទាប់មកថ្នាំងទាំងនេះប្រាកដជា "ប្រយុទ្ធ" ជាមួយគ្នា យើងអាចបញ្ចូលគ្នាបានភ្លាមៗ។

បញ្ហាជាមួយសញ្ញាផ្កាយ៖ CTEs អាចត្រូវបានដាក់។

មានសំបុកដែលមិនសូវល្អ ហើយថែមទាំងមានឈ្មោះដូចគ្នាទៀតផង។ ឧទាហរណ៍អ្នកអាចនៅខាងក្នុង CTE A ដើម្បីបង្កើត CTE Xនិងនៅកម្រិតដូចគ្នានៅខាងក្នុង CTE B ធ្វើ​វា​ម្តង​ទៀត CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

នៅពេលប្រៀបធៀប អ្នកត្រូវតែយល់ពីរឿងនេះ។ ការយល់ដឹងនេះ "ដោយភ្នែករបស់អ្នក" - សូម្បីតែមើលឃើញផែនការសូម្បីតែមើលឃើញរាងកាយនៃសំណើ - គឺពិបាកខ្លាំងណាស់។ ប្រសិនបើជំនាន់ CTE របស់អ្នកស្មុគស្មាញ ជាប់គាំង ហើយសំណើមានច្រើន នោះវានឹងសន្លប់ទាំងស្រុង។

សហជីព

ប្រសិនបើយើងមានពាក្យគន្លឹះនៅក្នុងសំណួរ UNION [ALL] (ប្រតិបត្តិករនៃការចូលរួមគំរូពីរ) បន្ទាប់មកនៅក្នុងផែនការវាត្រូវគ្នាទៅនឹងថ្នាំងណាមួយ។ Appendឬខ្លះ Recursive Union.


អ្វីដែលជា "ខាងលើ" ខាងលើ UNION - នេះគឺជាកូនចៅដំបូងនៃថ្នាំងរបស់យើងដែល "ខាងក្រោម" - ទីពីរ។ ប្រសិនបើឆ្លងកាត់ UNION យើងមានប្លុកជាច្រើន "ស្អិតជាប់" ក្នុងពេលតែមួយ Append- វា​នឹង​នៅ​តែ​មាន​ថ្នាំង​មួយ​ប៉ុណ្ណោះ ប៉ុន្តែ​វា​នឹង​មិន​មាន​ពីរ​ទេ ប៉ុន្តែ​មាន​កូន​ជា​ច្រើន - តាម​លំដាប់​ដែល​ពួក​គេ​ទៅ​រៀង​ខ្លួន៖

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

បញ្ហាជាមួយសញ្ញាផ្កាយ៖ នៅខាងក្នុងការបង្កើតគំរូឡើងវិញ (WITH RECURSIVE) ក៏អាចមានច្រើនជាងមួយ។ UNION. ប៉ុន្តែមានតែប្លុកចុងក្រោយបំផុតបន្ទាប់ពីមួយចុងក្រោយគឺតែងតែកើតឡើងវិញ។ UNION. ទាំងអស់ខាងលើគឺតែមួយ ប៉ុន្តែខុសគ្នា UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

អ្នក​ក៏​ត្រូវ​មាន​លទ្ធភាព​«បិទ»​ឧទាហរណ៍​បែប​នេះ។ ក្នុងឧទាហរណ៍នេះយើងឃើញ UNION- មាន 3 ផ្នែកនៅក្នុងសំណើរបស់យើង។ ដូច្នោះហើយមួយ។ UNION ត្រូវនឹង Append-node និងមួយទៀត - Recursive Union.

អាន - សរសេរទិន្នន័យ

អ្វីៗ​ត្រូវ​បាន​រៀប​ចំ​រួច​ហើយ ឥឡូវ​យើង​ដឹង​ថា​សំណើ​មួយ​ណា​ត្រូវ​នឹង​ផ្នែក​ណា​នៃ​គម្រោង។ ហើយនៅក្នុងបំណែកទាំងនេះ យើងអាចស្វែងរកវត្ថុទាំងនោះដែល "អាចអានបាន" យ៉ាងងាយស្រួល និងដោយធម្មជាតិ។

តាមទស្សនៈនៃសំណួរ យើងមិនដឹងថាវាជាតារាង ឬ CTE ទេ ប៉ុន្តែពួកវាត្រូវបានកំណត់ដោយថ្នាំងដូចគ្នា RangeVar. ហើយនៅក្នុងលក្ខខណ្ឌនៃ "អាចអានបាន" នេះក៏ជាសំណុំថ្នាំងដែលមានកំណត់ផងដែរ៖

• Seq Scan on [tbl]
• Bitmap Heap Scan on [tbl]
• Index [Only] Scan [Backward] using [idx] on [tbl]
• CTE Scan on [cte]
• Insert/Update/Delete on [tbl]

យើងដឹងពីរចនាសម្ព័ន្ធនៃផែនការនិងសំណួរយើងដឹងពីការឆ្លើយឆ្លងនៃប្លុកយើងស្គាល់ឈ្មោះរបស់វត្ថុ - យើងធ្វើការប្រៀបធៀបមួយទៅមួយ។


ម្តងទៀត ភារកិច្ច "ជាមួយសញ្ញាផ្កាយ". យើងទទួលយកសំណើ ប្រតិបត្តិវា យើងមិនមានឈ្មោះក្លែងក្លាយទេ - យើងគ្រាន់តែអានវាពីរដងពី CTE ដូចគ្នា។


យើងក្រឡេកមើលផែនការ - តើមានបញ្ហាអ្វី? ហេតុអ្វីបានជាយើងមានឈ្មោះក្លែងក្លាយ? យើងមិនបានបញ្ជាទិញវាទេ។ តើគាត់ទទួលបាន "លេខ" បែបនេះនៅឯណា?

PostgreSQL បន្ថែមវាដោយខ្លួនឯង។ អ្នកគ្រាន់តែត្រូវយល់ពីវា។ គ្រាន់តែជាឈ្មោះហៅក្រៅបែបនេះ សម្រាប់ពួកយើង សម្រាប់គោលបំណងនៃការប្រៀបធៀបជាមួយនឹងផែនការ វាមិនមានន័យអ្វីនោះទេ វាត្រូវបានបន្ថែមយ៉ាងសាមញ្ញនៅទីនេះ។ ចូរយើងកុំយកចិត្តទុកដាក់ចំពោះគាត់។

ទីពីរ ភារកិច្ច "ជាមួយសញ្ញាផ្កាយ"៖ ប្រសិនបើយើងកំពុងអានពីតារាងបែងចែក នោះយើងនឹងទទួលបាន node Append ឬ Merge Appendដែលនឹងមាន "កុមារ" មួយចំនួនធំ ហើយពួកគេម្នាក់ៗនឹងមានលក្ខណៈដូចម្ដេច Scan'om ពីផ្នែកតារាង៖ Seq Scan, Bitmap Heap Scan ឬ Index Scan. ប៉ុន្តែក្នុងករណីណាក៏ដោយ "កុមារ" ទាំងនេះនឹងមិនមែនជាសំណួរស្មុគស្មាញ - នេះជារបៀបដែលថ្នាំងទាំងនេះអាចត្រូវបានសម្គាល់ពី Append នៅ UNION.


យើង​ក៏​យល់​ពី​ការ​ចង​ក្រង​បែប​នេះ​ដែរ ប្រមូល​វា “ក្នុង​មួយ​គំនរ” ហើយ​និយាយ៖ “អ្វីគ្រប់យ៉ាងដែលអ្នកបានអានពី megatable គឺនៅទីនេះ និងចុះក្រោមដើមឈើ".

ថ្នាំងទទួលទិន្នន័យ "សាមញ្ញ"

Values Scan ត្រូវគ្នានឹងផែនការ VALUES នៅក្នុងសំណើ។

Result គឺជាការស្នើសុំដោយគ្មាន FROM ចូលចិត្ត SELECT 1. ឬនៅពេលដែលអ្នកមានការបញ្ចេញមតិមិនពិតដោយចេតនានៅក្នុង WHERE- ប្លុក (បន្ទាប់មកគុណលក្ខណៈលេចឡើង One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan "ផែនទី" ទៅ SRFs នៃឈ្មោះដូចគ្នា។

ប៉ុន្តែជាមួយនឹងសំណួរដែលភ្ជាប់មកជាមួយ អ្វីគ្រប់យ៉ាងគឺកាន់តែស្មុគស្មាញ - ជាអកុសល ពួកគេមិនតែងតែប្រែទៅជា InitPlan/SubPlan. ពេលខ្លះពួកគេប្រែទៅជា ... Join ឬ ... Anti Joinជាពិសេសនៅពេលអ្នកសរសេរអ្វីមួយដូចជា WHERE NOT EXISTS .... ហើយនៅទីនេះវាមិនតែងតែអាចបញ្ចូលគ្នាពួកគេបានទេ - នៅក្នុងអត្ថបទនៃផែនការមិនមានប្រតិបត្តិករដែលត្រូវគ្នាទៅនឹងថ្នាំងនៃផែនការនោះទេ។

ម្តងទៀត ភារកិច្ច "ជាមួយសញ្ញាផ្កាយ"៖ ខ្លះ VALUES នៅក្នុងសំណើ។ ក្នុងករណីនេះនិងក្នុងផែនការអ្នកនឹងទទួលបានថ្នាំងជាច្រើន។ Values Scan.


បច្ច័យ "លេខ" នឹងជួយបែងចែកពួកវាពីគ្នាទៅវិញទៅមក - ពួកគេត្រូវបានបន្ថែមយ៉ាងពិតប្រាកដតាមលំដាប់ដែលរកឃើញដែលត្រូវគ្នា។ VALUES- ប្លុកតាមសំណើពីកំពូលទៅបាត។

ដំណើរការទិន្នន័យ

វាហាក់បីដូចជាអ្វីៗទាំងអស់នៅក្នុងសំណើរបស់យើងត្រូវបានតម្រៀបចេញហើយ អ្វីៗនៅសេសសល់គឺ Limit.


ប៉ុន្តែនៅទីនេះអ្វីគ្រប់យ៉ាងគឺសាមញ្ញ - ថ្នាំងដូចជា Limit, Sort, Aggregate, WindowAgg, Unique "ផែនទី" ពីមួយទៅមួយទៅកាន់ប្រតិបត្តិករដែលត្រូវគ្នានៅក្នុងសំណើ ប្រសិនបើពួកគេនៅទីនោះ។ មិនមាន "ផ្កាយ" ឬការលំបាកនៅទីនេះទេ។

ចូលរួម

ការលំបាកកើតឡើងនៅពេលដែលយើងចង់បញ្ចូលគ្នា JOIN រវាងពួកគេ។ នេះមិនតែងតែអាចទៅរួចនោះទេ ប៉ុន្តែវាអាចទៅរួច។


តាមទស្សនៈរបស់អ្នកញែកសំណួរ យើងមានថ្នាំង JoinExprដែលមានកូនពីរនាក់ពិតប្រាកដ - ឆ្វេងនិងស្តាំ។ នេះ​បើ​តាម​នោះ​គឺ​ជា​អ្វី​ដែល "ខាង​លើ" JOIN របស់​អ្នក និង​អ្វី​ដែល​ត្រូវ​បាន​សរសេរ "ខាងក្រោម" វា​នៅ​ក្នុង​សំណើ។

ហើយ​តាម​ទស្សនៈ​នៃ​ផែនការ​នេះ​គឺ​ជា​កូន​ចៅ​ពីរ​នាក់​របស់​អ្នក​ខ្លះ * Loop/* Join- ថ្នាំង។ Nested Loop, Hash Anti Join,... - អ្វីមួយ​ដូច​នោះ។

ចូរប្រើតក្កវិជ្ជាសាមញ្ញ៖ ប្រសិនបើយើងមានតារាង A និង B ដែល "ចូលរួម" គ្នាទៅវិញទៅមកនៅក្នុងផែនការនោះ តាមការស្នើសុំ ពួកគេអាចមានទីតាំងនៅ A-JOIN-B, ឬ B-JOIN-A. ចូរ​យើង​ព្យាយាម​ផ្សំ​វិធី​នេះ ចូរ​យើង​ព្យាយាម​ផ្សំ​វិធី​ផ្សេង​ទៀត​ហើយ​បន្ត​រហូត​ដល់​យើង​អស់​គូ​បែប​នេះ។

ចូរ​យក​មែកធាង​វាក្យសម្ព័ន្ធ​របស់​យើង យក​ផែនការ​របស់​យើង​មើល​វា... មិន​ដូច​គ្នា​ទេ!


តោះគូរវាឡើងវិញជាទម្រង់ក្រាហ្វ - អូ វាមើលទៅដូចជាអ្វីមួយហើយ!


ចូរកត់សម្គាល់ថាយើងមានថ្នាំងដែលក្នុងពេលដំណាលគ្នាមានកូន B និង C - យើងមិនខ្វល់ថានៅក្នុងលំដាប់អ្វីនោះទេ។ ចូរផ្សំពួកវា ហើយបង្វែររូបភាពថ្នាំងពីលើ។


តោះមើលម្តងទៀត។ ឥឡូវនេះយើងមានថ្នាំងដែលមានកូន A និងគូ (B + C) - ឆបគ្នាជាមួយពួកគេផងដែរ។


អស្ចារ្យ! វាប្រែថាយើងទាំងពីរនេះ។ JOIN ពីសំណើជាមួយថ្នាំងផែនការត្រូវបានបញ្ចូលគ្នាដោយជោគជ័យ។

Alas, បញ្ហានេះមិនតែងតែត្រូវបានដោះស្រាយទេ។


ឧទាហរណ៍ប្រសិនបើនៅក្នុងសំណើ A JOIN B JOIN Cហើយនៅក្នុងផែនការ ជាដំបូងថ្នាំង "ខាងក្រៅ" A និង C ត្រូវបានភ្ជាប់។ ប៉ុន្តែមិនមានប្រតិបត្តិករបែបនេះនៅក្នុងសំណើនោះទេ យើងគ្មានអ្វីដែលត្រូវរំលេច គ្មានអ្វីត្រូវភ្ជាប់ជាមួយព័ត៌មានជំនួយនោះទេ។ វាដូចគ្នានឹង "ក្បៀស" នៅពេលអ្នកសរសេរ A, B.

ប៉ុន្តែក្នុងករណីភាគច្រើន ស្ទើរតែគ្រប់ថ្នាំងទាំងអស់អាចត្រូវបាន "បិទភ្ជាប់" ហើយអ្នកអាចទទួលបានទម្រង់បែបបទនេះនៅខាងឆ្វេងទាន់ពេលវេលា - តាមន័យត្រង់ ដូចជានៅក្នុង Google Chrome នៅពេលអ្នកវិភាគកូដ JavaScript ជាដើម។ អ្នក​អាច​មើល​ឃើញ​ពី​រយៈពេល​ដែល​បន្ទាត់​នីមួយៗ និង​សេចក្តីថ្លែងការណ៍​នីមួយៗ​ត្រូវ​ចំណាយ​ពេល​ដើម្បី "ប្រតិបត្តិ"។


ហើយដើម្បីធ្វើឱ្យវាកាន់តែងាយស្រួលសម្រាប់អ្នកក្នុងការប្រើប្រាស់ទាំងអស់នេះ ពួកយើងបានបង្កើតកន្លែងផ្ទុក ប័ណ្ណសារដែលជាកន្លែងដែលអ្នកអាចរក្សាទុក ហើយក្រោយមកស្វែងរកគម្រោងរបស់អ្នក រួមជាមួយនឹងសំណើដែលពាក់ព័ន្ធ ឬចែករំលែកតំណជាមួយនរណាម្នាក់។

ប្រសិនបើអ្នកគ្រាន់តែត្រូវការនាំយកសំណួរដែលមិនអាចអានបានចូលទៅក្នុងទម្រង់គ្រប់គ្រាន់ សូមប្រើ "ឧបករណ៍ធម្មតា" របស់យើង.

ប្រភព: www.habr.com