ఇదంతా ఎలా మొదలైందో మీకు గుర్తుందా. ప్రతిదీ మొదటిసారి మరియు మళ్లీ జరిగింది

నేను PostgreSQL ప్రశ్నను ఎలా ఆప్టిమైజ్ చేయాలి మరియు దాని నుండి ఏమి వచ్చింది అనే దాని గురించి.
మీరు ఎందుకు చేయాల్సి వచ్చింది? అవును, ఎందుకంటే గత 4 సంవత్సరాలు ప్రతిదీ నిశ్శబ్దంగా, ప్రశాంతంగా, గడియారం టిక్కింగ్ లాగా పనిచేసింది.
ఎపిగ్రాఫ్‌గా.

వాస్తవ సంఘటనల ఆధారంగా.
అన్ని పేర్లు మార్చబడ్డాయి, యాదృచ్ఛికాలు యాదృచ్ఛికంగా ఉన్నాయి.

మీరు ఒక నిర్దిష్ట ఫలితాన్ని సాధించినప్పుడు, ప్రారంభానికి ప్రేరణ ఏమిటో గుర్తుంచుకోవడం ఎల్లప్పుడూ ఆసక్తికరంగా ఉంటుంది, ఇది ఎక్కడ ప్రారంభమైంది.

కాబట్టి, ఫలితంగా ఏమి జరిగిందో వ్యాసంలో క్లుప్తంగా వివరించబడింది “PostgreSQL పనితీరును మెరుగుపరిచే పద్ధతుల్లో ఒకటిగా సింథసిస్".

మునుపటి సంఘటనల గొలుసును పునఃసృష్టించడం బహుశా ఆసక్తికరంగా ఉంటుంది.
చరిత్ర ఖచ్చితమైన ప్రారంభ తేదీని సేవ్ చేసింది - 2018-09-10 18:02:48.
అలాగే, కథలో ఇదంతా ప్రారంభమైన అభ్యర్థన ఉంది:
సమస్య అభ్యర్థనఎంచుకోండి
p.“PARAMETER_ID” parameter_id వలె,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS కస్టమర్_పార్ట్‌నంబర్,
w. "LRM" AS LRM,
w. "LOTID" లాటిడ్,
w.“RTD_VALUE” RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” AS అప్పర్_స్పెక్_లిమిట్,
p."TYPE_CALCUL" AS రకం_కాలిక్యుల్,
s."SPENT_NAME" AS ఖర్చు చేసిన_పేరు,
సె.“SPENT_DATE” AS గడిపిన_తేదీ,
సంగ్రహం("SPENT_DATE" నుండి సంవత్సరం) AS సంవత్సరం,
("SPENT_DATE" నుండి నెల)ని నెలగా సంగ్రహించండి,
s."REPORT_NAME" AS నివేదిక_పేరు,
p."STPM_NAME" AS stpm_పేరు,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
wdata w నుండి,
ఖర్చు చేసాడు,
pmtr p,
ఖర్చు_పిడి ఎస్పి,
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' మరియు s.“SPENT_DATE” <= '2018-09-30'
మరియు s.“SPENT_DATE” = (MAXని ఎంచుకోండి(s2.“SPENT_DATE”)
ఖర్చు చేసిన సె2 నుండి,
wdata w2
ఎక్కడ s2.“SPENT_ID” = w2.“SPENT_ID”
మరియు w2.“LRM” = w.“LRM”);

సమస్య యొక్క వివరణ ఊహాజనిత ప్రామాణికమైనది - “ప్రతిదీ చెడ్డది. సమస్య ఏమిటో చెప్పు."
నాకు వెంటనే 3న్నర అంగుళాల డ్రైవ్‌ల సమయానికి సంబంధించిన ఒక ఉదంతం గుర్తుకు వచ్చింది:

లామర్ హ్యాకర్ వద్దకు వస్తుంది.
-నాకు ఏమీ పనిలేదు, సమస్య ఎక్కడ ఉందో చెప్పండి.
- DNA లో...

అయితే, పనితీరు సంఘటనలను పరిష్కరించడానికి ఇది మార్గం కాదు. "వాళ్ళు మనల్ని అర్థం చేసుకోకపోవచ్చు"(తో). మనం దాన్ని గుర్తించాలి.
సరే, తవ్వి చూద్దాం. బహుశా ఫలితంగా ఏదో పేరుకుపోతుంది.

విచారణ ప్రారంభించారు

కాబట్టి, వివరించడానికి కూడా ఆశ్రయించకుండా, కంటితో వెంటనే ఏమి చూడవచ్చు.
1) JOINలు ఉపయోగించబడవు. ఇది చెడ్డది, ప్రత్యేకించి కనెక్షన్ల సంఖ్య ఒకటి కంటే ఎక్కువ ఉంటే.
2) కానీ అంతకన్నా దారుణమైన విషయం ఏమిటంటే, సహసంబంధమైన సబ్‌క్వెరీలు, అంతేకాకుండా, అగ్రిగేషన్‌తో. ఇది చాలా చెడ్డది.
ఇది వాస్తవానికి చెడ్డది. అయితే ఇది ఒకవైపు మాత్రమే. మరోవైపు, ఇది చాలా మంచిది, ఎందుకంటే సమస్య స్పష్టంగా పరిష్కారం మరియు మెరుగుపరచగల అభ్యర్థనను కలిగి ఉంది.
జాతకుడు (సి) వద్దకు వెళ్లవద్దు.
ప్రశ్న ప్రణాళిక అంత క్లిష్టంగా లేదు, కానీ ఇది చాలా సూచనగా ఉంది:
అమలు ప్రణాళిక

చాలా ఆసక్తికరమైన మరియు ఉపయోగకరమైనది, ఎప్పటిలాగే, ప్రారంభంలో మరియు ముగింపులో ఉంటుంది.
నెస్టెడ్ లూప్ (ఖర్చు=935.84..479763226.18 వరుసలు=3322 వెడల్పు=135) (వాస్తవ సమయం=31.536..8220420.295 వరుసలు=8111656 లూప్‌లు=1)
ప్రణాళిక సమయం: 3.807 ms
అమలు సమయం: 8222351.640 ms
పూర్తి సమయం 2 గంటల కంటే ఎక్కువ.

సమయం తీసుకున్న తప్పుడు పరికల్పనలు

పరికల్పన 1 - ఆప్టిమైజర్ పొరపాటు చేస్తుంది మరియు తప్పు ప్రణాళికను రూపొందిస్తుంది.

అమలు ప్రణాళికను దృశ్యమానం చేయడానికి, మేము సైట్‌ని ఉపయోగిస్తాము https://explain.depesz.com/. అయితే, సైట్ ఆసక్తికరమైన లేదా ఉపయోగకరమైన దేనినీ చూపించలేదు. మొదటి మరియు రెండవ చూపులో, నిజంగా సహాయపడే ఏదీ లేదు. పూర్తి స్కాన్ తక్కువగా ఉండే అవకాశం ఉందా. ముందుకి వెళ్ళు.

పరికల్పన 2-ఆటోవాక్యూమ్ వైపు నుండి బేస్ మీద ప్రభావం, మీరు బ్రేక్‌లను వదిలించుకోవాలి.

కానీ ఆటోవాక్యూమ్ డెమోన్లు బాగా ప్రవర్తిస్తాయి, ఎక్కువ కాలం వేలాడుతున్న ప్రక్రియలు లేవు. తీవ్రమైన లోడ్ లేదు. మనం ఇంకేదో వెతకాలి.

పరికల్పన 3 - గణాంకాలు పాతవి, ప్రతిదీ తిరిగి లెక్కించాల్సిన అవసరం ఉంది

మళ్ళీ, అది కాదు. గణాంకాలు తాజాగా ఉన్నాయి. ఆటోవాక్యూమ్‌తో సమస్యలు లేకపోవడంతో ఇది ఆశ్చర్యం కలిగించదు.

ఆప్టిమైజ్ చేయడం ప్రారంభిద్దాం

ప్రధాన పట్టిక 'wdata' ఖచ్చితంగా చిన్నది కాదు, దాదాపు 3 మిలియన్ రికార్డులు.
మరియు ఈ పట్టికను పూర్తి స్కాన్ అనుసరిస్తుంది.

హాష్ కాండ్: ((w."SPENT_ID" = s."SPENT_ID") మరియు ((సబ్‌ప్లాన్ 1) = సె."SPENT_DATE"))
-> సీక్ స్కాన్ wdata wలో (ఖర్చు=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" AS కస్టమర్_పార్ట్‌నంబర్,
w. "LRM" AS LRM,
w. "LOTID" లాటిడ్,
w.“RTD_VALUE” RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” AS అప్పర్_స్పెక్_లిమిట్,
p."TYPE_CALCUL" AS రకం_కాలిక్యుల్,
s."SPENT_NAME" AS ఖర్చు చేసిన_పేరు,
సె.“SPENT_DATE” AS గడిపిన_తేదీ,
సంగ్రహం("SPENT_DATE" నుండి సంవత్సరం) AS సంవత్సరం,
("SPENT_DATE" నుండి నెల)ని నెలగా సంగ్రహించండి,
s."REPORT_NAME" AS నివేదిక_పేరు,
p."STPM_NAME" AS stpm_పేరు,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
wdata నుండి INNER JOIN ఖర్చు చేసిన లు w.“SPENT_ID”=s.”“SPENT_ID”
లోపలి చేరండి pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
ఇన్నర్ జాయిన్ స్పెండ్_పిడి sp ఆన్ s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
ఎక్కడ
s.“SPENT_DATE” >= '2018-07-01' మరియు s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (MAXని ఎంచుకోండి(s2.“SPENT_DATE”)
wdata w2 INNER JOIN నుండి w2.“SPENT_ID”=s2.“SPENT_ID”లో సె2 గడిపారు
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
ప్రణాళిక సమయం: 2.486 ms
అమలు సమయం: 1223680.326 ms

కాబట్టి, మొదటి ఫలితం.
ఉంది: 6 ms (దాదాపు 985 గంటలు).
ఇది మారింది: 1 223 680.326 ms (కేవలం 20 నిమిషాల కంటే ఎక్కువ).
మంచి ఫలితం. సూత్రప్రాయంగా, మళ్ళీ, మేము అక్కడ ఆగిపోవచ్చు. కానీ ఇది చాలా రసహీనమైనది, మీరు ఆపలేరు.
కోసం

దశ రెండు - పరస్పర సంబంధం ఉన్న సబ్‌క్వెరీని వదిలించుకోండి

మార్చబడిన అభ్యర్థన వచనం:
పరస్పర సంబంధం లేని సబ్‌క్వెరీ లేకుండాఎంచుకోండి
p.“PARAMETER_ID” parameter_id వలె,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS కస్టమర్_పార్ట్‌నంబర్,
w. "LRM" AS LRM,
w. "LOTID" లాటిడ్,
w.“RTD_VALUE” RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” AS అప్పర్_స్పెక్_లిమిట్,
p."TYPE_CALCUL" AS రకం_కాలిక్యుల్,
s."SPENT_NAME" AS ఖర్చు చేసిన_పేరు,
సె.“SPENT_DATE” AS గడిపిన_తేదీ,
సంగ్రహం("SPENT_DATE" నుండి సంవత్సరం) AS సంవత్సరం,
("SPENT_DATE" నుండి నెల)ని నెలగా సంగ్రహించండి,
s."REPORT_NAME" AS నివేదిక_పేరు,
p."STPM_NAME" AS stpm_పేరు,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
Wdata w INNER JOIN నుండి sలో గడిపారు.“SPENT_ID” = w.“SPENT_ID”
లోపలి చేరండి pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
ఇన్నర్ జాయిన్ స్పెండ్_పిడి sp ఆన్ s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
ఇన్నర్ జాయిన్ (ఎంచుకోండి w2.“LRM”, MAX(s2.“SPENT_DATE”)
s2లో గడిపిన s2 నుండి INNER Wdata w2 ON s2.“SPENT_ID” = wXNUMX.“SPENT_ID”
w2 ద్వారా సమూహం.“LRM”
) md on w.“LRM” = md.“LRM”
ఎక్కడ
s."SPENT_DATE" >= '2018-07-01' మరియు s."SPENT_DATE" <= '2018-09-30';
ప్రణాళిక సమయం: 2.291 ms
అమలు సమయం: 165021.870 ms

ఉంది: 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" AS కస్టమర్_పార్ట్‌నంబర్,
w. "LRM" AS LRM,
w. "LOTID" లాటిడ్,
w.“RTD_VALUE” RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” AS అప్పర్_స్పెక్_లిమిట్,
p."TYPE_CALCUL" AS రకం_కాలిక్యుల్,
s."SPENT_NAME" AS ఖర్చు చేసిన_పేరు,
సె.“SPENT_DATE” AS గడిపిన_తేదీ,
సంగ్రహం (సె.“SPENT_DATE” నుండి సంవత్సరం) AS సంవత్సరం,
సంగ్రహం(సె.“SPENT_DATE” నుండి నెల) నెలగా,
s."REPORT_NAME" AS నివేదిక_పేరు,
p."STPM_NAME" AS stpm_పేరు,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
Wdata w INNER JOIN నుండి sలో గడిపారు.“SPENT_ID” = w.“SPENT_ID”
లోపలి చేరండి pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
ఇన్నర్ జాయిన్ స్పెండ్_పిడి sp ఆన్ s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
ఇన్నర్ జాయిన్ ( w2.“LRM”, MAX(s2.“SPENT_DATE”)ని “SPENT_DATE”గా ఎంచుకోండి
s2లో గడిపిన s2 నుండి INNER Wdata w2 ON s2.“SPENT_ID” = wXNUMX.“SPENT_ID”
w2 ద్వారా సమూహం.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” మరియు md.“LRM” = w.“LRM”
ఎక్కడ
s."SPENT_DATE" >= '2018-07-01' మరియు s."SPENT_DATE" <= '2018-09-30';
ప్రణాళిక సమయం: 3.192 ms
అమలు సమయం: 208014.134 ms

కాబట్టి, మేము మొదటి ఆమోదయోగ్యమైన ఫలితంతో ముగుస్తుంది, ఇది కస్టమర్‌కు చూపించడానికి అవమానకరమైనది కాదు:
దీనితో ప్రారంభించబడింది: 8 222 351.640 ms (2 గంటల కంటే ఎక్కువ)
మేము సాధించగలిగాము: 1 ms (223 నిమిషాల కంటే కొంచెం ఎక్కువ).
ఫలితం (మధ్యంతర): 208 014.134 ms (కేవలం 3 నిమిషాల కంటే ఎక్కువ).

అద్భుతమైన ఫలితం.

ఫలితం

మేము అక్కడ ఆగి ఉండవచ్చు.
కానీ...
తింటే ఆకలి వస్తుంది. నడిచేవాడు రోడ్డు మీద పట్టు సాధిస్తాడు. ఏదైనా ఫలితం ఇంటర్మీడియట్. ఆగి చనిపోయాడు. మొదలైనవి
ఆప్టిమైజేషన్‌ని కొనసాగిద్దాం.
గొప్ప ఆలోచన. ముఖ్యంగా కస్టమర్ కూడా పట్టించుకోవడం లేదు. మరియు దాని కోసం కూడా గట్టిగా.

కాబట్టి, ఇది డేటాబేస్ పునఃరూపకల్పన కోసం సమయం. ప్రశ్న నిర్మాణం ఇకపై ఆప్టిమైజ్ చేయబడదు (అయితే, అది తరువాత తేలినట్లుగా, ప్రతిదీ వాస్తవానికి విఫలమయ్యేలా చూసుకోవడానికి ఒక ఎంపిక ఉంది). కానీ డేటాబేస్ డిజైన్‌ను ఆప్టిమైజ్ చేయడం మరియు అభివృద్ధి చేయడం ప్రారంభించడం ఇప్పటికే చాలా మంచి ఆలోచన. మరియు ముఖ్యంగా ఆసక్తికరమైన. మళ్ళీ, మీ యవ్వనాన్ని గుర్తుంచుకోండి. నేను వెంటనే DBA అవ్వలేదు, నేను ప్రోగ్రామర్‌గా పెరిగాను (BASIC, అసెంబ్లర్, C, డబుల్ ప్లస్ C, Oracle, plsql). ఒక ఆసక్తికరమైన అంశం, ప్రత్యేక జ్ఞాపకాల కోసం ;-).
అయితే, మనం పరధ్యానం చెందకూడదు.

కాబట్టి,

లేదా విభజన మాకు సహాయపడుతుందా?
స్పాయిలర్ - “అవును, పనితీరును ఆప్టిమైజ్ చేయడంతో సహా ఇది సహాయపడింది.”

కానీ అది పూర్తిగా భిన్నమైన కథ…

కొనసాగుతుంది…

మూలం: www.habr.com