PostgreSQL Antipatterns: andao hidona amin'ny rakibolana mavesatra

Manohy ny andian-dahatsoratra natokana ho an'ny fandalinana ireo fomba tsy dia fantatra loatra hanatsarana ny fahombiazan'ny fanontaniana PostgreSQL "toa tsotra" izahay:

• Ny rakitsoratra tsy fahita firy dia ho tonga eo afovoan'ny JOIN
• Sisyphean JOIN arrays
• mampidi-doza JOIN sy OR
• CTE MIDIO CTE

Aza mieritreritra fa tsy tiako loatra ny MIDITRA... :)

Saingy matetika tsy misy izany, ny fangatahana dia hita fa mamokatra kokoa noho ny miaraka aminy. Ka anio no hanandrana Esory ny ROA-INTENANCE JOIN - mampiasa rakibolana.

Manomboka amin'ny PostgreSQL 12, ny sasany amin'ireo toe-javatra voalaza etsy ambany dia mety ho hafa kely noho ny CTE tsy misy famatsiana default. Ity fihetsika ity dia azo averina amin'ny famaritana ny fanalahidy MATERIALIZED.

Betsaka ny "zava-misy" amin'ny voambolana voafetra

Andao hanao asa fampiharana tena izy - mila mampiseho lisitra isika hafatra miditra na asa mavitrika miaraka amin'ny mpandefa:

25.01 | Иванов И.И. | Подготовить описание нового алгоритма.
22.01 | Иванов И.И. | Написать статью на Хабр: жизнь без JOIN.
20.01 | Петров П.П. | Помочь оптимизировать запрос.
18.01 | Иванов И.И. | Написать статью на Хабр: JOIN с учетом распределения данных.
16.01 | Петров П.П. | Помочь оптимизировать запрос.

Ao amin'ny tontolo abstract, ny mpanoratra asa dia tokony hozaraina mitovy amin'ny mpiasa rehetra ao amin'ny fikambananay, fa raha ny marina ny asa dia avy amin'ny olona voafetra ihany - "avy amin'ny fitantanana" miakatra ny ambaratongam-pahefana na "avy amin'ny subcontractors" avy amin'ny departemanta manodidina (mpandalina, mpamorona, varotra, ...).

Andao ekentsika fa ao amin'ny fikambanantsika misy olona 1000, mpanoratra 20 ihany (matetika na kely kokoa aza) no mametraka asa ho an'ny mpanakanto tsirairay ary Andao hampiasa io fahalalana iohanafaingana ny fangatahana "traditional".

Mpamorona script

-- сотрудники
CREATE TABLE person AS
SELECT
  id
, repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name"
, '2000-01-01'::date - (random() * 1e4)::integer birth_date
FROM
  generate_series(1, 1000) id;

ALTER TABLE person ADD PRIMARY KEY(id);

-- задачи с указанным распределением
CREATE TABLE task AS
WITH aid AS (
  SELECT
    id
  , array_agg((random() * 999)::integer + 1) aids
  FROM
    generate_series(1, 1000) id
  , generate_series(1, 20)
  GROUP BY
    1
)
SELECT
  *
FROM
  (
    SELECT
      id
    , '2020-01-01'::date - (random() * 1e3)::integer task_date
    , (random() * 999)::integer + 1 owner_id
    FROM
      generate_series(1, 100000) id
  ) T
, LATERAL(
    SELECT
      aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
    FROM
      aid
    WHERE
      id = T.owner_id
    LIMIT 1
  ) a;

ALTER TABLE task ADD PRIMARY KEY(id);
CREATE INDEX ON task(owner_id, task_date);
CREATE INDEX ON task(author_id);

Andeha hasehontsika ireo asa 100 farany ho an'ny mpanatanteraka manokana:

SELECT
  task.*
, person.name
FROM
  task
LEFT JOIN
  person
    ON person.id = task.author_id
WHERE
  owner_id = 777
ORDER BY
  task_date DESC
LIMIT 100;

[jereo ao amin'ny explain.tensor.ru]

Mipoitra izany 1/3 ny fotoana manontolo ary 3/4 ny famakiana pejin'ny angon-drakitra dia natao fotsiny mba hikaroka ny mpanoratra in-100 - ho an'ny asa famoahana tsirairay. Fa fantatsika fa amin'ireo an-jatony ireo 20 ihany no samy hafa - Azo atao ve ny mampiasa izany fahalalana izany?

hstore-dictionary

Andao hanararaotra karazana hstore mba hamoronana sanda fototra "rakibolana":

CREATE EXTENSION hstore

Mila mametraka ny karapanondron'ny mpanoratra sy ny anarany ao amin'ny rakibolana fotsiny isika mba hahafahantsika mamoaka amin'ny fampiasana ity fanalahidy ity:

-- формируем целевую выборку
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- формируем словарь для уникальных значений
, dict AS (
  SELECT
    hstore( -- hstore(keys::text[], values::text[])
      array_agg(id)::text[]
    , array_agg(name)::text[]
    )
  FROM
    person
  WHERE
    id = ANY(ARRAY(
      SELECT DISTINCT
        author_id
      FROM
        T
    ))
)
-- получаем связанные значения словаря
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
  T;

[jereo ao amin'ny explain.tensor.ru]

Lany tamin'ny fahazoana vaovao momba ny olona In-2 heny ny fotoana ary in-7 ny angona novakiana! Ankoatra ny "voambolana", ny nanampy anay hahatratra ireo vokatra ireo dia famoriam-bola betsaka avy amin'ny latabatra amin'ny alàlan'ny fampiasana = ANY(ARRAY(...)).

Fidirana an-databatra: Serialization sy Deserialization

Ahoana anefa raha mila mitahiry tsy saha lahatsoratra iray fotsiny isika, fa fidirana iray manontolo ao amin'ny rakibolana? Amin'ity tranga ity, ny fahaizan'ny PostgreSQL dia hanampy antsika Raiso ho sanda tokana ny fidirana an-databatra:

...
, dict AS (
  SELECT
    hstore(
      array_agg(id)::text[]
    , array_agg(p)::text[] -- магия #1
    )
  FROM
    person p
  WHERE
    ...
)
SELECT
  *
, (((TABLE dict) -> author_id::text)::person).* -- магия #2
FROM
  T;

Andeha hojerentsika ny zava-nitranga teto:

1. Noraisinay p ho solon'ny anarana ho an'ny fidirana latabatra olona feno ary nanangona ny laharan'ireo.
2. izany naverina naverina indray ny firaketana an-tsoratra mankany amin'ny andiana tady lahatsoratra (olona[]::text[]) mba hametrahana azy ao amin'ny rakibolana hstore ho sanda maromaro.
3. Rehefa mahazo firaketana mifandraika izahay dia nalaina avy amin'ny rakibolana tamin'ny fanalahidy ho tady lahatsoratra.
4. Mila lahatsoratra isika mivadika ho sanda karazana latabatra olona (ho an'ny latabatra tsirairay dia misy karazana anarana mitovy dia noforonina ho azy).
5. "Hanitatra" ny rakitsoratra nosoratana ho tsanganana mampiasa (...).*.

rakibolana json

Saingy tsy hahomby ny fika tahaka ny nampiharina etsy ambony raha tsy misy karazana latabatra mifanaraka amin'izany hanaovana ny "casting". Ny toe-javatra mitovy amin'izany ihany no hipoitra, ary raha manandrana mampiasa isika laharana CTE fa tsy latabatra "tena izy"..

Amin'ity tranga ity dia hanampy antsika izy ireo miasa amin'ny json:

...
, p AS ( -- это уже CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- теперь это уже json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- и внутри json для каждой строки
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) ->> author_id::text)::json -- извлекли из словаря как json
      ) AS j(name text, birth_date date) -- заполнили нужную нам структуру
  ) j;

Marihina fa rehefa mamaritra ny rafitra kendrena dia tsy afaka mitanisa ny saha rehetra amin'ny tady loharano, fa ireo izay tena ilaintsika ihany. Raha manana latabatra "teratany" isika, dia tsara kokoa ny mampiasa ny fiasa json_populate_record.

Mbola miditra amin'ny rakibolana indray mandeha izahay, saingy json-[de]be ny vidin'ny serialization, noho izany dia ara-drariny ny fampiasana an'io fomba io raha tsy amin'ny toe-javatra sasany rehefa miseho ho ratsy kokoa ny CTE Scan "marina".

Fampisehoana fitsapana

Noho izany, nahazo fomba roa izahay hametahana angon-drakitra ao anaty rakibolana - hstore/json_object. Ho fanampin'izany, ny laharan'ny fanalahidy sy ny soatoavina dia azo amboarina amin'ny fomba roa ihany koa, miaraka amin'ny fiovam-po anatiny na ivelany amin'ny lahatsoratra: array_agg(i::text) / array_agg(i)::text[].

Andeha hojerentsika ny fahombiazan'ny karazana serialization amin'ny fampiasana ohatra synthetic fotsiny - serialise isan-karazany ny fanalahidy:

WITH dict AS (
  SELECT
    hstore(
      array_agg(i::text)
    , array_agg(i::text)
    )
  FROM
    generate_series(1, ...) i
)
TABLE dict;

Evaluation script: serialization

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  hstore(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              TABLE dict
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  ,   LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

Ao amin'ny PostgreSQL 11, hatramin'ny haben'ny rakibolana amin'ny fanalahidy 2^12 maka fotoana kely kokoa ny serialization amin'ny json. Amin'ity tranga ity, ny tena mahomby dia ny fitambaran'ny json_object sy ny karazana fiovam-po "anatiny". array_agg(i::text).

Andeha hojerentsika in-8 ny sandan'ny fanalahidy tsirairay - raha ny marina, raha tsy miditra amin'ny rakibolana ianao, nahoana no ilaina izany?

Evaluation script: famakiana avy amin'ny rakibolana

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  json_object(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              SELECT
                (TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text
              FROM
                generate_series(1, $$ || (1 << (v + 3)) || $$) i
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  , LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

Ary... efa eo ho eo miaraka amin'ny fanalahidy 2^6, manomboka very imbetsaka ny famakiana avy amin'ny rakibolana json famakiana avy amin'ny hstore, ho an'ny jsonb dia mitranga amin'ny 2^9.

Fehin-kevitra farany:

• raha mila manao izany ianao MIARAKA amin'ny firaketana miverimberina maro - tsara kokoa ny mampiasa "diksionera" amin'ny latabatra
• raha andrasana ny rakibolanao kely ary tsy hovakianao firy - azonao ampiasaina json[b]
• amin'ny tranga hafa rehetra hstore + array_agg(i::text) dia hahomby kokoa

Source: www.habr.com