PostgreSQL መጠይቅ መገለጫ፡ እቅድ እና መጠይቅን እንዴት ማዛመድ እንደሚቻል

አስቀድመው እየተጠቀሙ ያሉት ብዙዎች አብራራ.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- መስቀለኛ መንገድ እና ለሌላው - 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- አግድ (ከዚያም ባህሪው ይታያል 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

መቀላቀል ስንፈልግ ችግሮች ይከሰታሉ JOIN በራሳቸው መካከል. ይህ ሁልጊዜ የሚቻል አይደለም, ግን ይቻላል.


ከጠያቂው ተንታኝ እይታ፣ መስቀለኛ መንገድ አለን። JoinExprበትክክል ሁለት ልጆች ያሉት - ግራ እና ቀኝ. ይህ፣ በዚህ መሰረት፣ የእርስዎ መቀላቀል “ከላይ” ያለው እና በጥያቄው ውስጥ “ከታች” ተብሎ የተጻፈው ነው።

እና ከእቅዱ እይታ አንጻር እነዚህ ሁለት የአንዳንድ ዘሮች ናቸው * Loop/* Join- መስቀለኛ መንገድ. Nested Loop, Hash Anti Join,... - እንደ 'ዛ ያለ ነገር.

ቀላል አመክንዮዎችን እንጠቀም፡ በእቅዱ ውስጥ “የሚጣመሩ” ሰንጠረዦች A እና B ካሉን በጥያቄው ውስጥ ሁለቱም ሊገኙ ይችላሉ A-JOIN-B, ወይም B-JOIN-A. በዚህ መንገድ ለማጣመር እንሞክር፣ በሌላ በኩል ደግሞ ለማጣመር እንሞክር፣ እና የመሳሰሉትን ጥንዶች እስክንጨርስ ድረስ።

የአገባብ ዛፋችንን እንውሰድ፣ እቅዳችንን እንይ፣ እንያቸው... አይመሳሰልም!


በግራፍ መልክ እንቀይረው - ኦህ ፣ ቀድሞውኑ የሆነ ነገር ይመስላል!


በአንድ ጊዜ ልጆች ቢ እና ሲ ያላቸው አንጓዎች እንዳሉን እናስተውል - በምን ቅደም ተከተል ግድ የለንም። እነሱን እናጣምር እና የመስቀለኛ መንገዱን ምስል ወደ ላይ እናዞር.


እንደገና እንይ። አሁን ከልጆች A እና ጥንዶች (B + C) ጋር - ከነሱ ጋር የሚስማማ ኖዶች አሉን።


በጣም ጥሩ! እኛ እነዚህ ሁለት መሆናችንን ያሳያል JOIN ከጥያቄው እቅድ አንጓዎች በተሳካ ሁኔታ ተጣምረዋል.

ወዮ, ይህ ችግር ሁልጊዜ አይፈታም.


ለምሳሌ, በጥያቄ ውስጥ ከሆነ A JOIN B JOIN C, እና በእቅዱ ውስጥ, በመጀመሪያ, "ውጫዊ" አንጓዎች A እና C ተገናኝተዋል. ነገር ግን በጥያቄው ውስጥ እንደዚህ አይነት ኦፕሬተር የለም, ምንም የሚያጎላ ነገር የለንም, ምንም ፍንጭ አያይዘውም. በሚጽፉበት ጊዜ "ኮማ" ጋር ተመሳሳይ ነው A, B.

ግን በአብዛኛዎቹ አጋጣሚዎች ሁሉም መስቀለኛ መንገዶች “የተፈቱ” ሊሆኑ ይችላሉ እና እንደዚህ ዓይነቱን መገለጫ በጊዜ በግራ በኩል ማግኘት ይችላሉ - በጥሬው ፣ ልክ እንደ ጃቫ ስክሪፕት ኮድ ሲተነትኑ በ Google Chrome ውስጥ። እያንዳንዱ መስመር እና እያንዳንዱ መግለጫ “ለመፈፀም” ምን ያህል ጊዜ እንደወሰደ ማየት ትችላለህ።


እና ይህን ሁሉ ለመጠቀም ለእርስዎ የበለጠ አመቺ እንዲሆን, ማከማቻ አድርገናል መዝገብ, ማስቀመጥ የሚችሉበት እና በኋላ እቅዶችዎን ከተዛማጅ ጥያቄዎች ጋር ያግኙ ወይም አገናኙን ለአንድ ሰው ያጋሩ።

የማይነበብ መጠይቅ ወደ በቂ ቅጽ ብቻ ማምጣት ከፈለጉ ይጠቀሙ የእኛ "መደበኛ".

ምንጭ: hab.com