તમને યાદ છે કે આ બધું કેવી રીતે શરૂ થયું. બધું પ્રથમ વખત અને ફરીથી હતું

પોસ્ટગ્રેએસક્યુએલ ક્વેરી ઓપ્ટિમાઇઝેશન સાથે મારે કેવી રીતે વ્યવહાર કરવો પડ્યો અને આ બધામાંથી શું બહાર આવ્યું તે વિશે.
તમારે કેમ કરવું પડ્યું? હા, કારણ કે પાછલા 4 વર્ષોમાં બધું શાંતિથી, શાંતિથી કામ કરતું હતું, જેમ કે ઘડિયાળ ટિક કરી રહી હતી.
એપિગ્રાફ તરીકે.

વાસ્તવિક ઘટનાઓ પર આધારિત.
બધા નામો બદલાઈ ગયા છે, સંયોગો રેન્ડમ છે.

જ્યારે કોઈ ચોક્કસ પરિણામ પ્રાપ્ત થાય છે, ત્યારે તે યાદ રાખવું હંમેશા રસપ્રદ છે કે શરૂઆત માટે શું પ્રોત્સાહન હતું, તે બધું કેવી રીતે શરૂ થયું.

તેથી, પરિણામે જે બન્યું તે લેખમાં ટૂંકમાં વર્ણવેલ છે “PostgreSQL પ્રદર્શનને સુધારવા માટેની પદ્ધતિઓમાંની એક તરીકે સિન્થેસિસ».

અગાઉની ઇવેન્ટ્સની સાંકળને ફરીથી બનાવવી કદાચ રસપ્રદ રહેશે.
ઇતિહાસે ચોક્કસ શરૂઆતની તારીખ રાખી છે — 2018-09-10 18:02:48.
ઉપરાંત, વાર્તામાં એક વિનંતી છે કે જ્યાંથી તે બધું શરૂ થયું:
સમસ્યા વિનંતીપસંદ કરો
p. "PARAMETER_ID" parameter_id તરીકે,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" ગ્રાહક_ભાગી નંબર તરીકે,
w."LRM" AS LRM,
w. "LOTID" as lotid,
w. "RTD_VALUE" AS RTD_મૂલ્ય,
w. "LOWER_SPEC_LIMIT" as low_spec_limit,
w. "UPPER_SPEC_LIMIT" as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
અર્ક ("SPENT_DATE" થી વર્ષ) AS વર્ષ,
અર્ક ("SPENT_DATE" થી મહિનો) મહિના તરીકે,
s."REPORT_NAME" AS રિપોર્ટ_નામ,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" as customerparam_name
wdataw થી,
ખર્ચ્યા
pmtrp,
spent_pdsp,
pd pd
ક્યાં s."SPENT_ID" = w."SPENT_ID"
અને p."PARAMETER_ID" = w."PARAMETER_ID"
અને s."SPENT_ID" = sp."SPENT_ID"
અને pd. "PD_ID" = sp. "PD_ID"
અને s."SPENT_DATE" >= '2018-07-01' અને સે."SPENT_DATE" <= '2018-09-30'
અને s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
s2 ખર્ચવામાંથી,
wdata w2
જ્યાં s2."SPENT_ID" = w2."SPENT_ID"
અને w2."LRM" = w."LRM");

સમસ્યાનું વર્ણન, અનુમાનિત ધોરણ - “બધું ખરાબ છે. મને કહો કે સમસ્યા શું છે."
મને તરત જ સાડા 3 ઇંચની ડ્રાઇવના સમયથી એક મજાક યાદ આવી:

લેમર હેકર પાસે આવે છે.
- મારા માટે કંઈ કામ કરતું નથી, મને કહો કે સમસ્યા ક્યાં છે.
- DNA માં...

પરંતુ, અલબત્ત, પ્રદર્શનની ઘટનાઓને હલ કરવાનો આ રસ્તો નથી. "આપણને કદાચ સમજાય નહીં"(સાથે). તે આકૃતિ કરવાની જરૂર છે.
સારું, ચાલો ખોદવું. કદાચ તે પરિણામે એકઠા થશે.

રોકાણ શરૂ કર્યું

તેથી, EXPLAIN નો આશરો લીધા વિના, નરી આંખે તરત જ શું જોઈ શકાય છે.
1) JOIN નો ઉપયોગ થતો નથી. આ ખરાબ છે, ખાસ કરીને જો જોડાણોની સંખ્યા એક કરતા વધુ હોય.
2) પરંતુ તેનાથી પણ ખરાબ શું છે - એક સહસંબંધિત સબક્વેરી, વધુમાં, એકત્રીકરણ સાથે. આ બહુ ખરાબ છે.
આ અલબત્ત ખરાબ છે. પરંતુ આ માત્ર એક તરફ છે. બીજી બાજુ, આ ખૂબ જ સારું છે, કારણ કે સમસ્યાનો સ્પષ્ટ ઉકેલ છે અને વિનંતીને સુધારી શકાય છે.
નસીબદાર (C) પાસે ન જશો.
પ્રશ્ન યોજના એટલી જટિલ નથી, પરંતુ તદ્દન સૂચક છે:
એક્ઝેક્યુશન પ્લાન

સૌથી રસપ્રદ અને ઉપયોગી, હંમેશની જેમ, શરૂઆતમાં અને અંતે.
નેસ્ટેડ લૂપ (કિંમત=935.84..479763226.18 પંક્તિઓ=3322 પહોળાઈ=135) (વાસ્તવિક સમય=31.536..8220420.295 પંક્તિઓ=8111656 લૂપ્સ=1)
આયોજન સમય: 3.807ms
એક્ઝેક્યુશન સમય: 8222351.640ms
અમલનો સમય 2 કલાકથી વધુ છે.

ખોટી પૂર્વધારણાઓ જેમાં સમય લાગ્યો

પૂર્વધારણા 1- ઑપ્ટિમાઇઝર ખોટું છે, ખોટી યોજના બનાવે છે.

એક્ઝેક્યુશન પ્લાનની કલ્પના કરવા માટે, અમે સાઇટનો ઉપયોગ કરીશું https://explain.depesz.com/. જો કે, સાઇટે કંઈપણ રસપ્રદ અથવા ઉપયોગી દર્શાવ્યું નથી. પ્રથમ અને બીજી નજરમાં - ખરેખર મદદ કરી શકે તેવું કંઈ નથી. જ્યાં સુધી - પૂર્ણ સ્કેન ન્યૂનતમ છે. આગળ વધો.

પૂર્વધારણા 2-ઓટોવેક્યુમની બાજુથી આધાર પર અસર, તમારે બ્રેક્સથી છુટકારો મેળવવાની જરૂર છે.

પરંતુ, ઓટોવેક્યુમ ડિમન સારી રીતે વર્તે છે, ત્યાં કોઈ લાંબી લટકતી પ્રક્રિયાઓ નથી. કોઈપણ ગંભીર ભાર - ના. બીજું કંઈક જોવાની જરૂર છે.

પૂર્વધારણા 3-આંકડા જૂના છે, તમારે દરેક વસ્તુની પુનઃ ગણતરી કરવાની જરૂર છે

ફરીથી, તે નથી. આંકડા અદ્યતન છે. જે, ઓટોવેક્યુમ સાથે સમસ્યાઓના અભાવને જોતાં, આશ્ચર્યજનક નથી.

ઑપ્ટિમાઇઝ કરવાનું શરૂ કરી રહ્યાં છીએ

મુખ્ય ટેબલ 'wdata' ચોક્કસપણે નાનું નથી, લગભગ 3 મિલિયન રેકોર્ડ્સ છે.
અને તે આ ટેબલ પર છે કે સંપૂર્ણ સ્કેન જાય છે.

હેશ કોન્ન્ડ: ((w."SPENT_ID" = s."SPENT_ID") અને ((સબપ્લાન 1) = s."SPENT_DATE"))
-> સેક સ્કેન wdata w પર (cost=0.00..574151.49 પંક્તિઓ=26886249 પહોળાઈ=46) (વાસ્તવિક સમય=0.005..8153.565 પંક્તિઓ=26873950 લૂપ્સ=1)
અમે માનક તરીકે કાર્ય કરીએ છીએ: "ચાલો એક અનુક્રમણિકા બનાવીએ અને બધું ઉડે છે".
ફીલ્ડ "SPENT_ID" પર અનુક્રમણિકા બનાવી
પરિણામ સ્વરૂપ:
ઇન્ડેક્સનો ઉપયોગ કરીને ક્વેરી એક્ઝેક્યુશન પ્લાન

સારું, તે મદદ કરી હતી?
હતી: 8 222 351.640 ms (ફક્ત 2 કલાકથી વધુ)
તે બન્યું: 6 985 431.575 ms (લગભગ 2 કલાક)
સામાન્ય રીતે, સમાન સફરજન, બાજુ દૃશ્ય.
ચાલો ક્લાસિક યાદ કરીએ:
“શું તમારી પાસે એક જ છે, પણ પાંખો વિના? શોધશે".

સૈદ્ધાંતિક રીતે, આને સારું પરિણામ કહી શકાય, સારું, સારું નહીં, પરંતુ સ્વીકાર્ય. ઓછામાં ઓછું, કેટલું કરવામાં આવ્યું છે અને શા માટે કરવામાં આવ્યું છે તે સારું છે તેનું વર્ણન કરતા ગ્રાહકને મોટો અહેવાલ આપો.
જોકે, અંતિમ નિર્ણય હજુ દૂર છે. બહુ દૂર.

અને હવે સૌથી રસપ્રદ બાબત - અમે ઑપ્ટિમાઇઝ કરવાનું ચાલુ રાખીએ છીએ, અમે ક્વેરી પોલિશ કરીશું

પહેલું પગલું - JOIN નો ઉપયોગ કરો

ફરીથી લખેલી ક્વેરી, હવે આના જેવી દેખાય છે (સારું ઓછામાં ઓછું સુંદર):
JOIN નો ઉપયોગ કરીને ક્વેરી કરોપસંદ કરો
p. "PARAMETER_ID" parameter_id તરીકે,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" ગ્રાહક_ભાગી નંબર તરીકે,
w."LRM" AS LRM,
w. "LOTID" as lotid,
w. "RTD_VALUE" AS RTD_મૂલ્ય,
w. "LOWER_SPEC_LIMIT" as low_spec_limit,
w. "UPPER_SPEC_LIMIT" as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
અર્ક ("SPENT_DATE" થી વર્ષ) AS વર્ષ,
અર્ક ("SPENT_DATE" થી મહિનો) મહિના તરીકે,
s."REPORT_NAME" AS રિપોર્ટ_નામ,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" as customerparam_name
ડબ્લ્યુડેટામાંથી આંતરિક જોડાવા માટે ખર્ચવામાં આવ્યો w."SPENT_ID"=s."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
આંતરિક જોડાઓ spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
આંતરિક જોડાઓ pd pd ON pd."PD_ID" = sp."PD_ID"
ક્યાં છે
s."SPENT_DATE" >= '2018-07-01' અને સે."SPENT_DATE" <= '2018-09-30'અને
s."SPENT_DATE" = (MAX(s2."SPENT_DATE") પસંદ કરો
w2 અંદરની જોડાવા માટે w2 પર s2 ખર્ચ્યા wdata માંથી."SPENT_ID"=s2."SPENT_ID"
ઇનર જોઇન wdata w
ON w2."LRM" = w."LRM" );
આયોજન સમય: 2.486ms
એક્ઝેક્યુશન સમય: 1223680.326ms

તો અહીં પ્રથમ પરિણામ છે.
હતી: 6 985 431.575 ms (લગભગ 2 કલાક).
તે બન્યું: 1 223 680.326 ms (માત્ર 20 મિનિટથી વધુ).
સારું પરિણામ. સૈદ્ધાંતિક રીતે, ફરીથી, ત્યાં રોકવું શક્ય બનશે. પરંતુ તેથી રસહીન, તમે રોકી શકતા નથી.
માટે

પગલું બે - સહસંબંધિત સબક્વેરીમાંથી છુટકારો મેળવો

બદલાયેલ વિનંતી ટેક્સ્ટ:
કોઈ સહસંબંધિત સબક્વેરી નથીપસંદ કરો
p. "PARAMETER_ID" parameter_id તરીકે,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" ગ્રાહક_ભાગી નંબર તરીકે,
w."LRM" AS LRM,
w. "LOTID" as lotid,
w. "RTD_VALUE" AS RTD_મૂલ્ય,
w. "LOWER_SPEC_LIMIT" as low_spec_limit,
w. "UPPER_SPEC_LIMIT" as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
અર્ક ("SPENT_DATE" થી વર્ષ) AS વર્ષ,
અર્ક ("SPENT_DATE" થી મહિનો) મહિના તરીકે,
s."REPORT_NAME" AS રિપોર્ટ_નામ,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" as customerparam_name
ડબલ્યુડેટામાંથી આંતરિક જોડાવા માટે s પર ખર્ચવામાં આવ્યો."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
આંતરિક જોડાઓ spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
આંતરિક જોડાઓ pd pd ON pd."PD_ID" = sp."PD_ID"
આંતરિક જોડાઓ (પસંદ કરો w2."LRM", MAX(s2."SPENT_DATE")
s2 INNER જોઇન wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
W2.LRM દ્વારા જૂથ
) md on w. "LRM" = md. "LRM"
ક્યાં છે
s."SPENT_DATE" >= '2018-07-01' અને s."SPENT_DATE" <= '2018-09-30';
આયોજન સમય: 2.291ms
એક્ઝેક્યુશન સમય: 165021.870ms

હતી: 1 223 680.326 ms (માત્ર 20 મિનિટથી વધુ).
તે બન્યું: 165 021.870 ms (ફક્ત 2 મિનિટથી વધુ).
આ પહેલેથી જ ઘણું સારું છે.
જો કે, અંગ્રેજી કહે છે તેમ,પરંતુ, ત્યાં હંમેશા એક પરંતુ છે" ખૂબ સારું પરિણામ આપોઆપ શંકા જગાવશે. અહીં કંઈક ખોટું છે.

સહસંબંધિત સબક્વેરીમાંથી છુટકારો મેળવવા માટે ક્વેરી સુધારવા વિશેની પૂર્વધારણા સાચી છે. પરંતુ અંતિમ પરિણામને યોગ્ય રીતે મેળવવા માટે તેને થોડો ફેરફાર કરવાની જરૂર છે.
પરિણામે, પ્રથમ મધ્યવર્તી પરિણામ:
સહસંબંધિત સબક્વેરી વિના સંપાદિત ક્વેરીપસંદ કરો
p. "PARAMETER_ID" parameter_id તરીકે,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" ગ્રાહક_ભાગી નંબર તરીકે,
w."LRM" AS LRM,
w. "LOTID" as lotid,
w. "RTD_VALUE" AS RTD_મૂલ્ય,
w. "LOWER_SPEC_LIMIT" as low_spec_limit,
w. "UPPER_SPEC_LIMIT" as upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
અર્ક (s. "SPENT_DATE" થી વર્ષ) AS વર્ષ,
અર્ક (s. "SPENT_DATE" માંથી મહિનો) મહિના તરીકે,
s."REPORT_NAME" AS રિપોર્ટ_નામ,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" as customerparam_name
ડબલ્યુડેટામાંથી આંતરિક જોડાવા માટે s પર ખર્ચવામાં આવ્યો."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
આંતરિક જોડાઓ spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
આંતરિક જોડાઓ pd pd ON pd."PD_ID" = sp."PD_ID"
આંતરિક જોડાઓ ( પસંદ કરો w2."LRM", MAX(s2."SPENT_DATE") AS "SPENT_DATE"
s2 INNER જોઇન wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
W2.LRM દ્વારા જૂથ
) md ON md."SPENT_DATE" = s."SPENT_DATE" AND md."LRM" = w."LRM"
ક્યાં છે
s."SPENT_DATE" >= '2018-07-01' અને s."SPENT_DATE" <= '2018-09-30';
આયોજન સમય: 3.192ms
એક્ઝેક્યુશન સમય: 208014.134ms

તેથી, પરિણામ સ્વરૂપે અમારી પાસે જે છે તે પ્રથમ સ્વીકાર્ય પરિણામ છે, જે ગ્રાહકને બતાવવામાં અમને શરમ નથી:
આનાથી પ્રારંભ: 8 222 351.640 ms (2 કલાકથી વધુ)
હાંસલ: 1 ms (માત્ર 223 મિનિટથી વધુ).
પરિણામ (મધ્યવર્તી): 208 014.134 ms (ફક્ત 3 મિનિટથી વધુ).

ઉત્તમ પરિણામ.

પરિણામ

આ અટકી શક્યું હોત.
પરંતુ…
ભૂખ ખાવાની સાથે આવે છે. રોડ ચાલવાથી માસ્ટ થશે. કોઈપણ પરિણામ મધ્યવર્તી છે. મૃત બંધ. વગેરે.
ચાલો ઑપ્ટિમાઇઝેશન સાથે ચાલુ રાખીએ.
મહાન વિચાર. ખાસ કરીને ગ્રાહક તેની સામે પણ ન હતો તે ધ્યાનમાં લેવું. અને તે પણ મજબૂત - માટે.

તેથી, ડેટાબેઝને ફરીથી ડિઝાઇન કરવાનો સમય આવી ગયો છે. વિનંતીનું માળખું હવે ઑપ્ટિમાઇઝ કરી શકાતું નથી (જોકે, તે પછીથી બહાર આવ્યું તેમ, ખરેખર ઉડવા માટે દરેક વસ્તુ માટે એક વિકલ્પ છે). પરંતુ હવે ડેટાબેઝ ડિઝાઇનનું ઓપ્ટિમાઇઝેશન અને વિકાસ કરવા માટે, આ પહેલેથી જ એક ખૂબ જ આશાસ્પદ વિચાર છે. અને સૌથી અગત્યનું રસપ્રદ. ફરીથી, યુવાની યાદ રાખો. છેવટે, હું તરત જ ડીબીએ બન્યો ન હતો, હું પ્રોગ્રામરો (મૂળભૂત, એસેમ્બલર, si, si બે વાર પ્લસ, ઓરેકલ, plsql)માંથી મોટો થયો. એક રસપ્રદ વિષય, અલબત્ત, અલગ સંસ્મરણો માટે ;-).
જો કે, ચાલો વિષયાંતર ન કરીએ.

અને તેથી,

અને કદાચ વિભાગીકરણ અમને મદદ કરશે?
સ્પોઇલર - "હા, તે મદદ કરી, અને પ્રદર્શનને ઑપ્ટિમાઇઝ કરવામાં, સહિત."

પરંતુ તે એક સંપૂર્ણપણે અલગ વાર્તા છે ...

ચાલુ રહી શકાય…

સોર્સ: www.habr.com