🥇PostgreSQL வினவல் விவரக்குறிப்பு: திட்டம் மற்றும் வினவலை எவ்வாறு பொருத்துவது

பலர் ஏற்கனவே பயன்படுத்தி வருகின்றனர் விளக்கவும்.tensor.ru - எங்கள் PostgreSQL திட்ட காட்சிப்படுத்தல் சேவையானது அதன் வல்லரசுகளில் ஒன்றைப் பற்றி அறிந்திருக்காமல் இருக்கலாம் - சர்வர் பதிவின் கடினமாக படிக்கக்கூடிய பகுதியை மாற்றுகிறது...

... தொடர்புடைய திட்ட முனைகளுக்கான சூழல் குறிப்புகளுடன் அழகாக வடிவமைக்கப்பட்ட வினவலில்:

அவரது இரண்டாம் பாகத்தின் இந்தப் பிரதியில் PGConf.Russia 2020 இல் அறிக்கை இதை எப்படிச் செய்தோம் என்பதை நான் உங்களுக்குச் சொல்கிறேன்.

வழக்கமான வினவல் செயல்திறன் சிக்கல்கள் மற்றும் அவற்றின் தீர்வுகளுக்கு அர்ப்பணிக்கப்பட்ட முதல் பகுதியின் டிரான்ஸ்கிரிப்டை கட்டுரையில் காணலாம் "நோய்வாய்ப்பட்ட SQL வினவல்களுக்கான சமையல் குறிப்புகள்".

முதலில், வண்ணமயமாக்கத் தொடங்குவோம் - நாங்கள் இனி திட்டத்தை வண்ணமயமாக்க மாட்டோம், நாங்கள் ஏற்கனவே அதை வண்ணமயமாக்கியுள்ளோம், எங்களிடம் ஏற்கனவே அழகாகவும் புரிந்துகொள்ளக்கூடியதாகவும் உள்ளது, ஆனால் ஒரு கோரிக்கை.

அத்தகைய வடிவமைக்கப்படாத "தாள்" மூலம் பதிவிலிருந்து இழுக்கப்பட்ட கோரிக்கை மிகவும் அசிங்கமாகவும், அதனால் சிரமமாகவும் இருப்பதாக எங்களுக்குத் தோன்றியது.

குறிப்பாக டெவலப்பர்கள் கோரிக்கையின் உடலை குறியீட்டில் "ஒட்டு" (இது, நிச்சயமாக, ஒரு எதிர்முறை, ஆனால் அது நடக்கும்) ஒரு வரியில். பயங்கரமான!

இதை எப்படியாவது அழகாக வரைவோம்.

இதை அழகாக வரைய முடிந்தால், அதாவது, கோரிக்கையின் உடலைப் பிரித்து மீண்டும் ஒன்றாக இணைக்க முடிந்தால், இந்த கோரிக்கையின் ஒவ்வொரு பொருளுக்கும் ஒரு குறிப்பை "இணைக்கலாம்" - திட்டத்தின் தொடர்புடைய கட்டத்தில் என்ன நடந்தது.

வினவல் தொடரியல் மரம்

இதைச் செய்ய, கோரிக்கை முதலில் பாகுபடுத்தப்பட வேண்டும்.

ஏனென்றால் நம்மிடம் உள்ளது கணினியின் மையமானது NodeJS இல் இயங்குகிறது, அதற்குப் பிறகு நாங்கள் ஒரு தொகுதியை உருவாக்கினோம், உங்களால் முடியும் GitHub இல் கண்டுபிடிக்கவும். உண்மையில், இவை PostgreSQL பாகுபடுத்தியின் உட்புறங்களுக்கு நீட்டிக்கப்பட்ட “பிணைப்புகள்” ஆகும். அதாவது, இலக்கணம் வெறுமனே பைனரி தொகுக்கப்பட்டுள்ளது மற்றும் NodeJS இலிருந்து பிணைப்புகள் செய்யப்படுகின்றன. நாங்கள் மற்றவர்களின் தொகுதிகளை ஒரு அடிப்படையாக எடுத்துக் கொண்டோம் - இங்கே பெரிய ரகசியம் எதுவும் இல்லை.

எங்கள் செயல்பாட்டிற்கான உள்ளீடாக கோரிக்கையின் உடலை நாங்கள் ஊட்டுகிறோம் - வெளியீட்டில் நாம் ஒரு JSON பொருளின் வடிவத்தில் ஒரு பாகுபடுத்தப்பட்ட தொடரியல் மரத்தைப் பெறுகிறோம்.

இப்போது நாம் இந்த மரத்தின் வழியாக எதிர் திசையில் ஓடி, நாம் விரும்பும் உள்தள்ளல்கள், வண்ணங்கள் மற்றும் வடிவமைத்தல் ஆகியவற்றுடன் ஒரு கோரிக்கையை இணைக்கலாம். இல்லை, இது தனிப்பயனாக்கக்கூடியது அல்ல, ஆனால் இது வசதியாக இருக்கும் என்று எங்களுக்குத் தோன்றியது.

மேப்பிங் வினவல் மற்றும் திட்ட முனைகள்

இப்போது நாம் முதல் படியில் பகுப்பாய்வு செய்த திட்டத்தையும், இரண்டாவது கட்டத்தில் பகுப்பாய்வு செய்த வினவலையும் எவ்வாறு இணைப்பது என்று பார்ப்போம்.

ஒரு எளிய உதாரணத்தை எடுத்துக் கொள்வோம் - CTE ஐ உருவாக்கி அதிலிருந்து இரண்டு முறை படிக்கும் வினவல் எங்களிடம் உள்ளது. அவர் அத்தகைய திட்டத்தை உருவாக்குகிறார்.

CTE

நீங்கள் அதை கவனமாகப் பார்த்தால், பதிப்பு 12 வரை (அல்லது அதிலிருந்து தொடங்கும் முக்கிய சொல்லுடன் MATERIALIZED) உருவாக்கம் CTE என்பது திட்டமிடுபவருக்கு ஒரு முழுமையான தடையாகும்.

அதாவது, கோரிக்கையில் எங்காவது CTE தலைமுறையையும், திட்டத்தில் எங்காவது ஒரு முனையையும் பார்த்தால் CTE, பின்னர் இந்த முனைகள் நிச்சயமாக ஒருவருக்கொருவர் "சண்டை", நாம் உடனடியாக அவற்றை இணைக்க முடியும்.

ஒரு நட்சத்திரத்தில் சிக்கல்: CTEகள் கூடு கட்டப்படலாம்.

மிகவும் மோசமாக உள்ளமைக்கப்பட்டவை மற்றும் அதே பெயரில் உள்ளவை கூட உள்ளன. உதாரணமாக, நீங்கள் உள்ளே செய்யலாம் 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-நோட் மற்றும் மற்றொன்றுக்கு - 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 அதையே சேர்க்கிறது. அதை நீங்கள் புரிந்து கொள்ள வேண்டும் அத்தகைய மாற்றுப்பெயர் எங்களுக்கு, திட்டத்துடன் ஒப்பிடும் நோக்கங்களுக்காக, அது எந்த அர்த்தமும் இல்லை, அது இங்கே சேர்க்கப்பட்டுள்ளது. நாம் அவரை கவனிக்க வேண்டாம்.

இரண்டாவது பணி "நட்சத்திரத்துடன்": நாம் ஒரு பகிர்வு அட்டவணையில் இருந்து படிக்கிறோம் என்றால், நமக்கு ஒரு முனை கிடைக்கும் Append அல்லது Merge Append, இது ஒரு பெரிய எண்ணிக்கையிலான "குழந்தைகள்" கொண்டிருக்கும், மேலும் அவை ஒவ்வொன்றும் எப்படியோ இருக்கும் Scanஅட்டவணைப் பிரிவில் இருந்து 'ஓம்: Seq Scan, Bitmap Heap Scan அல்லது Index Scan. ஆனால், எப்படியிருந்தாலும், இந்த “குழந்தைகள்” சிக்கலான கேள்விகளாக இருக்காது - இந்த முனைகளை இப்படித்தான் வேறுபடுத்தி அறியலாம். Append மணிக்கு UNION.

அத்தகைய முடிச்சுகளையும் நாங்கள் புரிந்துகொள்கிறோம், அவற்றை "ஒரு குவியலில்" சேகரித்து கூறுகிறோம்: "மெகாட்டபில் இருந்து நீங்கள் படிக்கும் அனைத்தும் இங்கே மற்றும் மரத்தின் கீழே உள்ளன".

"எளிய" தரவு பெறும் முனைகள்

Values Scan திட்டத்தில் ஒத்துள்ளது VALUES கோரிக்கையில்.

Result இல்லாத கோரிக்கையாகும் FROM வகையான SELECT 1. அல்லது உங்களிடம் வேண்டுமென்றே தவறான வெளிப்பாடு இருக்கும்போது WHERE-block (பின்னர் பண்புக்கூறு தோன்றும் 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 அதே பெயரின் SRF களுக்கு "வரைபடம்".

ஆனால் உள்ளமை வினவல்களுடன் எல்லாம் மிகவும் சிக்கலானது - துரதிர்ஷ்டவசமாக, அவை எப்போதும் மாறாது InitPlan/SubPlan. சில நேரங்களில் அவை மாறிவிடும் ... Join அல்லது ... Anti Join, குறிப்பாக நீங்கள் ஏதாவது எழுதும்போது WHERE NOT EXISTS .... இங்கே அவற்றை இணைப்பது எப்போதும் சாத்தியமில்லை - திட்டத்தின் உரையில் திட்டத்தின் முனைகளுடன் தொடர்புடைய ஆபரேட்டர்கள் இல்லை.

மீண்டும் பணி "நட்சத்திரத்துடன்": சில VALUES கோரிக்கையில். இந்த வழக்கில் மற்றும் திட்டத்தில் நீங்கள் பல முனைகளைப் பெறுவீர்கள் Values Scan.

"எண்ணிடப்பட்ட" பின்னொட்டுகள் அவற்றை ஒன்றிலிருந்து வேறுபடுத்த உதவும் - அவை தொடர்புடையவை காணப்படும் வரிசையில் சரியாக சேர்க்கப்படுகின்றன. VALUES- கோரிக்கையை மேலிருந்து கீழாகத் தடுக்கிறது.

தகவல் செயல்முறை

எங்கள் கோரிக்கையில் உள்ள அனைத்தும் வரிசைப்படுத்தப்பட்டதாகத் தெரிகிறது - மீதமுள்ளவை Limit.

ஆனால் இங்கே எல்லாம் எளிது - போன்ற முனைகள் Limit, Sort, Aggregate, WindowAgg, Unique கோரிக்கையில் தொடர்புடைய ஆபரேட்டர்கள் இருந்தால் "வரைபடம்" ஒன்றுக்கு ஒன்று. இங்கே "நட்சத்திரங்கள்" அல்லது சிரமங்கள் இல்லை.

சேர்க

நாம் ஒன்றிணைக்க விரும்பும்போது சிரமங்கள் எழுகின்றன JOIN தங்களுக்கு இடையே. இது எப்போதும் சாத்தியமில்லை, ஆனால் அது சாத்தியமாகும்.

வினவல் பாகுபடுத்தியின் பார்வையில், எங்களிடம் ஒரு முனை உள்ளது JoinExpr, இதில் சரியாக இரண்டு குழந்தைகள் உள்ளனர் - இடது மற்றும் வலது. இது, அதன்படி, உங்கள் சேர்வின் "மேலே" மற்றும் கோரிக்கையில் "கீழே" எழுதப்பட்டவை.

திட்டத்தின் பார்வையில், இவை சிலரின் இரண்டு சந்ததியினர் * Loop/* Join- முனை. Nested Loop, Hash Anti Join,... - அந்த மாதிரி ஏதாவது.

எளிமையான தர்க்கத்தைப் பயன்படுத்துவோம்: திட்டத்தில் ஒருவருக்கொருவர் "சேர்க்கும்" அட்டவணைகள் A மற்றும் B இருந்தால், கோரிக்கையில் அவை அமைந்துள்ளன. A-JOIN-B, அல்லது B-JOIN-A. இந்த வழியில் இணைக்க முயற்சிப்போம், வேறு வழியில் இணைக்க முயற்சிப்போம், அத்தகைய ஜோடிகளை நாம் ரன் அவுட் செய்யும் வரை.

நம் தொடரியல் மரத்தை எடுத்துக்கொள்வோம், எங்கள் திட்டத்தை எடுத்துக்கொள்வோம், அவற்றைப் பாருங்கள்... ஒத்ததாக இல்லை!

அதை வரைபட வடிவில் மீண்டும் வரைவோம் - ஓ, இது ஏற்கனவே ஏதோ போல் தெரிகிறது!

நாம் ஒரே நேரத்தில் B மற்றும் C குழந்தைகளைக் கொண்டிருக்கும் முனைகளைக் கொண்டிருப்பதைக் கவனியுங்கள் - எந்த வரிசையில் நாங்கள் கவலைப்படுவதில்லை. அவற்றை இணைத்து முனையின் படத்தைப் புரட்டுவோம்.

மீண்டும் பார்ப்போம். இப்போது எங்களிடம் குழந்தைகள் ஏ மற்றும் ஜோடிகளுடன் (பி + சி) முனைகள் உள்ளன - அவற்றுடனும் இணக்கமானது.

நன்று! நாம் இந்த இருவரும் என்று மாறிவிடும் JOIN திட்ட முனைகளுடன் கோரிக்கை வெற்றிகரமாக இணைக்கப்பட்டது.

ஐயோ, இந்த பிரச்சனை எப்போதும் தீர்க்கப்படாது.

உதாரணமாக, ஒரு கோரிக்கையில் இருந்தால் A JOIN B JOIN C, மற்றும் திட்டத்தில், முதலில், "வெளிப்புற" முனைகள் A மற்றும் C இணைக்கப்பட்டன. ஆனால் கோரிக்கையில் அத்தகைய ஆபரேட்டர் இல்லை, எங்களிடம் முன்னிலைப்படுத்த எதுவும் இல்லை, குறிப்பை இணைக்க எதுவும் இல்லை. நீங்கள் எழுதும்போது "கமா" என்பதும் அப்படித்தான் A, B.

ஆனால், பெரும்பாலான சந்தர்ப்பங்களில், கிட்டத்தட்ட எல்லா முனைகளையும் "அவிழ்க்க" முடியும் மற்றும் நீங்கள் சரியான நேரத்தில் இடதுபுறத்தில் இந்த வகையான சுயவிவரத்தைப் பெறலாம் - அதாவது, நீங்கள் ஜாவாஸ்கிரிப்ட் குறியீட்டை பகுப்பாய்வு செய்யும் போது Google Chrome இல் உள்ளது. ஒவ்வொரு வரியும் ஒவ்வொரு அறிக்கையும் "செயல்படுத்த" எவ்வளவு நேரம் எடுத்தது என்பதை நீங்கள் பார்க்கலாம்.

இதையெல்லாம் பயன்படுத்த உங்களுக்கு வசதியாக, நாங்கள் சேமிப்பகத்தை உருவாக்கியுள்ளோம் காப்பகம், நீங்கள் சேமித்து பின்னர் தொடர்புடைய கோரிக்கைகளுடன் உங்கள் திட்டங்களைக் கண்டறியலாம் அல்லது ஒருவருடன் இணைப்பைப் பகிரலாம்.

நீங்கள் படிக்க முடியாத வினவலை போதுமான வடிவத்தில் கொண்டு வர வேண்டும் என்றால், பயன்படுத்தவும் எங்கள் "இயல்பாக்கி".

ஆதாரம்: www.habr.com

PostgreSQL வினவல் விவரக்குறிப்பு: திட்டம் மற்றும் வினவலை எவ்வாறு பொருத்துவது