PostgreSQL ਐਂਟੀਪੈਟਰਨ: CTE x CTE

ਮੇਰੇ ਕੰਮ ਦੀ ਲਾਈਨ ਦੇ ਕਾਰਨ, ਮੈਨੂੰ ਉਹਨਾਂ ਸਥਿਤੀਆਂ ਨਾਲ ਨਜਿੱਠਣਾ ਪੈਂਦਾ ਹੈ ਜਦੋਂ ਇੱਕ ਡਿਵੈਲਪਰ ਇੱਕ ਬੇਨਤੀ ਲਿਖਦਾ ਹੈ ਅਤੇ ਸੋਚਦਾ ਹੈ "ਅਧਾਰ ਚੁਸਤ ਹੈ, ਇਹ ਸਭ ਕੁਝ ਆਪਣੇ ਆਪ ਸੰਭਾਲ ਸਕਦਾ ਹੈ!«

ਕੁਝ ਮਾਮਲਿਆਂ ਵਿੱਚ (ਅੰਸ਼ਕ ਤੌਰ 'ਤੇ ਡੇਟਾਬੇਸ ਦੀਆਂ ਸਮਰੱਥਾਵਾਂ ਦੀ ਅਗਿਆਨਤਾ ਤੋਂ, ਅੰਸ਼ਕ ਤੌਰ 'ਤੇ ਸਮੇਂ ਤੋਂ ਪਹਿਲਾਂ ਅਨੁਕੂਲਤਾਵਾਂ ਤੋਂ), ਇਹ ਪਹੁੰਚ "ਫ੍ਰੈਂਕਨਸਟਾਈਨਜ਼" ਦੀ ਦਿੱਖ ਵੱਲ ਲੈ ਜਾਂਦੀ ਹੈ।

ਪਹਿਲਾਂ, ਮੈਂ ਅਜਿਹੀ ਬੇਨਤੀ ਦੀ ਇੱਕ ਉਦਾਹਰਣ ਦੇਵਾਂਗਾ:

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

ਕਿਸੇ ਬੇਨਤੀ ਦੀ ਗੁਣਵੱਤਾ ਦਾ ਅਸਲ ਮੁਲਾਂਕਣ ਕਰਨ ਲਈ, ਆਓ ਕੁਝ ਮਨਮਾਨੇ ਡੇਟਾ ਸੈਟ ਬਣਾਈਏ:

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

ਇਹ ਪਤਾ ਚਲਦਾ ਹੈ ਕਿ ਡੇਟਾ ਨੂੰ ਪੜ੍ਹਨ ਵਿੱਚ ਇੱਕ ਚੌਥਾਈ ਤੋਂ ਵੀ ਘੱਟ ਸਮਾਂ ਲੱਗਿਆ ਪੁੱਛਗਿੱਛ ਐਗਜ਼ੀਕਿਊਸ਼ਨ:

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਟੁਕੜੇ-ਟੁਕੜੇ ਕਰਕੇ ਇਸ ਨੂੰ ਵੱਖਰਾ ਕਰਨਾ

ਆਓ ਬੇਨਤੀ 'ਤੇ ਇੱਕ ਡੂੰਘਾਈ ਨਾਲ ਵਿਚਾਰ ਕਰੀਏ ਅਤੇ ਉਲਝਣ ਵਿੱਚ ਰਹੋ:

1. ਜੇਕਰ ਕੋਈ ਆਵਰਤੀ CTE ਨਹੀਂ ਹਨ ਤਾਂ ਇੱਥੇ WITH RECURSIVE ਕਿਉਂ ਹੈ?
2. ਇੱਕ ਵੱਖਰੇ CTE ਵਿੱਚ ਸਮੂਹ ਘੱਟੋ-ਘੱਟ/ਅਧਿਕਤਮ ਮੁੱਲ ਕਿਉਂ ਹਨ ਜੇਕਰ ਉਹ ਫਿਰ ਵੀ ਮੂਲ ਨਮੂਨੇ ਨਾਲ ਬੰਨ੍ਹੇ ਹੋਏ ਹਨ?
   +25% ਸਮਾਂ
3. ਪਿਛਲੇ CTE ਨੂੰ ਦੁਹਰਾਉਣ ਲਈ ਅੰਤ ਵਿੱਚ ਬਿਨਾਂ ਸ਼ਰਤ 'SELECT * FROM' ਦੀ ਵਰਤੋਂ ਕਿਉਂ ਕਰੀਏ?
   +14% ਸਮਾਂ

ਇਸ ਮਾਮਲੇ ਵਿੱਚ, ਅਸੀਂ ਬਹੁਤ ਖੁਸ਼ਕਿਸਮਤ ਸੀ ਕਿ ਕਨੈਕਸ਼ਨ ਲਈ Hash Join ਨੂੰ ਚੁਣਿਆ ਗਿਆ ਸੀ, ਨਾ ਕਿ Nested Loop, ਕਿਉਂਕਿ ਉਦੋਂ ਸਾਨੂੰ ਸਿਰਫ਼ ਇੱਕ CTE ਸਕੈਨ ਪਾਸ ਨਹੀਂ, ਸਗੋਂ 10K ਪ੍ਰਾਪਤ ਹੋਏ ਹੋਣਗੇ!

ਸੀਟੀਈ ਸਕੈਨ ਬਾਰੇ ਥੋੜ੍ਹਾ ਜਿਹਾਇੱਥੇ ਸਾਨੂੰ ਇਹ ਯਾਦ ਰੱਖਣਾ ਚਾਹੀਦਾ ਹੈ CTE ਸਕੈਨ Seq ਸਕੈਨ ਦੇ ਸਮਾਨ ਹੈ - ਭਾਵ, ਕੋਈ ਇੰਡੈਕਸਿੰਗ ਨਹੀਂ, ਪਰ ਸਿਰਫ ਇੱਕ ਪੂਰੀ ਖੋਜ, ਜਿਸਦੀ ਲੋੜ ਹੋਵੇਗੀ 10K x 0.3ms = 3000ms cte_max ਦੁਆਰਾ ਚੱਕਰ ਲਈ ਜ 1K x 1.5ms = 1500ms ਜਦੋਂ cte_bind ਦੁਆਰਾ ਲੂਪ ਕੀਤਾ ਜਾਂਦਾ ਹੈ!
ਅਸਲ ਵਿੱਚ, ਤੁਸੀਂ ਨਤੀਜੇ ਵਜੋਂ ਕੀ ਪ੍ਰਾਪਤ ਕਰਨਾ ਚਾਹੁੰਦੇ ਸੀ? ਹਾਂ, ਆਮ ਤੌਰ 'ਤੇ ਇਹ ਉਹ ਸਵਾਲ ਹੈ ਜੋ "ਤਿੰਨ-ਮੰਜ਼ਲਾ" ਸਵਾਲਾਂ ਦਾ ਵਿਸ਼ਲੇਸ਼ਣ ਕਰਨ ਦੇ 5ਵੇਂ ਮਿੰਟ ਵਿੱਚ ਕਿਤੇ ਆਉਂਦਾ ਹੈ।

ਅਸੀਂ ਹਰੇਕ ਵਿਲੱਖਣ ਕੁੰਜੀ ਜੋੜੇ ਲਈ ਆਉਟਪੁੱਟ ਕਰਨਾ ਚਾਹੁੰਦੇ ਸੀ key_a ਦੁਆਰਾ ਸਮੂਹ ਤੋਂ ਘੱਟੋ-ਘੱਟ/ਵੱਧ ਤੋਂ ਵੱਧ.
ਤਾਂ ਆਓ ਇਸਦੀ ਵਰਤੋਂ ਕਰੀਏ ਵਿੰਡੋ ਫੰਕਸ਼ਨ:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਕਿਉਂਕਿ ਦੋਵਾਂ ਵਿਕਲਪਾਂ ਵਿੱਚ ਡੇਟਾ ਨੂੰ ਪੜ੍ਹਨ ਵਿੱਚ ਲਗਭਗ 4-5ms ਲੱਗਦੇ ਹਨ, ਫਿਰ ਸਾਡਾ ਸਾਰਾ ਸਮਾਂ ਲਾਭ ਹੁੰਦਾ ਹੈ -32% - ਇਹ ਇਸਦੇ ਸ਼ੁੱਧ ਰੂਪ ਵਿੱਚ ਹੈ ਬੇਸ CPU ਤੋਂ ਲੋਡ ਹਟਾਇਆ ਗਿਆ, ਜੇਕਰ ਅਜਿਹੀ ਬੇਨਤੀ ਨੂੰ ਅਕਸਰ ਕਾਫ਼ੀ ਲਾਗੂ ਕੀਤਾ ਜਾਂਦਾ ਹੈ।

ਆਮ ਤੌਰ 'ਤੇ, ਤੁਹਾਨੂੰ ਬੇਸ ਨੂੰ "ਗੋਲ ਨੂੰ ਲੈ ਕੇ ਜਾਓ, ਵਰਗ ਨੂੰ ਰੋਲ ਕਰੋ" ਲਈ ਮਜਬੂਰ ਨਹੀਂ ਕਰਨਾ ਚਾਹੀਦਾ।

ਸਰੋਤ: www.habr.com