پرو هوسٽر > بلاگ > انتظاميه > PostgreSQL Anti-Patterns: اچو ته ھٿ ھڻي ھڻي ھتي شامل ٿي ڊڪشنري سان

PostgreSQL Anti-Patterns: اچو ته ھٿ ھڻي ھڻي ھتي شامل ٿي ڊڪشنري سان

اسان مضمونن جو سلسلو جاري رکون ٿا جيڪي ”بظاهر سادو“ PostgreSQL سوالن جي ڪارڪردگي کي بهتر ڪرڻ لاءِ ٿورڙي سڃاتل طريقن جي مطالعي لاءِ وقف ڪيا ويا آهن:

اهو نه سوچيو ته مون کي ايترو پسند نه ڪيو شامل ٿيو ... :)

پر اڪثر ڪري ان کان سواء، درخواست ان جي ڀيٽ ۾ گهڻو وڌيڪ پيداوار ٿي سگهي ٿو. سو اڄ اسان ڪوشش ڪنداسين وسيلا-گھڻي شامل ٿيڻ کان نجات حاصل ڪريو - لغت استعمال ڪندي.

PostgreSQL Anti-Patterns: اچو ته ھٿ ھڻي ھڻي ھتي شامل ٿي ڊڪشنري سان

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 Anti-Patterns: اچو ته ھٿ ھڻي ھڻي ھتي شامل ٿي ڊڪشنري سان
[explanation.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 Anti-Patterns: اچو ته ھٿ ھڻي ھڻي ھتي شامل ٿي ڊڪشنري سان
[explanation.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. هن رڪارڊنگ جو سلسلو ٻيهر ڪيو ويو ٽيڪسٽ اسٽرنگ جي هڪ صف ڏانهن (شخص[]::text[]) ان کي 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 اسڪين پاڻ کي بدترين ڏيکاري ٿو.

جاچ ڪارڪردگي

تنهن ڪري، اسان کي هڪ لغت ۾ ڊيٽا کي ترتيب ڏيڻ لاء ٻه طريقا مليا آهن - hstore/json_object. ان کان علاوه، چاٻين ۽ قدرن جي صفن کي پڻ ٻن طريقن سان ٺاھي سگھجي ٿو، متن ۾ اندروني يا بيروني تبديلي سان: array_agg(i::text) / array_agg(i)::text[].

اچو ته هڪ خالص مصنوعي مثال استعمال ڪندي مختلف قسم جي سيريلائيزيشن جي اثرائتي چيڪ ڪريو. ڪنجي جي مختلف نمبرن کي ترتيب ڏيو:

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 Anti-Patterns: اچو ته ھٿ ھڻي ھڻي ھتي شامل ٿي ڊڪشنري سان

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 Anti-Patterns: اچو ته ھٿ ھڻي ھڻي ھتي شامل ٿي ڊڪشنري سان

۽... اڳ ۾ ئي لڳ ڀڳ 2^6 ڪنجين سان، json ڊڪشنري مان پڙهڻ سان ڪيترائي ڀيرا گم ٿيڻ شروع ٿئي ٿو hstore مان پڙهڻ، jsonb لاءِ ساڳيو ئي ٿئي ٿو 2^9.

حتمي نتيجا:

  • جيڪڏهن توهان کي ڪرڻ جي ضرورت آهي ڪيترن ئي ورجائي رڪارڊ سان شامل ٿيو - اهو بهتر آهي ته ٽيبل جي "ڊڪشنري" استعمال ڪريو
  • جيڪڏهن توهان جي لغت جي توقع آهي ننڍو ۽ توهان ان مان گهڻو نه پڙهي سگهندا - توھان استعمال ڪري سگھوٿا json[b]
  • ٻين سڀني ڪيسن ۾ hstore + array_agg (i::text) وڌيڪ اثرائتو ٿيندو

جو ذريعو: www.habr.com

تبصرو شامل ڪريو