🥇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 мәселе: bool басымдылығы

Дереккөзді пішімдейік:

{... 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 көрсетілгендей...

Ақпарат көзі: www.habr.com

PostgreSQL антипаттерндері: SQL-дегі жағдайды бағалау