āĻāĻŽāĻžāĻ° āĻāĻžāĻā§āĻ° āĻ˛āĻžāĻāĻ¨ā§āĻ° āĻāĻžāĻ°āĻŖā§, āĻāĻŽāĻžāĻā§ āĻĒāĻ°āĻŋāĻ¸ā§āĻĨāĻŋāĻ¤āĻŋ āĻŽā§āĻāĻžāĻŦā§āĻ˛āĻž āĻāĻ°āĻ¤ā§ āĻšāĻŦā§ āĻ¯āĻāĻ¨ āĻāĻāĻāĻ¨ āĻŦāĻŋāĻāĻžāĻļāĻāĻžāĻ°ā§ āĻāĻāĻāĻŋ āĻ āĻ¨ā§āĻ°ā§āĻ§ āĻ˛ā§āĻā§āĻ¨ āĻāĻŦāĻ āĻŽāĻ¨ā§ āĻāĻ°ā§āĻ¨ "āĻŦā§āĻ¸ āĻ¸ā§āĻŽāĻžāĻ°ā§āĻ, āĻāĻāĻŋ āĻ¨āĻŋāĻā§āĻ āĻ¸āĻŦāĻāĻŋāĻā§ āĻ¸āĻžāĻŽāĻ˛āĻžāĻ¤ā§ āĻĒāĻžāĻ°ā§!ÂĢ
āĻāĻŋāĻā§ āĻā§āĻˇā§āĻ¤ā§āĻ°ā§ (āĻāĻāĻļāĻŋāĻāĻāĻžāĻŦā§ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§āĻ° āĻā§āĻˇāĻŽāĻ¤āĻž āĻ¸āĻŽā§āĻĒāĻ°ā§āĻā§ āĻ
āĻā§āĻāĻ¤āĻž āĻĨā§āĻā§, āĻāĻāĻļāĻŋāĻāĻāĻžāĻŦā§ āĻ
āĻāĻžāĻ˛ āĻ
āĻĒā§āĻāĻŋāĻŽāĻžāĻāĻā§āĻļāĻ¨ āĻĨā§āĻā§), āĻāĻ āĻĒāĻĻā§āĻ§āĻ¤āĻŋāĻāĻŋ "āĻĢā§āĻ°āĻžāĻā§āĻā§āĻ¨āĻ¸ā§āĻāĻžāĻāĻ¨āĻ¸" āĻāĻ° āĻā§āĻšāĻžāĻ°āĻžāĻ° āĻĻāĻŋāĻā§ āĻ¨āĻŋāĻ¯āĻŧā§ āĻ¯āĻžāĻ¯āĻŧāĨ¤
āĻĒā§āĻ°āĻĨāĻŽāĻ¤, āĻāĻŽāĻŋ āĻāĻ āĻ§āĻ°āĻ¨ā§āĻ° āĻāĻāĻāĻŋ āĻ
āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻāĻāĻāĻŋ āĻāĻĻāĻžāĻšāĻ°āĻŖ āĻĻā§āĻŦ:
-- Đ´ĐģŅ ĐēĐ°ĐļдОК ĐēĐģŅŅĐĩвОК ĐŋĐ°ŅŅ ĐŊĐ°Ņ
ОдиĐŧ Đ°ŅŅĐžŅииŅОваĐŊĐŊŅĐĩ СĐŊĐ°ŅĐĩĐŊиŅ ĐŋĐžĐģĐĩĐš
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);
āĻāĻāĻž āĻĻā§āĻāĻž āĻ¯āĻžāĻā§āĻā§ āĻ¯ā§ āĻĄā§āĻāĻž āĻĒāĻĄāĻŧāĻ¤ā§ āĻ¸āĻŽāĻ¯āĻŧ āĻ˛ā§āĻā§āĻā§ āĻāĻ āĻāĻ¤ā§āĻ°ā§āĻĨāĻžāĻāĻļā§āĻ°āĻ āĻāĻŽ āĻĒā§āĻ°āĻļā§āĻ¨ āĻ¸āĻŽā§āĻĒāĻžāĻĻāĻ¨:
āĻā§āĻāĻ°ā§ āĻā§āĻāĻ°ā§ āĻāĻ°ā§ āĻ¨ā§āĻāĻ¯āĻŧāĻž
āĻāĻ¸ā§āĻ¨ āĻ āĻ¨ā§āĻ°ā§āĻ§āĻāĻŋ āĻāĻ¨āĻŋāĻˇā§āĻ āĻāĻžāĻŦā§ āĻĻā§āĻāĻŋ āĻāĻŦāĻ āĻŦāĻŋāĻā§āĻ°āĻžāĻ¨ā§āĻ¤ āĻšāĻ:
- āĻ¯āĻĻāĻŋ āĻā§āĻ¨ āĻ°āĻŋāĻāĻžāĻ°ā§āĻ¸āĻŋāĻ CTE āĻ¨āĻž āĻĨāĻžāĻā§ āĻ¤āĻžāĻšāĻ˛ā§ āĻāĻāĻžāĻ¨ā§ RECURSIVE āĻā§āĻ¨?
- āĻā§āĻ¨ āĻāĻāĻāĻŋ āĻĒā§āĻĨāĻ CTE-āĻ¤ā§ āĻ¨ā§āĻ¯ā§āĻ¨āĻ¤āĻŽ/āĻ¸āĻ°ā§āĻŦā§āĻā§āĻ āĻŽāĻžāĻ¨āĻā§āĻ˛āĻŋāĻā§ āĻ¤āĻžāĻ°āĻĒāĻ°ā§ āĻŽā§āĻ˛ āĻ¨āĻŽā§āĻ¨āĻžāĻ° āĻ¸āĻžāĻĨā§ āĻ¸āĻāĻ¯ā§āĻā§āĻ¤ āĻāĻ°āĻž āĻšāĻ¯āĻŧ āĻā§āĻ¨?
+25% āĻ¸āĻŽāĻ¯āĻŧ - āĻā§āĻ¨ āĻĒā§āĻ°ā§āĻŦāĻŦāĻ°ā§āĻ¤ā§ CTE āĻĒā§āĻ¨āĻ°āĻžāĻŦā§āĻ¤ā§āĻ¤āĻŋ āĻāĻ°āĻ¤ā§ āĻļā§āĻˇā§ āĻāĻāĻāĻŋ āĻ¨āĻŋāĻāĻļāĻ°ā§āĻ¤ 'SELECT * FROM' āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻŦā§āĻ¨?
+14% āĻ¸āĻŽāĻ¯āĻŧ
āĻāĻ āĻā§āĻˇā§āĻ¤ā§āĻ°ā§, āĻāĻŽāĻ°āĻž āĻā§āĻŦ āĻāĻžāĻā§āĻ¯āĻŦāĻžāĻ¨ āĻ¯ā§ āĻ¸āĻāĻ¯ā§āĻā§āĻ° āĻāĻ¨ā§āĻ¯ āĻšā§āĻ¯āĻžāĻļ āĻāĻ¯āĻŧā§āĻ¨āĻā§ āĻŦā§āĻā§ āĻ¨ā§āĻāĻ¯āĻŧāĻž āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛, āĻ¨ā§āĻ¸ā§āĻā§āĻĄ āĻ˛ā§āĻĒ āĻ¨āĻ¯āĻŧ, āĻāĻžāĻ°āĻŖ āĻ¤āĻāĻ¨ āĻāĻŽāĻ°āĻž āĻā§āĻŦāĻ˛ āĻāĻāĻāĻŋ CTE āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨ āĻĒāĻžāĻ¸āĻ āĻĒā§āĻ¤āĻžāĻŽ āĻ¨āĻž, 10K!
CTE āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨ āĻ¸āĻŽā§āĻĒāĻ°ā§āĻā§ āĻāĻāĻā§āĻāĻāĻžāĻ¨ā§ āĻāĻŽāĻžāĻĻā§āĻ° āĻāĻāĻž āĻŽāĻ¨ā§ āĻ°āĻžāĻāĻ¤ā§ āĻšāĻŦā§ CTE āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨ Seq Scan āĻāĻ° āĻŽāĻ¤āĻ - āĻ
āĻ°ā§āĻĨāĻžā§, āĻā§āĻ¨ āĻāĻ¨ā§āĻĄā§āĻā§āĻ¸āĻŋāĻ āĻ¨āĻ¯āĻŧ, āĻļā§āĻ§ā§āĻŽāĻžāĻ¤ā§āĻ° āĻāĻāĻāĻŋ āĻ¸āĻŽā§āĻĒā§āĻ°ā§āĻŖ āĻ
āĻ¨ā§āĻ¸āĻ¨ā§āĻ§āĻžāĻ¨, āĻ¯āĻžāĻ° āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨ āĻšāĻŦā§ 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);
āĻ¯ā§āĻšā§āĻ¤ā§ āĻāĻāĻ¯āĻŧ āĻŦāĻŋāĻāĻ˛ā§āĻĒā§ āĻĄā§āĻāĻž āĻĒāĻĄāĻŧāĻ¤ā§ āĻāĻāĻ āĻāĻ¨ā§āĻŽāĻžāĻ¨āĻŋāĻ 4-5ms āĻ˛āĻžāĻā§, āĻ¤āĻžāĻ°āĻĒāĻ° āĻāĻŽāĻžāĻĻā§āĻ° āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻ¸āĻŽāĻ¯āĻŧ āĻ˛āĻžāĻ -32% - āĻāĻāĻŋ āĻ¤āĻžāĻ° āĻŦāĻŋāĻļā§āĻĻā§āĻ§āĻ¤āĻŽ āĻāĻāĻžāĻ°ā§ āĻŦā§āĻ¸ āĻ¸āĻŋāĻĒāĻŋāĻāĻ āĻĨā§āĻā§ āĻ˛ā§āĻĄ āĻ¸āĻ°āĻžāĻ¨ā§ āĻšāĻ¯āĻŧā§āĻā§, āĻ¯āĻĻāĻŋ āĻāĻ āĻ§āĻ°āĻ¨ā§āĻ° āĻ āĻ¨ā§āĻ°ā§āĻ§ āĻĒā§āĻ°āĻžāĻ¯āĻŧāĻ āĻāĻžāĻ°ā§āĻ¯āĻāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧāĨ¤
āĻ¸āĻžāĻ§āĻžāĻ°āĻŖāĻāĻžāĻŦā§, āĻāĻĒāĻ¨āĻžāĻ° āĻŦā§āĻ¸āĻāĻŋāĻā§ "āĻā§āĻ˛āĻžāĻāĻžāĻ°āĻāĻŋ āĻŦāĻšāĻ¨ āĻāĻ°āĻ¤ā§, āĻŦāĻ°ā§āĻāĻžāĻāĻžāĻ°āĻāĻŋ āĻ°ā§āĻ˛ āĻāĻ°āĻ¤ā§" āĻŦāĻžāĻ§ā§āĻ¯ āĻāĻ°āĻž āĻāĻāĻŋāĻ¤ āĻ¨āĻ¯āĻŧāĨ¤
āĻāĻ¤ā§āĻ¸: www.habr.com