అప్పటి నుండి మీరు దీన్ని 6000 సార్లు ఉపయోగించారు, కానీ సులభ ఫీచర్లలో ఒకటి గుర్తించబడకపోవచ్చు నిర్మాణాత్మక ఆధారాలు, ఇది ఇలా కనిపిస్తుంది:
వాటిని వినండి మరియు మీ అభ్యర్థనలు "సిల్కీ స్మూత్గా మారుతాయి". 🙂
కానీ తీవ్రంగా, రిక్వెస్ట్ని నెమ్మదిగా మరియు వనరుల పరంగా "తిండిపోతు"గా మార్చే అనేక పరిస్థితులు, విలక్షణమైనవి మరియు ప్రణాళిక యొక్క నిర్మాణం మరియు డేటా ద్వారా గుర్తించబడతాయి.
ఈ సందర్భంలో, ప్రతి వ్యక్తి డెవలపర్ తన స్వంత అనుభవంపై ఆధారపడి తన స్వంత ఆప్టిమైజేషన్ ఎంపిక కోసం వెతకవలసిన అవసరం లేదు - ఇక్కడ ఏమి జరుగుతుందో, కారణం ఏమిటో మేము అతనికి చెప్పగలము మరియు ఒక పరిష్కారంతో ఎలా రావాలి. మేము ఏమి చేసాము.
ఈ కేసులను నిశితంగా పరిశీలిద్దాం - అవి ఎలా నిర్వచించబడ్డాయి మరియు అవి ఏ సిఫార్సులకు దారితీస్తాయి.
టాపిక్లో మెరుగైన ఇమ్మర్షన్ కోసం, మీరు ముందుగా సంబంధిత బ్లాక్ని వినవచ్చు PGConf.Russia 2020లో నా నివేదిక, ఆపై మాత్రమే ప్రతి ఉదాహరణ యొక్క వివరణాత్మక విశ్లేషణకు వెళ్లండి:
#1: సూచిక "అండర్సార్టింగ్"
ఎప్పుడు
క్లయింట్ "LLC కొలోకోల్చిక్" కోసం చివరి ఇన్వాయిస్ను చూపండి.
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;
అటువంటి ఆదిమ నమూనాలో కూడా - 8.5x వేగంగా మరియు 33x తక్కువ రీడ్లు. ప్రభావం స్పష్టంగా ఉంటుంది, ప్రతి విలువకు మీరు ఎక్కువ "వాస్తవాలు" కలిగి ఉంటారు. fk.
అటువంటి సూచిక "ఉపసర్గ" ఇండెక్స్గా పని చేస్తుందని నేను గమనించాను fk, ఇక్కడ క్రమబద్ధీకరించడం pk కాదు మరియు కాదు (మీరు దీని గురించి మరింత చదువుకోవచ్చు అసమర్థమైన సూచికలను కనుగొనడం గురించి నా వ్యాసంలో) ముఖ్యంగా, ఇది సాధారణ అందిస్తుంది స్పష్టమైన విదేశీ కీ మద్దతు ఈ ఫీల్డ్ ద్వారా.
#2: ఇండెక్స్ ఖండన (బిట్మ్యాప్అండ్)
ఎప్పుడు
"NJSC Lyutik" తరపున ముగించబడిన క్లయింట్ "LLC Kolokolchik" కోసం అన్ని ఒప్పందాలను చూపండి.
ఎలా గుర్తించాలి
-> 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); -- отбор по конкретной паре
ఇక్కడ లాభం తక్కువగా ఉంటుంది, ఎందుకంటే బిట్మ్యాప్ హీప్ స్కాన్ దాని స్వంత ప్రభావవంతంగా ఉంటుంది. కాని ఏదోవిధముగా 7x వేగంగా మరియు 2.5x తక్కువ రీడ్లు.
#3: ఇండెక్స్లను కలపడం (BitmapOr)
ఎప్పుడు
ప్రాసెసింగ్ కోసం మొదటి 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 రికార్డ్లు మొదటి బ్లాక్లో వెంటనే పొందబడ్డాయి అనే వాస్తవాన్ని మేము సద్వినియోగం చేసుకున్నాము, కాబట్టి రెండవది, మరింత “ఖరీదైన” బిట్మ్యాప్ హీప్ స్కాన్తో, కూడా అమలు చేయబడలేదు - ఫలితంగా 22x వేగంగా, 44x తక్కువ రీడ్లు!
నియమం ప్రకారం, మీరు ఇప్పటికే ఉన్న అభ్యర్థనకు "మరొక ఫిల్టర్ని జోడించాలనుకున్నప్పుడు" ఇది జరుగుతుంది.
“మరియు మీకు అదే లేదు, కానీ పెర్ల్ బటన్లతో? » చిత్రం "డైమండ్ హ్యాండ్"
ఉదాహరణకు, ఎగువ టాస్క్ని సవరించడం, వాటి ప్రయోజనంతో సంబంధం లేకుండా ప్రాసెసింగ్ కోసం మొదటి 20 పురాతన "క్లిష్టమైన" అభ్యర్థనలను చూపండి.
ఎలా గుర్తించాలి
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
సిఫార్సులు
[మరింత] ప్రత్యేకతను సృష్టించండి WHERE నిబంధనతో సూచిక లేదా ఇండెక్స్లో అదనపు ఫీల్డ్లను చేర్చండి.
మీ పనులకు ఫిల్టరింగ్ పరిస్థితి "స్టాటిక్" అయితే - అంటే విస్తరణను కలిగి ఉండదు భవిష్యత్తులో విలువల జాబితా - WHERE ఇండెక్స్ని ఉపయోగించడం మంచిది. వివిధ బూలియన్/ఎనమ్ స్థితిగతులు ఈ వర్గానికి బాగా సరిపోతాయి.
వడపోత పరిస్థితి ఉంటే వివిధ విలువలను తీసుకోవచ్చు, అప్పుడు ఈ ఫీల్డ్లతో ఇండెక్స్ని విస్తరించడం మంచిది - 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;
మీరు చూడగలిగినట్లుగా, ప్లాన్ నుండి ఫిల్టరింగ్ పూర్తిగా పోయింది మరియు అభ్యర్థన మారింది 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
సిఫార్సులు
మానవీయంగా క్రమం తప్పకుండా నిర్వహించండి వాక్యూమ్ [పూర్తి] లేదా తగినంత తరచుగా ప్రాసెసింగ్ సాధించండి ఆటోవాక్యూమ్ దాని పారామితులను చక్కగా ట్యూన్ చేయడం ద్వారా, సహా నిర్దిష్ట పట్టిక కోసం.
వారు కొంచెం చదివినట్లు అనిపిస్తుంది మరియు ప్రతిదీ ఇండెక్స్ చేయబడింది మరియు వారు ఎవరినీ అదనంగా ఫిల్టర్ చేయలేదు - అయినప్పటికీ, మనం కోరుకునే దానికంటే చాలా ఎక్కువ పేజీలు చదవబడ్డాయి.
ఎలా గుర్తించాలి
-> 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 ఆలోచనకు దారి తీస్తుంది.
పెద్ద సంఖ్యలో రికార్డుల యొక్క వన్-టైమ్ ప్రాసెసింగ్ (సార్టింగ్ లేదా ప్రత్యేకత) దీని కోసం కేటాయించిన మెమరీకి సరిపోదు.
ఎలా గుర్తించాలి
-> *
&& temp written > 0
సిఫార్సులు
ఆపరేషన్ ఉపయోగించిన మెమరీ మొత్తం పరామితి యొక్క సెట్ విలువను ఎక్కువగా మించకపోతే పని_మెమ్, దాన్ని సరిచేయాలి. మీరు వెంటనే అందరి కోసం కాన్ఫిగరేషన్లో చేయవచ్చు లేదా మీరు ద్వారా చేయవచ్చు SET [LOCAL] నిర్దిష్ట అభ్యర్థన/లావాదేవీ కోసం.
ఉదాహరణకు:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
స్పష్టమైన కారణాల వల్ల, డిస్క్ కాకుండా మెమరీని మాత్రమే ఉపయోగించినట్లయితే, అప్పుడు ప్రశ్న చాలా వేగంగా అమలు చేయబడుతుంది. అదే సమయంలో, లోడ్ యొక్క భాగం కూడా HDD నుండి తీసివేయబడుతుంది.
కానీ చాలా మెమరీని కేటాయించడం ఎల్లప్పుడూ పని చేయదని మనం అర్థం చేసుకోవాలి - ఇది అందరికీ సరిపోదు.
#9: అసందర్భ గణాంకాలు
ఎప్పుడు
చాలా ఒకేసారి బేస్ లోకి కురిపించింది, కానీ వారు దానిని తరిమికొట్టడానికి సమయం లేదు ANALYZE.
ఎలా గుర్తించాలి
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10
పోటీ అభ్యర్థన కోసం లాక్ వేచి ఉంది లేదా తగినంత CPU/హైపర్వైజర్ హార్డ్వేర్ వనరులు లేవు.
ఎలా గుర్తించాలి
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
సిఫార్సులు
ఒక బాహ్య ఉపయోగించండి పర్యవేక్షణ వ్యవస్థ నిరోధించడం లేదా అసాధారణ వనరుల వినియోగం కోసం సర్వర్. వందలాది సర్వర్ల కోసం ఈ ప్రక్రియను నిర్వహించే మా వెర్షన్ గురించి మేము ఇప్పటికే మాట్లాడాము. ఇక్కడ и ఇక్కడ.