PostgreSQL ક્વેરી પ્રોફાઇલર: પ્લાન અને ક્વેરીનો મેળ કેવી રીતે મેળવવો

ઘણા જેઓ પહેલેથી ઉપયોગ કરી રહ્યા છે explain.tensor.ru - અમારી PostgreSQL પ્લાન વિઝ્યુલાઇઝેશન સેવા કદાચ તેની એક મહાસત્તાથી વાકેફ ન હોય - સર્વર લોગના વાંચવા માટે મુશ્કેલ ભાગને ફેરવીને...

... અનુરૂપ પ્લાન નોડ્સ માટે સંદર્ભિત સંકેતો સાથે સુંદર રીતે ડિઝાઇન કરેલી ક્વેરી:

તેમના બીજા ભાગની આ પ્રતિલિપિમાં PGConf.Russia 2020 પર અહેવાલ હું તમને કહીશ કે અમે આ કેવી રીતે કર્યું.

સામાન્ય ક્વેરી કામગીરી સમસ્યાઓ અને તેમના ઉકેલોને સમર્પિત પ્રથમ ભાગની ટ્રાન્સક્રિપ્ટ, લેખમાં મળી શકે છે. "બીમાર SQL પ્રશ્નો માટેની વાનગીઓ".

પ્રથમ, ચાલો રંગ કરવાનું શરૂ કરીએ - અને અમે હવે યોજનાને રંગ આપીશું નહીં, અમે તેને પહેલેથી જ રંગીન કરી દીધું છે, અમારી પાસે પહેલેથી જ સુંદર અને સમજી શકાય તેવું છે, પરંતુ એક વિનંતી.

અમને એવું લાગતું હતું કે આવી અનફોર્મેટેડ “શીટ” સાથે લોગમાંથી ખેંચાયેલી વિનંતી ખૂબ જ નીચ અને તેથી અસુવિધાજનક લાગે છે.


ખાસ કરીને જ્યારે વિકાસકર્તાઓ કોડમાં વિનંતીના મુખ્ય ભાગને "ગુંદર" કરે છે (આ, અલબત્ત, એન્ટિપેટર્ન છે, પરંતુ તે થાય છે). ભયાનક!

ચાલો આને વધુ સુંદર રીતે દોરીએ.


અને જો આપણે આને સુંદર રીતે દોરી શકીએ, એટલે કે, વિનંતિના મુખ્ય ભાગને ડિસએસેમ્બલ અને પાછું એકસાથે મૂકી શકીએ, તો પછી અમે આ વિનંતીના દરેક ઑબ્જેક્ટ માટે સંકેત "જોડી" શકીએ છીએ - યોજનાના અનુરૂપ બિંદુ પર શું થયું.

ક્વેરી સિન્ટેક્સ ટ્રી

આ કરવા માટે, વિનંતીને પહેલા વિશ્લેષિત કરવી આવશ્યક છે.


કારણ કે અમારી પાસે છે સિસ્ટમનો મુખ્ય ભાગ નોડજેએસ પર ચાલે છે, પછી અમે તેના માટે એક મોડ્યુલ બનાવ્યું છે, તમે કરી શકો છો તેને GitHub પર શોધો. હકીકતમાં, આ પોસ્ટગ્રેએસક્યુએલ પાર્સરના આંતરિક ભાગોમાં વિસ્તૃત "બંધન" છે. એટલે કે, વ્યાકરણ ફક્ત દ્વિસંગી સંકલિત છે અને તેને નોડજેએસથી બાઈન્ડિંગ્સ બનાવવામાં આવે છે. અમે અન્ય લોકોના મોડ્યુલોને આધાર તરીકે લીધા - અહીં કોઈ મોટું રહસ્ય નથી.

અમે વિનંતીના મુખ્ય ભાગને અમારા કાર્યમાં ઇનપુટ તરીકે ફીડ કરીએ છીએ - આઉટપુટ પર અમને 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

જો આપણી પાસે ક્વેરી માં કીવર્ડ હોય 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 ધરાવે છે - અમે કયા ક્રમમાં કાળજી લેતા નથી. ચાલો તેમને ભેગા કરીએ અને નોડના ચિત્રને ફેરવીએ.


ચાલો ફરી જોઈએ. હવે અમારી પાસે બાળકો A અને જોડીઓ (B + C) સાથે નોડ્સ છે - તેમની સાથે પણ સુસંગત છે.


મહાન! તે તારણ આપે છે કે અમે આ બે છીએ JOIN પ્લાન નોડ્સ સાથે વિનંતીથી સફળતાપૂર્વક જોડવામાં આવ્યા હતા.

અરે, આ સમસ્યા હંમેશા હલ થતી નથી.


ઉદાહરણ તરીકે, જો વિનંતીમાં હોય A JOIN B JOIN C, અને યોજનામાં, સૌ પ્રથમ, "બાહ્ય" નોડ્સ A અને C જોડાયેલા હતા. પરંતુ વિનંતીમાં આવા કોઈ ઓપરેટર નથી, અમારી પાસે હાઇલાઇટ કરવા માટે કંઈ નથી, સંકેત જોડવા માટે કંઈ નથી. જ્યારે તમે લખો છો ત્યારે તે "અલ્પવિરામ" સાથે સમાન છે A, B.

પરંતુ, મોટાભાગના કિસ્સાઓમાં, લગભગ તમામ ગાંઠો "ખુલ્લી" હોઈ શકે છે અને તમે સમયસર ડાબી બાજુએ આ પ્રકારની પ્રોફાઇલિંગ મેળવી શકો છો - શાબ્દિક રીતે, જેમ કે Google Chrome માં જ્યારે તમે JavaScript કોડનું વિશ્લેષણ કરો છો. તમે જોઈ શકો છો કે દરેક લાઇન અને દરેક સ્ટેટમેન્ટને "એક્ઝીક્યુટ" કરવામાં કેટલો સમય લાગ્યો.


અને આ બધાનો ઉપયોગ તમારા માટે વધુ અનુકૂળ બનાવવા માટે, અમે સ્ટોરેજ બનાવ્યું છે આર્કાઇવ, જ્યાં તમે સાચવી શકો છો અને પછીથી સંબંધિત વિનંતીઓ સાથે તમારી યોજનાઓ શોધી શકો છો અથવા કોઈની સાથે લિંક શેર કરી શકો છો.

જો તમારે ફક્ત વાંચી ન શકાય તેવી ક્વેરી પર્યાપ્ત સ્વરૂપમાં લાવવાની જરૂર હોય, તો ઉપયોગ કરો અમારા "સામાન્ય".

સોર્સ: www.habr.com