🥇 Gwrthbatrymau PostgreSQL: YMUNIADAU niweidiol a NEU

Byddwch yn wyliadwrus o weithrediadau sy'n dod â byfferau...
Gan ddefnyddio ymholiad bach fel enghraifft, gadewch i ni edrych ar rai dulliau cyffredinol o optimeiddio ymholiadau yn PostgreSQL. Chi sydd i benderfynu a ydych chi'n eu defnyddio ai peidio, ond mae'n werth gwybod amdanynt.

Mewn rhai fersiynau dilynol o PG gall y sefyllfa newid wrth i'r amserlennydd ddod yn fwy craff, ond ar gyfer 9.4/9.6 mae'n edrych tua'r un peth, ag yn yr enghreifftiau yma.

Gadewch i ni dderbyn cais go iawn:

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;

am enwau byrddau a chaeauGellir trin enwau “Rwsia” caeau a thablau yn wahanol, ond mater o chwaeth yw hwn. Gan fod y yma yn Tensor nid oes unrhyw ddatblygwyr tramor, ac mae PostgreSQL yn caniatáu inni roi enwau hyd yn oed mewn hieroglyffau, os ydynt wedi'i amgáu mewn dyfyniadau, yna mae'n well gennym enwi gwrthrychau yn ddiamwys ac yn glir fel nad oes unrhyw anghysondebau.
Edrychwn ar y cynllun canlyniadol:

[edrychwch ar explain.tensor.ru]

144ms a bron i 53K byffer - hynny yw, mwy na 400MB o ddata! A byddwn yn ffodus os yw pob un ohonynt yn y storfa erbyn amser ein cais, fel arall bydd yn cymryd llawer mwy o amser wrth eu darllen o ddisg.

Yr algorithm sydd bwysicaf!

Er mwyn gwneud y gorau o unrhyw gais rywsut, rhaid i chi ddeall yn gyntaf beth ddylai ei wneud.
Gadewch i ni adael datblygiad strwythur y gronfa ddata ei hun y tu allan i gwmpas yr erthygl hon am y tro, a chytuno y gallwn yn gymharol “rhad” ailysgrifennu'r cais a/neu rolio i'r gwaelod rai o'r pethau sydd eu hangen arnom mynegeion.

Felly y cais:
— yn gwirio bodolaeth rhyw ddogfen o leiaf
- yn y cyflwr sydd ei angen arnom ac o fath arbennig
- os mai'r awdur neu'r perfformiwr yw'r gweithiwr sydd ei angen arnom

YMUNWCH + TERFYN 1

Yn aml iawn mae'n haws i ddatblygwr ysgrifennu ymholiad lle mae nifer fawr o dablau'n cael eu cysylltu i ddechrau, ac yna dim ond un cofnod sydd ar ôl o'r set gyfan hon. Ond nid yw haws i'r datblygwr yn golygu mwy effeithlon ar gyfer y gronfa ddata.
Yn ein hachos ni dim ond 3 bwrdd oedd - a beth yw'r effaith ...

Yn gyntaf, gadewch i ni gael gwared ar y cysylltiad â'r tabl "Math o Ddogfen", ac ar yr un pryd dywedwch wrth y gronfa ddata. mae ein cofnod teip yn unigryw (rydym yn gwybod hyn, ond nid oes gan y trefnydd syniad eto):

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

Oes, os yw'r tabl/CTE yn cynnwys un maes o un cofnod, yna yn PG gallwch hyd yn oed ysgrifennu fel hyn, yn lle

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

Gwerthusiad diog mewn ymholiadau PostgreSQL

DidfapOr vs UNDEB

Mewn rhai achosion, bydd Bitmap Heap Scan yn costio llawer i ni - er enghraifft, yn ein sefyllfa ni, pan fydd cryn dipyn o gofnodion yn bodloni'r cyflwr gofynnol. Rydym yn ei gael oherwydd NEU cyflwr wedi'i droi'n BitmapOr- gweithredu yn y cynllun.
Gadewch i ni ddychwelyd at y broblem wreiddiol - mae angen i ni ddod o hyd i gofnod cyfatebol unrhyw un o'r amodau - hynny yw, nid oes angen chwilio am holl gofnodion 59K o dan y ddau amod. Y mae ffordd i weithio allan un cyflwr, a ewch i'r ail yn unig pan na chafwyd dim yn y cyntaf. Bydd y dyluniad canlynol yn ein helpu ni:

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

Mae TERFYN “Allanol” 1 yn sicrhau bod y chwiliad yn dod i ben pan ganfyddir y cofnod cyntaf. Ac os yw eisoes wedi'i ganfod yn y bloc cyntaf, ni fydd yr ail floc yn cael ei weithredu (heb ei ddienyddio erioed o ran).

“Cuddio amodau anodd o dan CASE”

Mae yna foment hynod anghyfleus yn yr ymholiad gwreiddiol – gwirio’r statws yn erbyn y tabl cysylltiedig “DocumentExtension”. Waeth beth yw gwirionedd amodau eraill yn y mynegiant (er enghraifft, d. NID YW “Dileu” yn WIR), mae'r cysylltiad hwn bob amser yn cael ei weithredu ac yn “costio adnoddau”. Bydd mwy neu lai ohonynt yn cael eu gwario - yn dibynnu ar faint y tabl hwn.
Ond gallwch chi addasu'r ymholiad fel bod chwilio am gofnod cysylltiedig yn digwydd dim ond pan fo gwir angen:

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

Unwaith o'r tabl cysylltiedig i ni nid oes angen unrhyw un o'r meysydd ar gyfer y canlyniad, yna cawn gyfle i droi JOIN i amod ar subquery.
Gadewch i ni adael y meysydd wedi'u mynegeio “y tu allan i'r cromfachau CASE”, ychwanegu amodau syml o'r cofnod i'r bloc PRYD - a nawr dim ond wrth basio i YNA y gweithredir yr ymholiad “trwm”.

Fy enw olaf yw "Cyfanswm"

Rydym yn casglu'r ymholiad canlyniadol gyda'r holl fecaneg a ddisgrifir uchod:

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;

Addasu [i] fynegeion

Sylwodd llygad hyfforddedig fod yr amodau mynegeio yn is-flociau UNION ychydig yn wahanol - mae hyn oherwydd bod gennym eisoes fynegeion addas ar y bwrdd. Ac os nad oeddent yn bodoli, byddai'n werth creu: Dogfen (Person3, Math o Ddogfen) и Dogfen (Dogfen Math, Gweithiwr).
am drefn y caeau mewn amodau HTO safbwynt y cynlluniwr, wrth gwrs, gallwch chi ysgrifennu (A, B) = (constA, constB)Ac (B, A) = (constB, constA). Ond wrth recordio yn nhrefn y meysydd yn y mynegai, mae cais o'r fath yn symlach yn fwy cyfleus i ddadfygio yn ddiweddarach.
Beth sydd yn y cynllun?

[edrychwch ar explain.tensor.ru]

Yn anffodus, roeddem yn anlwcus ac ni ddaethpwyd o hyd i unrhyw beth yn y bloc UNION cyntaf, felly roedd yr ail un yn dal i gael ei ddienyddio. Ond er hynny - yn unig 0.037ms ac 11 byffer!
Rydym wedi cyflymu'r cais ac wedi lleihau pwmpio data yn y cof sawl mil o weithiau, gan ddefnyddio technegau gweddol syml - canlyniad da gydag ychydig o gopi-past. 🙂

Ffynhonnell: hab.com

Gwrthbatrymau PostgreSQL: YMUNIADAU a NEUau Niweidiol