🥇PostgreSQL հակապատկերներ. վիճակի գնահատում SQL-ում

SQL-ը C++ չէ, ոչ էլ JavaScript: Հետևաբար, տրամաբանական արտահայտությունների գնահատականը տարբեր է, և սա ամենևին էլ նույնը չէ.

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL հարցման կատարման պլանը օպտիմալացնելիս կարող է կամայականորեն «վերադասավորել» համարժեք պայմանները, դրանցից ոչ մեկը մի հաշվարկեք անհատական գրառումների համար, անդրադարձեք կիրառական ցուցանիշի վիճակին ... Մի խոսքով, ամենահեշտ ձևը ենթադրելն է, որ դուք չի կարող կառավարել հերթականությունը, որով դրանք կլինեն (և արդյոք դրանք ընդհանրապես կհաշվարկվեն) հավասար պայմանները.

Հետևաբար, եթե դուք դեռ ցանկանում եք կառավարել առաջնահերթությունը, դուք պետք է կառուցվածքայինորեն կառավարեք դարձնել այս պայմանները անհավասար պայմանականով արտահայտությունները и օպերատորներ.

Տվյալները և դրանց հետ աշխատելը հիմք են հանդիսանում մեր VLSI համալիրի, ուստի մեզ համար շատ կարևոր է, որ դրանց վրա վիրահատությունները կատարվեն ոչ միայն ճիշտ, այլև արդյունավետ։ Դիտարկենք կոնկրետ օրինակներ, որտեղ կարելի է սխալներ թույլ տալ արտահայտման գնահատման մեջ, և որտեղ արժե բարելավել դրանց արդյունավետությունը:

#0: RTFM

Սկսելով օրինակ փաստաթղթերից:

Երբ գնահատման կարգը կարևոր է, այն կարելի է ամրագրել կոնստրուկտով CASE. Օրինակ, այս կերպ խուսափել նախադասության մեջ զրոյի բաժանումից WHERE անվստահելի:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Անվտանգ տարբերակ.
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Օգտագործված շինարարությունը CASE պաշտպանում է արտահայտությունը օպտիմալացումից, ուստի այն պետք է օգտագործվի միայն անհրաժեշտության դեպքում:

# 1: ձգանման պայման

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

Ամեն ինչ կարծես թե լավ է, բայց... Ոչ ոք չի խոստանում, որ ներդրվածը SELECT չի կատարվի, եթե առաջին պայմանը կեղծ է: Ուղղեք այն բնադրված IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

Հիմա եկեք ուշադիր նայենք, պարզվեց, որ ձգան գործառույթի ամբողջ մարմինը «փաթաթված» է IF. Եվ սա նշանակում է, որ ոչինչ չի խանգարում մեզ այս պայմանը հեռացնել ընթացակարգից WHEN-պայմաններ:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

Այս մոտեցումը թույլ է տալիս պահպանել սերվերի ռեսուրսները երաշխիքով, եթե պայմանը կեղծ է:

#2՝ ԿԱՄ/ԵՎ շղթա

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

Հակառակ դեպքում կարելի է ձեռք բերել, որ երկուսն էլ EXISTS ճիշտ կլինի, բայց երկուսն էլ մահապատժի են ենթարկվելու.

Բայց եթե մենք հաստատ գիտենք, որ դրանցից մեկը շատ ավելի հաճախ է «ճշմարիտ» (կամ «կեղծ»՝ համար AND-շղթաներ) - հնարավո՞ր է ինչ-որ կերպ «մեծացնել դրա առաջնահերթությունը», որպեսզի երկրորդը ևս մեկ անգամ չկատարվի:

Պարզվում է, որ դա հնարավոր է՝ ալգորիթմական մոտեցումը մոտ է հոդվածի թեմային PostgreSQL հակապատկերներ. Հազվադեպ մուտքը հասնում է JOIN-ի կեսին.

Եկեք ուղղակի «խցկենք CASE-ի տակ» այս երկու պայմաններից էլ.

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

Այս դեպքում մենք չենք սահմանել ELSE-արժեք, այսինքն, եթե երկու պայմաններն էլ կեղծ են CASE կվերադառնա NULL, որը մեկնաբանվում է որպես FALSE в WHERE- պայմանները.

Այս օրինակը կարելի է համատեղել այլ կերպ՝ ըստ ճաշակի և գույնի.

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3. ինչպես [չ] գրել պայմաններ

Մենք երկու օր ծախսեցինք այս ձգանի «տարօրինակ» գործարկման պատճառների վերլուծության վրա. տեսնենք, թե ինչու:

Աղբյուր.

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

Խնդիր թիվ 1. անհավասարությունը չի հաշվի առնում NULL-ը

Ենթադրենք, որ ամեն ինչ OLD- դաշտերը կարևոր էին NULL. Ի՞նչ է լինելու։

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

Իսկ պայմանները մշակելու տեսանկյունից NULL համարժեք FALSE, ինչպես նշվեց վերևում:

որոշումՕգտագործեք օպերատոր IS DISTINCT FROM - ից ROW- օպերատոր, ամբողջ գրառումները միանգամից համեմատելով.

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

Խնդիր թիվ 2. նույն ֆունկցիոնալության տարբեր իրականացում

Եկեք համեմատենք.

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

Ինչու են լրացուցիչ ներդրումներ SELECT? Գործառույթ to_regclass? Ինչու՞ է տարբեր...

Եկեք շտկենք.

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

Խնդիր թիվ 3. բուլ գերակայություն

Եկեք ձևաչափենք աղբյուրը.

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

Օփ... Փաստորեն, պարզվեց, որ առաջին երկու պայմաններից որևէ մեկի ճշմարտացիության դեպքում ամբողջ պայմանը վերածվում է. TRUE, անտեսելով անհավասարությունները։ Եվ սա ամենևին էլ այն չէ, ինչ մենք ուզում էինք։

Եկեք շտկենք.

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

Խնդիր թիվ 4 (փոքր). բարդ ԿԱՄ պայման մեկ դաշտի համար

Փաստորեն, թիվ 3-ում խնդիրներ ունեինք հենց այն պատճառով, որ երեք պայման կար. Բայց դրանց փոխարեն դուք կարող եք յոլա գնալ մեկով՝ օգտագործելով մեխանիզմը coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

Մենք նույնպես NULL «բռնել», և բարդ OR Պետք չէ իրարանցում անել փակագծերով:

Ընդհանուր

Եկեք շտկենք այն, ինչ ստացել ենք.

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

Եվ հաշվի առնելով, որ այս ձգան գործառույթը կարող է օգտագործվել միայն UPDATEձգան՝ առկայության պատճառով OLD/NEW վերին մակարդակի վիճակում, ապա այս վիճակը կարող է ընդհանրապես դուրս հանվել WHEN- վիճակ, ինչպես ցույց է տրված թիվ 1-ում...

Source: www.habr.com

PostgreSQL հակապատկերներ. վիճակի գնահատում SQL-ում