PostgreSQL એન્ટિપેટર્ન: હાનિકારક જોડાઓ અને ORs

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

PG ના કેટલાક અનુગામી સંસ્કરણોમાં શેડ્યૂલર વધુ સ્માર્ટ બનતાની સાથે પરિસ્થિતિ બદલાઈ શકે છે, પરંતુ 9.4/9.6 માટે તે લગભગ સમાન દેખાય છે, જેમ કે અહીં ઉદાહરણોમાં છે.

ચાલો એક ખૂબ જ વાસ્તવિક વિનંતી લઈએ:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

કોષ્ટક અને ક્ષેત્રના નામો વિશેક્ષેત્રો અને કોષ્ટકોના "રશિયન" નામોને અલગ રીતે સારવાર કરી શકાય છે, પરંતુ આ સ્વાદની બાબત છે. કારણ કે અહીં ટેન્સર પર ત્યાં કોઈ વિદેશી વિકાસકર્તાઓ નથી, અને PostgreSQL અમને ચિત્રલિપીમાં પણ નામ આપવાની મંજૂરી આપે છે, જો તેઓ અવતરણમાં બંધ, તો પછી અમે અસ્પષ્ટ અને સ્પષ્ટ રીતે વસ્તુઓને નામ આપવાનું પસંદ કરીએ છીએ જેથી કોઈ વિસંગતતા ન હોય.
ચાલો પરિણામી યોજના જોઈએ:

[explan.tensor.ru પર જુઓ]

144ms અને લગભગ 53K બફર્સ - એટલે કે 400MB થી વધુ ડેટા! અને જો તે બધા અમારી વિનંતીના સમય સુધીમાં કેશમાં હોય તો અમે નસીબદાર હોઈશું, અન્યથા ડિસ્કમાંથી વાંચવામાં તે ઘણી વખત વધુ સમય લેશે.

અલ્ગોરિધમ સૌથી મહત્વપૂર્ણ છે!

કોઈપણ વિનંતીને કોઈક રીતે ઑપ્ટિમાઇઝ કરવા માટે, તમારે પહેલા સમજવું જોઈએ કે તે શું કરવું જોઈએ.
ચાલો હવે માટે આ લેખના અવકાશની બહાર ડેટાબેઝ માળખાના વિકાસને છોડી દઈએ અને સંમત થઈએ કે આપણે પ્રમાણમાં "સસ્તું" કરી શકીએ છીએ. વિનંતી ફરીથી લખો અને/અથવા અમને જોઈતી કેટલીક વસ્તુઓને બેઝ પર રોલ કરો સૂચકાંકો.

તેથી વિનંતી:
- ઓછામાં ઓછા કેટલાક દસ્તાવેજનું અસ્તિત્વ તપાસે છે
- આપણને જરૂર હોય તેવી સ્થિતિમાં અને ચોક્કસ પ્રકારની
- જ્યાં લેખક અથવા કલાકાર એ કર્મચારી છે જેની અમને જરૂર છે

જોડાઓ + મર્યાદા 1

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

ચાલો પહેલા "દસ્તાવેજ પ્રકાર" કોષ્ટક સાથેના જોડાણથી છુટકારો મેળવીએ, અને તે જ સમયે ડેટાબેઝને કહીએ કે અમારો પ્રકાર રેકોર્ડ અનન્ય છે (અમે આ જાણીએ છીએ, પરંતુ શેડ્યૂલરને હજુ સુધી કોઈ ખ્યાલ નથી):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

હા, જો કોષ્ટક/CTE માં એક જ રેકોર્ડના એક જ ક્ષેત્રનો સમાવેશ થાય છે, તો પછી PG માં તમે તેના બદલે આના જેવું પણ લખી શકો છો.

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

PostgreSQL ક્વેરીઝમાં આળસુ મૂલ્યાંકન

બીટમેપઓર વિ યુનિયન

કેટલાક કિસ્સાઓમાં, બીટમેપ હીપ સ્કેન અમને ઘણો ખર્ચ કરશે - ઉદાહરણ તરીકે, અમારી પરિસ્થિતિમાં, જ્યારે ઘણા બધા રેકોર્ડ્સ આવશ્યક શરતને પૂર્ણ કરે છે. અમને તે મળ્યું કારણ કે અથવા સ્થિતિ BitmapOr માં ફેરવાઈ- યોજનામાં કામગીરી.
ચાલો મૂળ સમસ્યા પર પાછા ફરીએ - આપણે તેને અનુરૂપ રેકોર્ડ શોધવાની જરૂર છે કોઈપણ શરતોમાંથી - એટલે કે, બંને શરતો હેઠળ તમામ 59K રેકોર્ડ્સ શોધવાની જરૂર નથી. એક શરત બહાર કામ કરવા માટે એક માર્ગ છે, અને જ્યારે પ્રથમમાં કંઈ ન મળ્યું ત્યારે જ બીજા પર જાઓ. નીચેની ડિઝાઇન અમને મદદ કરશે:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

"બાહ્ય" મર્યાદા 1 ખાતરી કરે છે કે જ્યારે પ્રથમ રેકોર્ડ મળે ત્યારે શોધ સમાપ્ત થાય છે. અને જો તે પહેલાથી જ પ્રથમ બ્લોકમાં જોવા મળે છે, તો બીજો બ્લોક ચલાવવામાં આવશે નહીં (ક્યારેય ચલાવવામાં આવ્યું નથી ના સન્માનમાં).

"કેસ હેઠળ મુશ્કેલ પરિસ્થિતિઓ છુપાવવી"

મૂળ ક્વેરી માં એક અત્યંત અસુવિધાજનક ક્ષણ છે - સંબંધિત કોષ્ટક “DocumentExtension” સામે સ્થિતિ તપાસી રહી છે. અભિવ્યક્તિમાં અન્ય પરિસ્થિતિઓના સત્યને ધ્યાનમાં લીધા વિના (ઉદાહરણ તરીકે, d. "કાઢી નાખેલ" સાચું નથી), આ કનેક્શન હંમેશા એક્ઝિક્યુટ કરવામાં આવે છે અને "ખર્ચ સંસાધન" કરે છે. તેમાંથી વધુ કે ઓછા ખર્ચવામાં આવશે - આ કોષ્ટકના કદ પર આધારિત છે.
પરંતુ તમે ક્વેરી સંશોધિત કરી શકો છો જેથી સંબંધિત રેકોર્ડની શોધ ત્યારે જ થાય જ્યારે તે ખરેખર જરૂરી હોય:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

એકવાર અમને લિંક કરેલ કોષ્ટકમાંથી પરિણામ માટે કોઈપણ ક્ષેત્રની જરૂર નથી, તો અમારી પાસે સબક્વેરી પર JOIN ને શરતમાં ફેરવવાની તક છે.
ચાલો અનુક્રમિત ફીલ્ડ્સને "કેસ કૌંસની બહાર" છોડીએ, રેકોર્ડમાંથી WHEN બ્લોકમાં સરળ શરતો ઉમેરીએ - અને હવે "ભારે" ક્વેરી માત્ર THEN પર પસાર થાય ત્યારે જ એક્ઝિક્યુટ થાય છે.

મારું છેલ્લું નામ "કુલ" છે

અમે ઉપર વર્ણવેલ તમામ મિકેનિક્સ સાથે પરિણામી ક્વેરી એકત્રિત કરીએ છીએ:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

અનુક્રમણિકાઓને સમાયોજિત કરી રહ્યા છીએ

એક પ્રશિક્ષિત આંખે નોંધ્યું કે UNION સબબ્લોકમાં અનુક્રમિત પરિસ્થિતિઓ થોડી અલગ છે - આ એટલા માટે છે કારણ કે અમારી પાસે ટેબલ પર પહેલેથી જ યોગ્ય અનુક્રમણિકાઓ છે. અને જો તેઓ અસ્તિત્વમાં ન હોય, તો તે બનાવવા યોગ્ય હશે: દસ્તાવેજ(વ્યક્તિ3, દસ્તાવેજનો પ્રકાર) и દસ્તાવેજ (દસ્તાવેજ પ્રકાર, કર્મચારી).
ROW પરિસ્થિતિઓમાં ક્ષેત્રોના ક્રમ વિશેઆયોજકના દૃષ્ટિકોણથી, અલબત્ત, તમે લખી શકો છો (A, B) = (constA, constB)અને (B, A) = (constB, constA). પરંતુ જ્યારે રેકોર્ડિંગ અનુક્રમણિકામાં ક્ષેત્રોના ક્રમમાં, આવી વિનંતી પાછળથી ડીબગ કરવા માટે વધુ અનુકૂળ છે.
શું છે યોજનામાં?

[explan.tensor.ru પર જુઓ]

કમનસીબે, અમે કમનસીબ હતા અને પ્રથમ UNION બ્લોકમાં કંઈ મળ્યું ન હતું, તેથી બીજાને હજુ પણ ચલાવવામાં આવ્યો હતો. પરંતુ તેમ છતાં - માત્ર 0.037ms અને 11 બફર્સ!
અમે વિનંતીને ઝડપી બનાવી છે અને મેમરીમાં ડેટા પમ્પિંગમાં ઘટાડો કર્યો છે અનેક હજાર વખત, એકદમ સરળ તકનીકોનો ઉપયોગ કરીને - થોડી કોપી-પેસ્ટ સાથે સારું પરિણામ. 🙂

સોર્સ: www.habr.com