ProHoster > بلاګ > اداره > د پوسټګر ایس کیو ایل ضد نمونې: قاموس هیټ هیوی جوین

د پوسټګر ایس کیو ایل ضد نمونې: قاموس هیټ هیوی جوین

موږ د مقالو لړۍ ته دوام ورکوو چې د لږ پیژندل شوي لارو مطالعې ته وقف شوي ترڅو د "ظاهر ساده" PostgreSQL پوښتنو فعالیت ښه کړي:

فکر مه کوئ چې زه دومره ګډون نه خوښوم ... :)

مګر ډیری وختونه پرته له دې، غوښتنه د هغې په پرتله د پام وړ ډیر ګټور وي. نو نن به موږ هڅه وکړو د منابعو ژور شمولیت څخه ځان خلاص کړئ - د لغت کارول.

د پوسټګر ایس کیو ایل ضد نمونې: قاموس هیټ هیوی جوین

د 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;

د پوسټګر ایس کیو ایل ضد نمونې: قاموس هیټ هیوی جوین
[ تشریح.tensor.ru ته وګورئ]

دا دا معلومه شوه 1/3 ټول وخت او 3/4 لوستل د معلوماتو پاڼې یوازې 100 ځله د لیکوال لټون لپاره رامینځته شوي - د هر محصول کار لپاره. مګر موږ پوهیږو چې د دې سلګونو په منځ کې یوازې 20 مختلف - ایا دا ممکنه ده چې دا پوهه وکاروئ؟

hstore-dictionary

راځئ چې ګټه ترې واخلو hstore ډول د "لغت" کلیدي ارزښت رامینځته کولو لپاره:

CREATE EXTENSION hstore

موږ یوازې اړتیا لرو چې د لیکوال ID او د هغه نوم په قاموس کې واچوو ترڅو موږ وکولی شو د دې کلیمې په کارولو سره استخراج کړو:

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

د پوسټګر ایس کیو ایل ضد نمونې: قاموس هیټ هیوی جوین
[ تشریح.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 سکین خپل ځان خراب ښکاره کړي.

د فعالیت ازموینه

نو، موږ په قاموس کې د ډیټا سیریل کولو لپاره دوه لارې ترلاسه کړې - 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 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;

د پوسټګر ایس کیو ایل ضد نمونې: قاموس هیټ هیوی جوین

او ... لا دمخه تقریبا د 2^6 کلیدونو سره، د json لغت څخه لوستل څو ځله له لاسه ورکول پیل کوي د hstore څخه لوستل، د jsonb لپاره ورته په 2^9 کې پیښیږي.

وروستۍ پایلې:

  • که تاسو ورته اړتیا لرئ د ډیری تکرار ریکارډونو سره یوځای شئ - دا غوره ده چې د میز "لغت" وکاروئ
  • که ستاسو لغت تمه کیږي کوچنی او تاسو به له هغې څخه ډیر څه ونه لوستل شي - تاسو کولی شئ json وکاروئ [b]
  • په نورو ټولو قضیو کې hstore + array_agg(i::text) ډیر اغیزمن به وي

سرچینه: www.habr.com

Add a comment