ፕሮሆስተር > ጦማር > አስተዳደር > PostgreSQL Antipatterns፡ መዝገበ ቃላት ከባድ ይቀላቀሉ

PostgreSQL Antipatterns፡ መዝገበ ቃላት ከባድ ይቀላቀሉ

“ቀላል የሚመስሉ” የPostgreSQL መጠይቆችን አፈጻጸም ለማሻሻል ብዙም ያልታወቁ መንገዶችን ለማጥናት የታቀዱ ተከታታይ መጣጥፎችን እንቀጥላለን፡

መቀላቀልን በጣም የማልወደው እንዳይመስልህ… :)

ግን ብዙውን ጊዜ ያለ እሱ ፣ ጥያቄው ከእሱ ጋር ካለው የበለጠ ፍሬያማ ይሆናል። ስለዚህ ዛሬ እንሞክራለን ከሀብት-ተኮር JOIN ያስወግዱ - መዝገበ ቃላት በመጠቀም።

PostgreSQL Antipatterns፡ መዝገበ ቃላት ከባድ ይቀላቀሉ

ከ PostgreSQL 12 ጀምሮ፣ ከዚህ በታች የተገለጹት አንዳንድ ሁኔታዎች በምክንያት ትንሽ ለየት ባለ መልኩ ሊባዙ ይችላሉ። ነባሪ ቁሳዊ ያልሆነ CTE. ቁልፉን በመግለጽ ይህ ባህሪ መመለስ ይቻላል MATERIALIZED.

ብዙ “እውነታዎች” በተወሰነ መዝገበ-ቃላት ውስጥ

በጣም እውነተኛ የመተግበሪያ ተግባር እንውሰድ - ዝርዝር ማሳየት አለብን ገቢ መልዕክቶች ወይም ከላኪዎች ጋር ያሉ ንቁ ተግባራት፡-

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

በረቂቅ ዓለም ውስጥ፣ የተግባር ፀሐፊዎች በሁሉም የድርጅታችን ሠራተኞች መካከል በእኩልነት መሰራጨት አለባቸው፣ ግን በእውነቱ ተግባሮች እንደ አንድ ደንብ ፣ በትክክል ከተወሰኑ ሰዎች ይመጣሉ - “ከአስተዳደር” እስከ ተዋረድ ወይም “ከንዑስ ተቋራጮች” ከአጎራባች ዲፓርትመንቶች (ተንታኞች ፣ ዲዛይነሮች ፣ ግብይት ፣ ...)።

1000 ሰዎች ባሉበት ድርጅታችን ውስጥ 20 ደራሲዎች ብቻ (በተለምዶ እንዲያውም ያነሰ) ለእያንዳንዱ የተለየ ፈጻሚ እና ተግባር ያዘጋጃሉ። ይህንን ርዕሰ ጉዳይ እውቀት እንጠቀምበት"ባህላዊ" ጥያቄን ለማፋጠን.

ስክሪፕት ጀነሬተር

-- сотрудники
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);

ለአንድ የተወሰነ አስፈፃሚ የመጨረሻዎቹን 100 ተግባራት እናሳይ፡-

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፡ መዝገበ ቃላት ከባድ ይቀላቀሉ
[ማብራሪያውን ይመልከቱ.tensor.ru]

እንደ እውነቱ ነው 1/3 ጠቅላላ ጊዜ እና 3/4 ንባቦች የውሂብ ገጾች ደራሲውን 100 ጊዜ ለመፈለግ ብቻ ተደርገዋል - ለእያንዳንዱ የውጤት ተግባር። ግን ከእነዚህ በመቶዎች መካከል ያንን እናውቃለን ብቻ 20 የተለያዩ - ይህን እውቀት መጠቀም ይቻላል?

hstore-መዝገበ-ቃላት

እንጠቀምበት hstore አይነት የ"መዝገበ ቃላት" ቁልፍ እሴት ለማመንጨት፡-

CREATE EXTENSION hstore

ይህንን ቁልፍ ተጠቅመን ማውጣት እንድንችል የደራሲውን መታወቂያ እና ስሙን መዝገበ ቃላት ውስጥ ማስገባት ብቻ ያስፈልገናል፡-

-- формируем целевую выборку
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;

PostgreSQL Antipatterns፡ መዝገበ ቃላት ከባድ ይቀላቀሉ
[ማብራሪያውን ይመልከቱ.tensor.ru]

ስለ ሰዎች መረጃ ለማግኘት ወጪ 2 ጊዜ ያነሰ ጊዜ እና 7 ጊዜ ያነሰ ውሂብ ማንበብ! "ከቃላት ዝርዝር" በተጨማሪ እነዚህን ውጤቶች እንድናገኝ የረዳን ነገር ነው። የጅምላ መዝገብ ሰርስሮ ማውጣት በመጠቀም በአንድ ማለፊያ ውስጥ ከጠረጴዛው = ANY(ARRAY(...)).

የሰንጠረዥ ግቤቶች፡ ተከታታይነት እና ማሰናከል

ግን አንድ የጽሑፍ መስክ ብቻ ሳይሆን በመዝገበ-ቃላቱ ውስጥ ያለውን ሙሉ ግቤት ማስቀመጥ ብንፈልግስ? በዚህ አጋጣሚ የ PostgreSQL ችሎታ ይረዳናል። የሰንጠረዥ ግቤትን እንደ አንድ ነጠላ እሴት ይያዙ:

...
, 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;

እዚህ ምን እየተካሄደ እንዳለ እንመልከት፡-

  1. ወሰድን p እንደ ሙሉ ሰው የጠረጴዛ መግቢያ ተለዋጭ ስም ከእነርሱም ብዙ አሰባሰበ።
  2. ይህ የቀረጻው ድርድር እንደገና ተቀርጿል። በ hstore መዝገበ ቃላት ውስጥ እንደ የእሴቶች ድርድር ለማስቀመጥ ወደ ብዙ የጽሑፍ ሕብረቁምፊዎች (ሰው[]:: ጽሑፍ[])።
  3. ተዛማጅ መዝገብ ስንቀበል, እኛ ከመዝገበ-ቃላቱ በቁልፍ ተወስዷል እንደ የጽሑፍ ሕብረቁምፊ።
  4. ጽሑፍ እንፈልጋለን ወደ የሰንጠረዥ አይነት እሴት ይለውጡ ሰው (ለእያንዳንዱ ጠረጴዛ አንድ አይነት ስም በራስ-ሰር ይፈጠራል።
  5. በመጠቀም የተተየበው መዝገብ ወደ አምዶች "ዘርጋ" (...).*.

json መዝገበ ቃላት

ነገር ግን "መውሰድን" ለማድረግ ምንም አይነት ተጓዳኝ የጠረጴዛ አይነት ከሌለ ከላይ እንደተተገበርነው እንዲህ ያለው ዘዴ አይሰራም. በትክክል ተመሳሳይ ሁኔታ ይነሳል, እና ለመጠቀም ከሞከርን የCTE ረድፍ እንጂ “እውነተኛ” ሠንጠረዥ አይደለም።.

በዚህ ሁኔታ እነሱ ይረዱናል ከ 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;

የዒላማውን መዋቅር ስንገልጽ ሁሉንም የምንጭ ሕብረቁምፊ መስኮች መዘርዘር አንችልም, ነገር ግን እኛ በእርግጥ የሚያስፈልጉን ብቻ መሆኑን ልብ ሊባል ይገባል. "ቤተኛ" ሰንጠረዥ ካለን, ተግባሩን መጠቀም የተሻለ ነው json_populate_record.

አሁንም መዝገበ ቃላቱን አንድ ጊዜ እንደርስበታለን፣ ግን json-[de] ተከታታይ ወጪዎች በጣም ከፍተኛ ናቸው።ስለዚህ, "ሐቀኛ" CTE Scan እራሱን በከፋ ሁኔታ ሲያሳይ ይህንን ዘዴ በአንዳንድ ሁኔታዎች ብቻ መጠቀም ምክንያታዊ ነው.

የሙከራ አፈጻጸም

ስለዚህ፣ ውሂብን ወደ መዝገበ ቃላት ለመደርደር ሁለት መንገዶች አግኝተናል- hstore/json_ነገር. በተጨማሪም ፣ የቁልፎች እና የእሴቶች እራሳቸው በሁለት መንገዶች ሊፈጠሩ ይችላሉ ፣ ከውስጣዊም ሆነ ከውጭ ወደ ጽሑፍ መለወጥ ። array_agg(i:: ጽሑፍ) / array_agg(i): ጽሑፍ[].

የተጣራ ሰራሽ ምሳሌን በመጠቀም የተለያዩ የመለያ ዓይነቶችን ውጤታማነት እንፈትሽ - የተለያዩ የቁልፍ ቁጥሮችን ተከታታይ ያድርጉ:

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

የግምገማ ስክሪፕት፡ ተከታታይነት

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;

PostgreSQL Antipatterns፡ መዝገበ ቃላት ከባድ ይቀላቀሉ

በ PostgreSQL 11 ላይ፣ እስከ መዝገበ ቃላት መጠን በግምት 2^12 ቁልፎች ወደ json ተከታታይ ማድረግ ትንሽ ጊዜ ይወስዳል. በዚህ አጋጣሚ በጣም ውጤታማ የሆነው የ json_object እና የ"ውስጣዊ" አይነት ልወጣ ጥምረት ነው። array_agg(i::text).

አሁን የእያንዳንዱን ቁልፍ ዋጋ 8 ጊዜ ለማንበብ እንሞክር - ከሁሉም በኋላ, መዝገበ ቃላትን ካልደረስክ, ለምን አስፈለገ?

የግምገማ ስክሪፕት፡ ከመዝገበ ቃላት ማንበብ

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;

PostgreSQL Antipatterns፡ መዝገበ ቃላት ከባድ ይቀላቀሉ

እና ... ቀድሞውኑ በግምት በ2^6 ቁልፎች፣ ከ json መዝገበ ቃላት ማንበብ ብዙ ጊዜ ማጣት ይጀምራል ከ hstore ማንበብ፣ ለ jsonb ተመሳሳይ የሆነው በ2^9 ነው።

የመጨረሻ መደምደሚያዎች፡-

  • ማድረግ ካስፈለገዎት በበርካታ ተደጋጋሚ መዝገቦች ይቀላቀሉ - የሰንጠረዡን "መዝገበ-ቃላት" መጠቀም የተሻለ ነው
  • መዝገበ ቃላትዎ የሚጠበቅ ከሆነ ትንሽ እና ከእሱ ብዙ ማንበብ አይችሉም - json [b] መጠቀም ይችላሉ
  • በሌሎች በሁሉም ጉዳዮች hstore + array_agg(i:: ጽሑፍ) የበለጠ ውጤታማ ይሆናል

ምንጭ: hab.com

አስተያየት ያክሉ