Nā Antipatterns PostgreSQL: nā hui ʻino a me nā OR

E makaʻala i nā hana e lawe mai ai i nā buffers...
Ke hoʻohana nei i kahi nīnau liʻiliʻi ma ke ʻano he laʻana, e nānā kākou i kekahi mau ala ākea e hoʻonui ai i nā nīnau ma PostgreSQL. Inā ʻoe e hoʻohana iā lākou a i ʻole, aia iā ʻoe, akā pono e ʻike e pili ana iā lākou.

I kekahi mau mana hope o PG hiki ke loli ke kūlana i ka lilo ʻana o ka mea hoʻonohonoho maʻalahi, akā no ka 9.4/9.6 ua like ia, e like me nā hiʻohiʻona ma aneʻi.

E lawe kāua i kahi noi maoli:

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;

e pili ana i ka papa a me nā inoa kahuaHiki ke mālama ʻia nā inoa ʻo "Russian" o nā māla a me nā papa, akā he mea ʻono kēia. No ka mea maanei ma Tensor ʻaʻohe mea hoʻomohala haole, a ʻae ʻo PostgreSQL iā mākou e hāʻawi i nā inoa ma nā hieroglyphs, inā lākou i hoʻopaʻa ʻia i loko o nā huaʻōlelo, a laila makemake mākou e hoʻopaʻa inoa i nā mea me ka maopopo ʻole a ʻaʻohe mea like ʻole.
E nānā kākou i ka hopena o ka papahana:

[nānā ma explain.tensor.ru]

144ms a kokoke i 53K mau pale - ʻo ia hoʻi, ʻoi aku ma mua o 400MB o ka ʻikepili! A e laki mākou inā aia lākou a pau i loko o ka cache i ka manawa o kā mākou noi, i ʻole e lōʻihi ka lōʻihi i ka heluhelu ʻana mai ka disk.

ʻO ka algorithm ka mea nui loa!

I mea e hoʻonui ai i kekahi noi, pono ʻoe e hoʻomaopopo mua i kāna mea e hana ai.
E haʻalele kākou i ka hoʻomohala ʻana o ka ʻōnaehana waihona ma waho o ke ʻano o kēia ʻatikala no kēia manawa, a ʻae aku hiki iā mākou ke ʻano "cheaply" kākau hou i ka noi a/a ʻōwili paha ma ke kumu i kekahi o nā mea a mākou e pono ai nā ʻanike.

No laila ke noi:
- nānā i ka loaʻa ʻana o kekahi palapala
- ma ke kūlana a mākou e pono ai a me kekahi ʻano
- kahi o ka mea kākau a mea hana paha ka limahana a mākou e pono ai

HUI + LIMIT 1

ʻOi aku ka maʻalahi o ka mea hoʻomohala e kākau i kahi nīnau kahi i hui mua ʻia ai ka nui o nā papa, a laila hoʻokahi wale nō moʻolelo i koe mai kēia pūʻulu holoʻokoʻa. Akā, maʻalahi no ka mea hoʻomohala ʻaʻole ia he ʻoi aku ka maikaʻi no ka waihona.
I kā mākou hihia he 3 mau papa wale nō - a he aha ka hopena ...

E wehe mua kākou i ka pilina me ka papa "Document Type", a ma ka manawa like e haʻi i ka waihona ʻokoʻa kā mākou ʻano moʻolelo (ʻike mākou i kēia, akā ʻaʻohe manaʻo o ka mea hoʻonohonoho):

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

ʻAe, inā loaʻa i ka papa/CTE kahi kahua hoʻokahi o kahi moʻolelo hoʻokahi, a laila ma PG hiki iā ʻoe ke kākau e like me kēia, ma kahi o

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

Ka loiloi palaualelo ma nā nīnau PostgreSQL

BitmapOr vs UNION

I kekahi mau hihia, nui ke kumu kūʻai o Bitmap Heap Scan iā mākou - no ka laʻana, i ko mākou kūlana, ke loaʻa ka nui o nā moʻolelo i ke kūlana i koi ʻia. Loaʻa iā mākou no ka mea Ua hoʻololi ʻia ke kūlana i BitmapOr- hana ma ka papahana.
E hoʻi kāua i ka pilikia kumu - pono mākou e ʻimi i kahi moʻolelo pili kekahi mai nā kūlana - ʻo ia hoʻi, ʻaʻohe pono e ʻimi i nā moʻolelo 59K āpau ma lalo o nā kūlana ʻelua. Aia kahi ala e hana ai i hoʻokahi kūlana, a hele i ka lua wale no ke loaa ole i ka mua. E kōkua mai kēia hoʻolālā iā mākou:

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

“Mawaho” LIMIT 1 e hoʻopau i ka huli ʻana ke loaʻa ka moʻolelo mua. A inā loaʻa ia ma ka poloka mua, ʻaʻole e hoʻokō ʻia ka poloka ʻelua (ʻaʻole i hoʻokō ʻia e pili ana i).

"Hana i nā kūlana paʻakikī ma lalo o CASE"

Aia kahi manawa kūpono ʻole i ka nīnau kumu - nānā i ke kūlana e pili ana i ka papa pili "DocumentExtension". Me ka nānā ʻole i ka ʻoiaʻiʻo o nā kūlana ʻē aʻe i ka ʻōlelo (no ka laʻana, d. ʻAʻole ʻoiaʻiʻo "Hoʻopau ʻia".), hoʻokō mau ʻia kēia pili a "kumu waiwai". E hoʻohana ʻia ka nui a i ʻole ka liʻiliʻi o lākou - pili i ka nui o kēia pākaukau.
Akā hiki iā ʻoe ke hoʻololi i ka nīnau i hiki ke ʻimi i kahi moʻolelo pili i ka wā e pono ai:

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

Hoʻokahi manawa mai ka papaʻaina pili iā mākou ʻaʻole pono kekahi o nā māla no ka hopena, a laila loaʻa iā mākou ka manawa kūpono e hoʻohuli iā JOIN i kahi kūlana ma kahi subquery.
E haʻalele kāua i nā kahua kuhikuhi "ma waho o ka CASE brackets", e hoʻohui i nā kūlana maʻalahi mai ka moʻolelo i ka poloka WHEN - a i kēia manawa ua hoʻokō ʻia ka nīnau "kaumaha" i ka wā e hele ai iā THEN.

ʻO "Total" koʻu inoa hope

ʻOhi mākou i ka hopena o ka nīnau me nā mechanics a pau i wehewehe ʻia ma luna.

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;

Hoʻoponopono i nā kuhikuhi

Ua ʻike ka maka i hoʻomaʻamaʻa ʻia he ʻokoʻa iki nā kūlana i kuhikuhi ʻia i nā subblocks UNION - no ka mea ua loaʻa iā mākou nā kuhikuhi kūpono ma ka papaʻaina. A inā ʻaʻole lākou, pono e hana ʻia: Palapala(Person3, DocumentType) и Palapala(DocumentType, Limahana).
e pili ana i ka hoʻonohonoho ʻana o nā kahua ma nā kūlana ROWMai ka manaʻo o ka mea hoʻolālā, ʻoiaʻiʻo, hiki iā ʻoe ke kākau (A, B) = (constA, constB)a (B, A) = (constB, constA). Akā i ka hoʻopaʻa ʻana i ka hoʻonohonoho ʻana o nā kahua ma ka index, ʻoi aku ka maʻalahi o ka noi ʻana i ka debug ma hope.
He aha ka mea i loko o ka papahana?

[nānā ma explain.tensor.ru]

ʻO ka mea pōʻino, ua pōʻino mākou a ʻaʻohe mea i loaʻa ma ka poloka UNION mua, no laila ua hoʻokō ʻia ka lua. Akā naʻe - wale nō 0.037ms a me 11 pale!
Ua wikiwiki mākou i ka noi a ua hōʻemi i ka ʻikepili i ka hoʻomanaʻo mau tausani manawa, me ka hoʻohana ʻana i nā ʻenehana maʻalahi - kahi hopena maikaʻi me kahi kope-paʻi liʻiliʻi. 🙂

Source: www.habr.com