ืขืืืขืืข ืืืฉืื ืฆืืจืืง - ืขืคื ืืืขื ืฆื PostgreSQL.
ืืืจ'ืืืข ืืขืืืืื ื ืขืก ืืืืขืจ 6000 ืืื ืืื ื ืืขืืึธืื, ืึธืืขืจ ืืืื ืขืจ ืคืื ืื ืืึทื ืืืง ืคึฟืขืึดืงืืืื ืงืขื ืืึธืื ื ืืืึธ ืึทื ื ืึธืืืืกื ืืื ืกืืจืึทืงืืฉืขืจืึทื ืงืืื, ืืืึธืก ืงืืง ืขืคึผืขืก ืืื ืืึธืก:
ืืขืจื ืฆื ืืื ืืื ืืืื ืจืืงืืืขืก ืืืขื "ืืืขืจื ืกืืืงื ืืืึทื". ๐
ืึธืืขืจ ืขืืขืก, ืคืืืข ืกืืืืึทืืืึธื ืก ืืืึธืก ืืึทืื ืึท ืืงืฉื ืคึผืึทืืขืืขื ืืื "ืืืึทืืึทื ืึทืก" ืืื ืืขืจืืื ืขื ืคืื ืจืขืกืืจืกื, ืืขื ืขื ืืืคึผืืฉ ืืื ืงืขื ืขื ืืืื ืื ืขืจืงืขื ื ืืืจื ืื ืกืืจืืงืืืจ ืืื ืืึทืื ืคืื ืืขื ืคึผืืึทื.
ืืื ืืขื ืคืึทื, ืืขืืขืจ ืืืื ืืขืืืขืืึธืคึผืขืจ ืืืขื ื ืืฉื ืืึธืื ืฆื ืงืืงื ืคึฟืึทืจ ืึทื ืึทืคึผืืึทืืึทืืืืฉืึทื ืึธืคึผืฆืืข ืืืืฃ ืืืื ืืืืื, ืจืืืืืื ื ืืืืื ืืืืฃ ืืืื ืืืืืขื ืข ืืขืจืคืึทืจืื ื - ืืืจ ืงืขื ืขื ืืึธืื ืืื ืืืึธืก ืืื ืืขืฉืขืขื ืืฉ ืืึธ, ืืืึธืก ืงืขื ืืืื ืื ืกืืื, ืืื ืืื ืฆื ืงืืืขื ืึทืจืืืฃ ืืื ืึท ืืืืืื ื. ืืืึธืก ืืื ืืืึธืก ืืืจ ืืึธืื ืืขืืื.
ืืื ืก ื ืขืืขื ืึท ื ืขืขื ืืขืจ ืงืืง ืืื ืื ืงืึทืกืขืก - ืืื ืืื ืืขื ืขื ืืืคืืื ื ืืื ืืืึธืก ืจืขืงืึทืืึทื ืืืืฉืึทื ื ืืื ืคืืจื ืฆื.
ืคึฟืึทืจ ืึท ืืขืกืขืจ ืืืืื ืืื ืืขืจ ืืขืืข, ืืืจ ืงืขื ืขื ืขืจืฉืืขืจ ืืขืจื ืฆื ืื ืงืึธืจืึทืกืคึผืึทื ืืื ื ืืืึธืง ืคึฟืื , ืืื ื ืึธืจ ืืขืืึธืื ืืืื ืฆื ืึท ืืืืืืื ืึทื ืึทืืืกืืก ืคืื ืืขืืขืจ ืืืึทืฉืคึผืื:
#1: ืืื ืืขืงืก "ืื ืืขืจืกืึธืจืืื ื"
ืืืขื ืืื
ืืืืึทืื ืื ืืขืฆืืข ืื ืืืืืก ืคึฟืึทืจ ืืขื ืงืืืขื ื "LLC Kolokolchik".
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
ืจืขืงืึทืืึทื ืืืืฉืึทื ื
ืืื ืืขืงืก ืืขื ืืฆื ืืงืกืคึผืึทื ื ืืื ืกืึธืจื ืคืขืืืขืจ.
ืืืึทืฉืคึผืื:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "ัะฐะบัะพะฒ"
, (random() * 1000)::integer fk_cli; -- 1K ัะฐะทะฝัั
ะฒะฝะตัะฝะธั
ะบะปััะตะน
CREATE INDEX ON tbl(fk_cli); -- ะธะฝะดะตะบั ะดะปั foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- ะพัะฑะพั ะฟะพ ะบะพะฝะบัะตัะฝะพะน ัะฒัะทะธ
ORDER BY
pk DESC -- ั
ะพัะธะผ ะฒัะตะณะพ ะพะดะฝั "ะฟะพัะปะตะดะฝัั" ะทะฐะฟะธัั
LIMIT 1;
ืืืจ ืงืขื ืขื ืืื ืืึทืืขืจืงื ืึทื ืืขืจ ืืื 100 ืจืขืงืึธืจืืก ืืขื ืขื ืกืึทืืืจืึทืงืืื ืืืจื ืื ืืื ืืขืงืก, ืืืึธืก ืืขื ืขื ืืขืืึธืื ืึทืืข ืืืืกืืขืฉืืขืื, ืืื ืืขืจ ืืืืื ืืืื ืขืจ ืืื ืืื ืงืก.
ืืืจ ืคืึทืจืจืืืื:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- ะดะพะฑะฐะฒะธะปะธ ะบะปัั ัะพััะธัะพะฒะบะธ
ืืคืืื ืืืืฃ ืึทืืึท ืคึผืจืืืืืืื ืืืกืืขืจ - 8.5 ืงืก ืคืึทืกืืขืจ ืืื 33 ืงืก ืืืืื ืืงืขืจืข ืืืืขื ืขื. ืื ืืืืจืงืื ื ืืืขื ืืืื ืงืืืจืขืจ, ืื ืืขืจ "ืคืืงืื" ืืืจ ืืึธืื ืคึฟืึทืจ ืืขืืขืจ ืืืขืจื. fk.
ืืื ืืึธื ืึทื ืึทืืึท ืึทื ืืื ืืขืงืก ืืืขื ืึทืจืืขืื ืืื ืึท "ืคึผืจืขืคืืงืก" ืืื ืืขืงืก ื ืื ืขืจืืขืจ ืืื ืื ืคืจืืขืจืืืงืข ืคึฟืึทืจ ืื ืืขืจืข ืงืืืืจืื ืืื fk, ืืื ืกืึธืจืืื ื ืืืจื pk ืืื ื ืืฉื ืืื ืืื ื ืืฉื (ืืืจ ืงืขื ื ืืืืขื ืขื ืืขืจ ืืืขืื ืืขื ). ืืื ืืึทืืื ืืขืจ, ืขืก ืืืขื ืฆืืฉืืขืื ื ืึธืจืืึทื ืืงืกืคึผืืืกืึทื ืคืจืขืื ืฉืืืกื ืฉืืืฆื ืืืจื ืืขื ืคืขืื.
#2: ืืื ืืขืงืก ืื ืืขืจืกืขืงืฉืึทื (ืืืืืึทืคึผืึทื ื)
ืืืขื ืืื
ืืืืึทืื ืึทืืข ืงืึทื ืืจืึทืงืฅ ืคึฟืึทืจ ืืขื ืงืืืขื ื "ืืืง ืงืึธืืึธืงืึธืืืฉืืง" ืืขืคืื ืขื ืืืืฃ ืืืืึทืฃ ืคืื "ื ืืืฉืกืง ืืืืืืง".
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanืจืขืงืึทืืึทื ืืืืฉืึทื ื
ืฉืึทืคึฟื ืงืึธืืคึผืึธืกืืืข ืืื ืืขืงืก ืืืจื ืคืขืืืขืจ ืคึฟืื ืืืืืข ืืงืืจ ืึธืืขืจ ืืงืกืคึผืึทื ื ืืืื ืขืจ ืคืื ืื ืืืืืกืืื ื ืคืขืืืขืจ ืคึฟืื ืื ืจืืข.
ืืืึทืฉืคึผืื:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "ัะฐะบัะพะฒ"
, (random() * 100)::integer fk_org -- 100 ัะฐะทะฝัั
ะฒะฝะตัะฝะธั
ะบะปััะตะน
, (random() * 1000)::integer fk_cli; -- 1K ัะฐะทะฝัั
ะฒะฝะตัะฝะธั
ะบะปััะตะน
CREATE INDEX ON tbl(fk_org); -- ะธะฝะดะตะบั ะดะปั foreign key
CREATE INDEX ON tbl(fk_cli); -- ะธะฝะดะตะบั ะดะปั foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- ะพัะฑะพั ะฟะพ ะบะพะฝะบัะตัะฝะพะน ะฟะฐัะต
ืืืจ ืคืึทืจืจืืืื:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
ืืึธ ืื ืืขืืืื ืก ืืื ืงืืขื ืขืจืขืจ, โโโโืืืืึทื ืืืืืึทืคึผ ืืขืึทืคึผ ืกืงืึทื ืืื ืืึทื ืฅ ืขืคืขืงืืืื ืืืืฃ ืืื. ืืืขืจ ืกืืึท ืืื ืกืืึท 7 ืงืก ืคืึทืกืืขืจ ืืื 2.5 ืงืก ืืืืื ืืงืขืจืข ืืืืขื ืขื.
#3: ืงืึทืืืืื ืื ื ืื ืืขืงืกืื (ืืืืืึทืคึผืึธืจ)
ืืืขื ืืื
ืืืืึทืื ืื ืขืจืฉืืขืจ 20 ืึธืืืืึทืกื "ืืืืื" ืึธืืขืจ ืึทื ืกืึทืกืืื ื ืจืืงืืืขืก ืคึฟืึทืจ ืคึผืจืึทืกืขืกืื ื, ืืื ืืืืืขื ืข ืืื ืืืืืขืจืงืืึทื.
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanืจืขืงืึทืืึทื ืืืืฉืึทื ื
ื ืืฅ ืืื ืืึทื [ืึทืืข] ืฆื ืคืึทืจืืื ืื ืกืืืงืืืขืจืื ืคึฟืึทืจ ืืขืืขืจ ืคืื ืื ืฆืืฉืืึทื ื ืึธืืขืจ ืืืึทืงืก.
ืืืึทืฉืคึผืื:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "ัะฐะบัะพะฒ"
, CASE
WHEN random() < 1::real/16 THEN NULL -- ั ะฒะตัะพััะฝะพัััั 1:16 ะทะฐะฟะธัั "ะฝะธััั"
ELSE (random() * 100)::integer -- 100 ัะฐะทะฝัั
ะฒะฝะตัะฝะธั
ะบะปััะตะน
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- ะธะฝะดะตะบั ั "ะฒัะพะดะต ะบะฐะบ ะฟะพะดั
ะพะดััะตะน" ัะพััะธัะพะฒะบะพะน
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- ัะฒะพะธ
fk_own IS NULL -- ... ะธะปะธ "ะฝะธััะธ"
ORDER BY
pk
, (fk_own = 1) DESC -- ัะฝะฐัะฐะปะฐ "ัะฒะพะธ"
LIMIT 20;
ืืืจ ืคืึทืจืจืืืื:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- ัะฝะฐัะฐะปะฐ "ัะฒะพะธ" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- ะฟะพัะพะผ "ะฝะธััะธ" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- ะฝะพ ะฒัะตะณะพ - 20, ะฑะพะปััะต ะธ ะฝะต ะฝะฐะดะพ
ืืืจ ืืึธืื ืืขื ืืฆื ืื ืคืึทืงื ืึทื ืึทืืข 20 ื ืืืืืง ืจืขืงืึธืจืืก ืืขื ืขื ืืืืื ืืืงืืืขื ืืื ืืขืจ ืขืจืฉืืขืจ ืืืึธืง, ืึทืืื ืื ืฆืืืืืืข, ืืื ืื ืืขืจ "ืืืึทืขืจ" ืืืืืึทืคึผ ืืขืึทืคึผ ืกืงืึทื, ืืื ื ืืฉื ืืคืืื ืขืงืกืึทืงืืืืึทื - ืืื ืึท ืจืขืืืืืึทื 22 ืงืก ืคืึทืกืืขืจ, 44 ืงืก ืืืืื ืืงืขืจืข ืืืืขื ืขื!
ื ืืขืจ ืืืืืืื ืืขืจืฆืืืืื ื ืืืขืื ืืขื ืึทืคึผืืึทืืึทืืืืฉืึทื ืืืคึฟื ืืืืฃ ืงืึธื ืงืจืขืืข ืืืืฉืคืืื ืงืขื ืขื ืืืื ืืืืขื ืขื ืืื ืึทืจืืืงืืขื ะธ .
ืืขื ืขืจืึทืืืืขื ืืืขืจืกืืข ืืืคืืืื ืกืขืืขืงืฆืืข ืืืจื ืขืืืขืืข ืฉืืืกืืขื (ืืื ื ืื ื ืึธืจ ืคึฟืึทืจ ืื ืงืึธื ืกื / ื ืื ืคึผืึธืจ) ืืื ืืืกืงืึทืกื ืืื ืืขื ืึทืจืืืงื .
#4: ืืืจ ืืืืขื ืขื ืฆื ืคืื
ืืืขื ืืื
ืืื ืึท ืืขืจืฉื, ืขืก ืึทืงืขืจื ืืืขื ืืืจ ืืืืื ืฆื "ืฆืืืฉืขืคึผืขื ืื ืื ืืขืจ ืคืืืืขืจ" ืฆื ืึท ืืืืืกืืื ื ืืงืฉื.
"ืืื ืืืจ ืืึธื ื ืื ืืึธืื ืื ืืขืืืข, ืึธืืขืจ ืืื ืคึผืขืจื ืงื ืขืคึผืืขื? ยป ืคืืื "ืืืึทืืึธื ื ืืึทื ื"
ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืืึธืืืคืืฆืืจื ืื ืึทืจืืขื ืืืืื, ืืืืึทืื ืื ืขืจืฉืืขืจ 20 ืึธืืืืึทืกื "ืงืจืืืืฉ" ืจืืงืืืขืก ืคึฟืึทืจ ืคึผืจืึทืกืขืกืื ื, ืจืึทืืึทืจืืืึทืก ืคืื ืืืืขืจ ืฆืื.
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 ร rows < RRbF -- ะพััะธะปัััะพะฒะฐะฝะพ >80% ะฟัะพัะธัะฐะฝะฝะพะณะพ
&& loops ร RRbF > 100 -- ะธ ะฟัะธ ััะพะผ ะฑะพะปััะต 100 ะทะฐะฟะธัะตะน ััะผะผะฐัะฝะพ
ืจืขืงืึทืืึทื ืืืืฉืึทื ื
ืฉืึทืคึฟื [ืืขืจ] ืกืคึผืขืฉืึทืืืืื ืืื ืืขืงืก ืืื WHERE ืคึผืื ืงื ืึธืืขืจ ืึทืจืืึทื ื ืขืืขื ื ืึธื ืคืขืืืขืจ ืืื ืื ืืื ืืขืงืก.
ืืืื ืื ืคึฟืืืืจืืจืื ื ืฆืืฉืืึทื ื ืืื "ืกืืึทืืืง" ืคึฟืึทืจ ืืืื ืืึทืกืงืก - ืืึธืก ืืื ืืื ื ืืฉื ืึทืจืืึทื ื ืขืืขื ืืงืกืคึผืึทื ืฉืึทื ืจืฉืืื ืคืื ืืืึทืืืขืก ืืื ืืขืจ ืฆืืงืื ืคึฟื - ืขืก ืืื ืืขืกืขืจ ืฆื ื ืืฆื ืึท WHERE ืืื ืืขืงืก. ืคืึทืจืฉืืื ืืืืืึทื / ืขื ืื ืกืืึทืืืกืขืก ืคึผืึทืกืืง ืืขืืื ื ืืื ืืขื ืงืึทืืขืืึธืจืืข.
ืืืื ืื Filtration ืฆืืฉืืึทื ื ืงืขื ืขื ื ืขืืขื ืืืืฃ ืคืึทืจืฉืืืขื ืข ืืืึทืืืขืก, ืขืก ืืื ืืขืกืขืจ ืฆื ืืงืกืคึผืึทื ื ืื ืืื ืืขืงืก ืืื ืื ืคืขืืืขืจ - ืืื ืืื ืื ืกืืืืึทืฆืืข ืืื BitmapAnd ืืืืื.
ืืืึทืฉืคึผืื:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "ัะฐะบัะพะฒ"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 ัะฐะทะฝัั
ะฒะฝะตัะฝะธั
ะบะปััะตะน
END fk_own
, (random() < 1::real/50) critical; -- 1:50, ััะพ ะทะฐัะฒะบะฐ "ะบัะธัะธัะฝะฐั"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
ืืืจ ืคืึทืจืจืืืื:
CREATE INDEX ON tbl(pk)
WHERE critical; -- ะดะพะฑะฐะฒะธะปะธ "ััะฐัะธัะฝะพะต" ััะปะพะฒะธะต ัะธะปัััะฐัะธะธ
ืืื ืืืจ ืงืขื ืขื ืืขื, ืื ืคึฟืืืืจืืจืื ื ืคืื ืืขื ืคึผืืึทื ืืื ืืึธืจ ื ืืืึธ, ืืื ืื ืืงืฉื ืืื ืืขืืืืจื 5 ืืื ืคืึทืกืืขืจ.
# 5: ืฉืืืขืจ ืืืฉ
ืืืขื ืืื
ืคืึทืจืฉืืื ืคืจืืืื ืฆื ืืึทืื ืืืื ืืืืืขื ืข ืึทืจืืขื ืคึผืจืึทืกืขืกืื ื ืจืื, ืืืขื ืึท ืืจืืืก ื ืืืขืจ ืคืื ืืขืจืืืึทื ืืืงืื ืืขื / ืืืืืฉืึทื ื ืคืื ืจืขืงืึธืจืืก ืืืืฃ ืื ืืืฉ ืคืืจื ืฆื ืึท ืกืืืืึทืฆืืข ืคืื โโโโืึท ืืจืืืก ื ืืืขืจ ืคืื "ืืืื" ืจืขืงืึธืจืืก.
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops ร (rows + RRbF) < (shared hit + shared read) ร 8
-- ะฟัะพัะธัะฐะฝะพ ะฑะพะปััะต 1KB ะฝะฐ ะบะฐะถะดัั ะทะฐะฟะธัั
&& shared hit + shared read > 64
ืจืขืงืึทืืึทื ืืืืฉืึทื ื
ืืึทื ืืืึทืื ืคืืจื ืืืืก ืงืขืกืืืืขืจ ืืืึทืงืืื [ืคืื] ืึธืืขืจ ืืขืจืืจืืืื ืึทืืึทืงืืืึทืืื ืึธืคื ืคึผืจืึทืกืขืกืื ื ืืืจื ืคืืื-ืืื ืื ื ืืืึทื ืคึผืึทืจืึทืืขืืขืจืก, ืึทืจืืึทื ืืขืจืขืื ื .
ืืื ืจืืึฟ ืคืืื, ืึทืืึท ืคึผืจืึธืืืขืืก ืืขื ืขื ืืขืคึฟืืจื ืืืจื ื ืขืืขื ืึธื ืคึฟืจืขื ืืืืกืืืื ืืืขื ืืขืจืืคึฟื ืคึฟืื ืืขืฉืขืคื ืืึธืืืง, ืึทืืึท ืืื ืื ืืืกืงืึทืกื ืืื .
ืึธืืขืจ ืืืจ ืืืื ืคึฟืึทืจืฉืืืื ืึทื ืืคืืื ืืืึทืงืืื ืคืื ืงืขื ืขื ื ืืฉื ืฉืืขื ืืืง ืืขืืคื. ืคึฟืึทืจ ืึทืืึท ืงืึทืกืขืก, ืืืจ ืืึธื ืืืงืขื ืขื ืืื ืืื ืื ืึทืืืขืจืืืึทื ืคืื ืืขื ืึทืจืืืงื. .
#6: ืืืืขื ืขื ืคืื ืื "ืืืื" ืคืื ืื ืืื ืืขืงืก
ืืืขื ืืื
ืขืก ืืืื ื ืึทื ืืื ืืืืขื ืขื ืึท ืืืกื, ืืื ืึทืืฅ ืืื ืื ืืขืงืกื, ืืื ืืื ืืึธืื ื ืืฉื ืคืืืืขืจ ืืืขืจ ืขืก ืื ืขืงืกืืจืข - ืึธืืขืจ ื ืึธื, ืคืื ืืขืจ ืืืขืืขืจ ืืขื ืขื ืืืืขื ืขื ืืื ืืืจ ืืืึธืื ืืื.
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> Index [Only] Scan [Backward]
&& loops ร (rows + RRbF) < (shared hit + shared read) ร 8
-- ะฟัะพัะธัะฐะฝะพ ะฑะพะปััะต 1KB ะฝะฐ ะบะฐะถะดัั ะทะฐะฟะธัั
&& shared hit + shared read > 64
ืจืขืงืึทืืึทื ืืืืฉืึทื ื
ื ืขืืขื ืึท ื ืึธืขื ื ืงืืง ืืื ืื ืกืืจืืงืืืจ ืคืื ืื ืืื ืืขืงืก ืืขื ืืฆื ืืื ืื ืฉืืืกื ืคืขืืืขืจ ืกืคึผืขืกืืคืืขื ืืื ืื ืึธื ืคึฟืจืขื - ืจืืึฟ ืืกืชึผืื, ืืื ืืขืงืก ืืืื ื ืืฉื ืืึทืฉืืืื. ืืืจ ืืืขื ืจืืึฟ ืืกืชึผืื ืืึทืจืคึฟื ืฆื ืฉืึทืคึฟื ืึท ืขื ืืขื ืืื ืืขืงืก, ืึธืืขืจ ืึธื ืคึผืจืขืคืืงืก ืคืขืืืขืจ, ืึธืืขืจ .
ืืืึทืฉืคึผืื:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "ัะฐะบัะพะฒ"
, (random() * 100)::integer fk_org -- 100 ัะฐะทะฝัั
ะฒะฝะตัะฝะธั
ะบะปััะตะน
, (random() * 1000)::integer fk_cli; -- 1K ัะฐะทะฝัั
ะฒะฝะตัะฝะธั
ะบะปััะตะน
CREATE INDEX ON tbl(fk_org, fk_cli); -- ะฒัะต ะฟะพััะธ ะบะฐะบ ะฒ #2
-- ัะพะปัะบะพ ะฒะพั ะพัะดะตะปัะฝัะน ะธะฝะดะตะบั ะฟะพ fk_cli ะผั ัะถะต ะฟะพััะธัะฐะปะธ ะปะธัะฝะธะผ ะธ ัะดะฐะปะธะปะธ
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- ะฐ fk_org ะฝะต ะทะฐะดะฐะฝะพ, ั
ะพัั ััะพะธั ะฒ ะธะฝะดะตะบัะต ัะฐะฝััะต
LIMIT 20;
ืึทืืฅ ืืืื ื ืฆื ืืืื ืคืืึทื, ืืคืืื ืืื ืืขืจืืื ืขื ืคืื ืื ืืื ืืขืงืก, ืึธืืขืจ ืขืคืขืก ืกืึทืกืคึผืืฉืึทืก - ืคึฟืึทืจ ืืขืืขืจ ืคืื ืื 20 ืจืขืงืึธืจืืก ืืืืขื ืขื, 4 ืืืขืืขืจ ืคืื ืืึทืื ืืึธืื ืฆื ืืืื ืกืึทืืืจืึทืงืืื, 32KB ืคึผืขืจ ืจืขืงืึธืจื - ืืื ืขืก ื ืืฉื ืืจืืืกื? ืื ืืื ืืื ืืขืงืก ื ืึธืืขื tbl_fk_org_fk_cli_idx ืคืืจื ืฆื ืืขืืึทื ืง.
ืืืจ ืคืึทืจืจืืืื:
CREATE INDEX ON tbl(fk_cli);
ืคึผืืืฆืืื ื - 10 ืืื ืคืึทืกืืขืจ ืืื 4 ืืื ืืืืื ืืงืขืจ ืฆื ืืืืขื ืขื!
ืคึฟืึทืจ ืืขืจ ืืืืฉืคืืื ืคืื ืืึทืืืึธื ืืฉ ื ืืฆื ืคืื ืื ืืขืงืกืื, ืืขื ืืขื ืึทืจืืืงื .
#7: CTE ร CTE
ืืืขื ืืื
ืืื ืืงืฉื ืกืงืึธืจื "ืคืขื" CTE ืคืื ืคืึทืจืฉืืืขื ืข ืืืฉื, ืืื ืืขืืึธืื ืืึทืฉืืึธืกื ืฆื ืืึธื ืฆืืืืฉื ืืื JOIN.
ืืขืจ ืคืึทื ืืื ืืึทืืืึทืืืง ืคึฟืึทืจ ืืืขืจืกืืขืก ืืื ืืขืจ ืื12 ืึธืืขืจ ืจืืงืืืขืก ืืื WITH MATERIALIZED.
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> CTE Scan
&& loops > 10
&& loops ร (rows + RRbF) > 10000
-- ัะปะธัะบะพะผ ะฑะพะปััะพะต ะดะตะบะฐััะพะฒะพ ะฟัะพะธะทะฒะตะดะตะฝะธะต CTE
ืจืขืงืึทืืึทื ืืืืฉืึทื ื
ืึทื ืึทืืืืืจื ืื ืืงืฉื ืงืขืจืคืึทืื ? ืืืื ืืึธ, ืืขืืึธืื ืฆืืืืืื "ืืืขืจืืขืจืืื" ืืื hstore/json ืืืื ืื ืืึธืืขื ืืืกืงืจืืืื ืืื .
#8: ืืืกืืืึทืื ืฆื ืืืกืง (ืืขืืคึผ ืืขืฉืจืืื)
ืืืขื ืืื
ืืืื ืืึธื ืคึผืจืึทืกืขืกืื ื (ืกืึธืจืืื ื ืึธืืขืจ ืืื ืฆืืง) ืคืื ืึท ืืจืืืก ื ืืืขืจ ืคืื ืจืขืงืึธืจืืก ืืื ื ืืฉื ืคึผืึทืกืืง ืืื ืื ืืืงืึธืจื ืึทืืึทืงืืืืื ืคึฟืึทืจ ืืขื.
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> *
&& temp written > 0ืจืขืงืึทืืึทื ืืืืฉืึทื ื
ืืืื ืื ืกืืืข ืคืื โโโโืืืงืึธืจื ืืขื ืืฆื ืืืจื ืื ืึธืคึผืขืจืึทืฆืืข ืืื ื ืืฉื ืืงืกืื ืื ืืึทืฉืืืื ืืืขืจื ืคืื ืื ืคึผืึทืจืึทืืขืืขืจ , ืขืก ืืึธื ืืืื ืงืขืจืขืงืืึทื. ืืืจ ืงืขื ืขื ืืืืื ืืื ืื ืงืึทื ืคืืืืขืจืืืฉืึทื ืคึฟืึทืจ ืึทืืขืืขื, ืึธืืขืจ ืืืจ ืงืขื ืขื ืืืจื SET [LOCAL] ืคึฟืึทืจ ืึท ืกืคึผืขืฆืืคืืฉ ืืงืฉื / ืืจืึทื ืกืึทืงืืืึธื.
ืืืึทืฉืคึผืื:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
ืืืจ ืคืึทืจืจืืืื:
SET work_mem = '128MB'; -- ะฟะตัะตะด ะฒัะฟะพะปะฝะตะฝะธะตะผ ะทะฐะฟัะพัะฐ
ืคึฟืึทืจ ืงืืึธืจ ืืื ืืขืจ ืืึธื ืกืืืืช, ืืืื ืืืืื ืืึผืจืื ืืื ืืขื ืืฆื, ืืื ื ืืฉื ืืืกืง, ืื ืึธื ืคึฟืจืขื ืืืขื ืืืื ืขืงืกืึทืงืืืืึทื ืคืื ืคืึทืกืืขืจ. ืืื ืืขืจ ืืขืืืืงืขืจ ืฆืืื, ืืืื ืคืื ืื ืืึทืกืข ืืื ืืืื ืึทืืืขืงืืขื ืืืขื ืคืื ืื ืืื.
ืึธืืขืจ ืืืจ ืืึทืจืคึฟื ืฆื ืคึฟืึทืจืฉืืืื ืึทื ืื ืึทืืึทืงืืืฉืึทื ืคืื ืึท ืคึผืืึทืฅ ืคืื ืืึผืจืื ืืืขื ืฉืืขื ืืืง ื ืืฉื ืึทืจืืขืื - ืขืก ืืื ืคืฉืื ื ืืฉื ืืขื ืื ืคึฟืึทืจ ืึทืืขืืขื.
#9: ืืจืขืืึทืืืึทื ื ืกืืึทืืืกืืืง
ืืืขื ืืื
ืืกืื ืืื ืืขื ืืืฒื ืืจืฒื ืืขืืืกื ืืื ืืขืจ ืืืืข, ืืืขืจ ืืฒ ืืืื ื ืืฉื ืืขืืื ืงืฒื ืฆืฒื ืขืก ืืฐืขืง ืฆื ืคืืจืืจืืื ANALYZE.
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10ืจืขืงืึทืืึทื ืืืืฉืึทื ื
ืคืึทืจืืจืขื ืืขื ืื ืืขืืืข ANALYZE.
ืืขื ืกืืืืึทืฆืืข ืืื ืืืกืงืจืืืื ืืื ืืขืจ ืืขืืึทื ืืื .
#10: "ืขืคึผืขืก ืืื ืคืึทืืฉ"
ืืืขื ืืื
ืขืก ืืื ืืขืืืขื ืึท ืฉืืึธืก ืืืืจืื ืคึฟืึทืจ ืึท ืงืึทืืคึผืืืื ื ืืขืื, ืึธืืขืจ ืขืก ืืื ื ืืฉื ืืขื ืื ืงืคึผื / ืืืืคึผืขืจืืืืืืขืจ ืืึทืื ืืืึทืจื ืจืขืกืืจืกื.
ืืื ืฆื ืืืขื ืืืคืืฆืืจื
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- ัะธัะฐะปะธ ะผะฐะปะพ, ะฝะพ ัะปะธัะบะพะผ ะดะพะปะณะพ
ืจืขืงืึทืืึทื ืืืืฉืึทื ื
ื ืืฆื ืึท ืคืื ืืจืืืกื ืืืง ืืึธื ืืืึธืจืื ื ืกืืกืืขื ืกืขืจืืืขืจ ืคึฟืึทืจ ืืืึทืงืื ื ืึธืืขืจ ืึทืื ืึธืจืืึทื ืืืื ืงืึทื ืกืึทืืฉืึทื. ืืืจ ืืึธืื ืฉืืื ืืขืจืขืื ืืืขืื ืืื ืืืขืจ ืืืขืจืกืืข ืคืื โโืึธืจืืึทื ืืืืื ื ืืขื ืคึผืจืึธืฆืขืก ืคึฟืึทืจ ืืื ืืขืจืืขืจ ืคืื ืกืขืจืืืขืจืก. ะธ .
ืืงืืจ: www.habr.com