ProHoster > Blogi > Haldamine > PostgreSQL Antipatterns: lööme sÔnaraamiga rasket JOINi

PostgreSQL Antipatterns: lööme sÔnaraamiga rasket JOINi

JĂ€tkame artiklite seeriat, mis on pĂŒhendatud vĂ€he tuntud meetodite uurimisele, mis parandavad PostgreSQL-is "nĂ€iliselt lihtsate" pĂ€ringute jĂ”udlust:

Ärge arvake, et ma ei armasta JOIN-i nii vĂ€ga
 🙂

Kuid sageli on pĂ€ring ilma selleta mĂ€rgatavalt tootlikum kui sellega. SeetĂ”ttu proovime tĂ€na ĂŒldse vabaneda ressursimahukast JOIN-ist — abil sĂ”nastikust.

PostgreSQL Antipatterns: lööme sÔnaraamiga rasket JOINi

Alates PostgreSQL 12-st vÔivad allpool kirjeldatud olukorrad pisut teisiti kÀituda, kuna CTE ei ole vaikimisi materialiseeritud. Seda kÀitumist saab tagasi tuua endise jÀrgi, mÀÀrates vÔtme MATERIALIZED.

Palju "fakte" piiratud sÔnastiku kohta

VĂ”tame tĂ€iesti reaalse rakendusĂŒlesande — peame vĂ€ljastama loendi saabuvatest sĂ”numitest vĂ”i aktiivsetest ĂŒlesannetest saatjatelt:

25.01 | Ivanov I.I. | Uue algoritmi kirjelduse ettevalmistamine.
22.01 | Ivanov I.I. | Artikkel kirjutamine Habris: elu ilma JOIN-ita.
20.01 | Petrov P.P. | Aita pÀringut optimeerida.
18.01 | Ivanov I.I. | Artikkel kirjutamine Habris: JOIN andmete jaotuse arvestamisel.
16.01 | Petrov P.P. | Aita pÀringut optimeerida.

Kujuteldavas maailmas peaks ĂŒlesannete autorid olema vĂ”rdselt jaotatud kĂ”igi meie organisatsiooni töötajate vahel, kuid tegelikkuses saavad ĂŒlesanded tavaliselt piisavalt piiratud hulga inimestelt — "ĂŒlemuselt" hierarhia kaudu vĂ”i "kĂ”rvallaste" kĂ”rvalosakondadelt (analĂŒĂŒtikud, disainerid, turundus, ...).

Oletame, et meie organisatsioonis on 1000 inimesest ainult 20 autorit (tavaliselt isegi vĂ€hem), kes esivad ĂŒlesandeid iga konkreetse tĂ€itja kohta ja kasutame seda teemaske tundmist, et kiirendada "traditsioonilist" pĂ€ringut.

Skriptigeneraator

-- töötajad
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);

-- ĂŒlesanded mÀÀratud jaotuse jĂ€rgi
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);

NĂ€itame viimased 100 ĂŒlesannet konkreetse tĂ€itja jaoks:

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;

PostgreSQL Antipatterns: lööme sÔnaraamiga rasket JOINi
[vaata explain.tensor.ru]

Selgub, et 1/3 kogu ajast ja 3/4 lugemist andmete lehekĂŒlgi tehti vaid selleks, et 100 korda otsida autorit — iga vĂ€ljundatud ĂŒlesande jaoks. Kuid me teame, et nende sajapuhul on kokku 20 erinevat — kas ei saaks seda teadmist kasutada?

hstore-sÔnastik

Kasutame tĂŒĂŒp hstore vĂ”tme-vÀÀrtuse "sĂ”nastiku" genereerimiseks:

CREATE EXTENSION hstore

SÔnastikku piisab paigutada autori ID ja tema nimi, et hiljem saaksime selle vÔtme jÀrgi vÀlja vÔtta:

-- loome sihtvaliku
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- loome sÔnastiku ainulaadsete vÀÀrtuste jaoks
, 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
    ))
)
-- saame seotud vÀÀrtused sÔnastikust
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
  T;

PostgreSQL Antipatterns: lööme sÔnaraamiga rasket JOINi
[vaata explain.tensor.ru]

Inimeste info saamiseks kulus kaks korda vĂ€hem aega ja seitsme korra vĂ€hem andmeid loeti! Aaside „sĂ”nastikustamise“ kĂ”rval aitas meid saavutada ka massiline rekordite tĂ”mbamine tabelist ĂŒheainsa lĂ€bimisega kasutades = ANY(ARRAY(...)).

Tabeli salvestused: serialiseerimine ja deserialiseerimine

Aga mis siis, kui peame sĂ”nastikus salvestama mitte ĂŒhte tekstivĂ€li, vaid terve kirje? Sel juhul aitab meid PostgreSQL töötada tabeli kirjet kui ĂŒhtset vÀÀrtust:

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

Vaatame, mis siin ĂŒldse toimus:

  1. Me vÔtsime p kui alias tÀis tabeli kirjele person ja kogusime neist massiivi.
  2. See massive records have been cast into an array of text strings (person[]::text[]) to place it in the hstore dictionary as an array of values.
  3. When retrieving the related record, we extracted it from the dictionary by key as a text string.
  4. We need to convert the text into a table type value person (for each table, a named type is created automatically).
  5. We "expanded" the typed record into columns using (...).*.

json dictionary

However, such a trick, as we applied above, will not work if there is no corresponding table type to perform the "cast." The exact same situation will arise if we try to use a CTE string instead of a "real" table.

In this case, we will be helped by functions for working with json:

... 
, p AS ( -- this is already CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- now this is already json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- and inside json for each row
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) ->> author_id::text)::json -- extracted from the dictionary as json
      ) AS j(name text, birth_date date) -- filled in the structure we need
  ) j;

It should be noted that when describing the target structure, we can list not all fields of the source string, but only those that we really need. If we have a "native" table, it is better to use the function json_populate_record.

Accessing the dictionary still happens in a single manner, but the costs of json-[de]serialization are quite high, so this method is reasonable to use only in certain cases when the "honest" CTE Scan performs worse.

Testing performance

So, we have obtained two methods of serializing data into a dictionary — hstore / json_object. In addition, the key and value arrays can also be generated in two ways, with internal or external conversion to text: array_agg(i::text) / array_agg(i)::text[].

Let's check the efficiency of different types of serialization on a purely synthetic example — serializing different numbers of keys:

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

Estimation script: serialization

T-ga (
  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;

PostgreSQL Antipatterns: lööme sÔnaraamiga rasket JOINi

PostgreSQL 11 puhul umbes 2^12 vĂ”tme sĂ”nastiku suuruses JSON-ks serialiseerimine vĂ”tab vĂ€hem aega. Selle kĂ”ige tĂ”husam kombineerimine on json_object ja "sise" tĂŒĂŒpide konversioon array_agg(i::text).

NĂŒĂŒd proovime lugeda iga vĂ”tme vÀÀrtust 8 korda — kui sĂ”nastikku ei kutsuta, siis miks see on vajalik?

Hinnanguline skript: lugemine sÔnastikust

T-ga (
  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 < (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;

PostgreSQL Antipatterns: lööme sÔnaraamiga rasket JOINi

Ja... umbes 2^6 vÔtme korral algab lugemine json-sÔnastikust hstore'ist kordades halvem jsonb puhul juhtub sama 2^9 juures.

LÔplikud jÀreldused:

  • kui on vaja teha JOIN korduvalt esinevate kirjetega — on parem kasutada tabeli "sĂ”nastikuks" muundamist
  • kui teie sĂ”nastik on oodatud vĂ€ike ja lugeda te seda natuke — vĂ”ib kasutada json[b]
  • kĂ”ikides muudes olukordades hstore + array_agg(i::text) on tĂ”husam

Allikas: habr.com

Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne hostimine veebilehtede jaoks DDoS-i kaitsega, VPS VDS serverid | ProHoster