Kai kalbame apie PostgreSQL ir užklausų optimizavimą, dauguma žmonių iš karto galvoja apie B-Tree indeksus, GIN, GiST ir pan. Tačiau BRIN indeksai dažnai lieka šešėlyje, nepaisant to, kad kai kuriais atvejais jie yra nepaprastai naudingi. Jie gali žymiai padidinti našumą, ypač dirbant su didelėmis lentelėmis ir ribota vieta diske. Šiandien paaiškinsiu, kaip veikia BRIN ir kada jis šviečia.
Kas yra BRIN?
BRIN reiškia Block Range Index. Nors įprasti indeksai siekia saugoti išsamią informaciją apie kiekvieną eilutę, BRIN laikosi kitokio požiūrio. Jis saugo puslapių grupių, vadinamų „zonomis“, suvestinę informaciją, o ne indeksuoja kiekvieną eilutę atskirai.
Kad būtų paprasčiau, BRIN padalija lentelę į blokus (kiekviena zona pagal numatytuosius nustatymus paprastai apima 32 puslapius) ir toje zonoje išsaugo minimalias ir didžiausias stulpelio reikšmes (arba kitas apibendrintas charakteristikas, priklausomai nuo operatoriaus klasės). Kai pateikiate duomenų užklausą, BRIN nustato atitinkamas zonas, o likusias praleidžia.
Ar tai 100% tikslus? Ne visai – vis tiek turėsite nuskaityti eilutes pasirinktose zonose. Tačiau šis filtravimas yra žymiai greitesnis nei visos lentelės nuskaitymas.
Kada BRIN yra geras pasirinkimas?
Štai keli scenarijai, kuriuose BRIN puikiai tinka:
-
Didžiuliai stalai
Kai dirbate su lenteles, kuriose yra milijonai ar milijardai eilučių, tradiciniai B-Tree indeksai gali išsipūsti ir užimti daug vietos. Kita vertus, BRIN išlieka lengvas ir kompaktiškas. -
Natūralių duomenų koreliacija
Jei jūsų duomenys turi natūralią tvarką, BRIN gali būti ypač efektyvus. Pavyzdžiui, jei turiteorder_date
stulpelyje anorders
lentelę, tikėtina, kad senesni užsakymai saugomi lentelės pradžioje, o naujesni – pabaigoje. Užklausoms, nukreiptoms į konkretų mėnesį, BRIN greitai susiaurina atitinkamas zonas. -
Vietos diske taupymas
BRIN indeksai yra nepaprastai kompaktiški. Užuot saugoję visas atskiras reikšmes, jie naudoja apibendrintus metaduomenis kiekvienai zonai, o tai sumažina saugojimo reikalavimus.
BRIN apribojimai
Kaip ir bet kuris įrankis, BRIN turi kompromisų:
- Netikslumas: BRIN tiksliai nenustato duomenų vietų. Tai tik nurodo zonas, kuriose duomenys gali būti, todėl tose zonose reikia toliau filtruoti.
- Išsklaidyti duomenys: Jei stulpelių reikšmės yra išsklaidytos atsitiktinai, BRIN negalės veiksmingai pašalinti nesusijusių zonų, todėl tai bus mažiau naudinga.
BRIN indekso sukūrimas
Štai trumpas pavyzdys, kaip sukurti BRIN indeksą:
CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date)
WITH (pages_per_range = 32);
The pages_per_range
parametras nustato puslapių skaičių kiekvienoje zonoje. Numatytasis yra 128 puslapiai, bet galite jį koreguoti. Mažesnė reikšmė padidina indekso tikslumą, bet taip pat padidina indekso dydį. Eksperimentuodami su šiuo parametru galite rasti geriausią duomenų balansą.
Suvestinių automatizavimas
Galite naudoti plėtinius, pvz autosummarize
automatizuoti suvestinių duomenų atnaujinimą, nebereikia skambinti rankiniu būdu brin_summarize_new_values
.
BRIN operatoriaus klasės: Minmax ir įtraukimas
BRIN naudoja operatorių klases, kad nustatytų, kaip apibendrinti duomenis. Du pagrindiniai metodai yra šie:
- Minmax klasės: Išsaugokite minimalias ir didžiausias vertes kiekvienoje zonoje. Idealiai tinka užsakytiems tipams, pvz., skaičiams, datoms ar eilutėms.
- Įtraukimo klasės: Tvarkykite sudėtingesnius tipus, pvz., geometriją, IP tinklus ar diapazonus. Šiose klasėse saugomi „masiniai“ atvaizdai, tokie kaip ribojantys langeliai arba diapazonų sąjungos, leidžiančios atlikti sudėtingų duomenų struktūrų operacijas.
Kodėl įtraukimo klasės yra svarbios
Įtraukimo klasės gali efektyviai apdoroti sudėtingus duomenis. Pavyzdžiui, jie gali identifikuoti zonas, kuriose yra objektų, kertančių konkrečią sritį, todėl jos gali būti naudingos naudojant specializuotus naudojimo atvejus, pvz., erdvinius duomenis.
BRIN indeksai yra galinga, tačiau nepakankamai naudojama PostgreSQL funkcija. Nors jie netinka kiekvienai situacijai, jie gali žymiai pagerinti našumą dirbant su dideliais duomenų rinkiniais ir natūraliai sutvarkytais duomenimis. Eksperimentuokite su BRIN, kad atskleistumėte jo potencialą ir optimizuotumėte duomenų bazės užklausas.
Vardas | Duomenų tipas | Operatoriai |
---|---|---|
abstime_minmax_ops | susilaikymas | <<= = >= > |
int8_minmax_ops | bigint | <<= = >= > |
bit_minmax_ops | šiek tiek | <<= = >= > |
varbit_minmax_ops | šiek tiek įvairus | <<= = >= > |
box_inclusion_ops | dėžutė | << &< && &> >> ~= @> <@ |
bytea_minmax_ops | baitas | <<= = >= > |
bpchar_minmax_ops | charakteris | < <= = >= > |
char_minmax_ops | „char” | <<= = >= > |
data_minmax_ops | data | <<= = >= > |
float8_minmax_ops | dvigubas tikslumas | <<= = >= > |
inet_minmax_ops | inet | <<= = >= > |
network_inclusion_ops | inet | && >>= <<= = >> << |
int4_minmax_ops | sveikasis skaičius | <<= = >= > |
interval_minmax_ops | intervalas | <<= = >= > |
macaddr_minmax_ops | macaddr | <<= = >= > |
name_minmax_ops | pavadinimas | <<= = >= > |
numeric_minmax_ops | skaitinis | < <= = >= > |
pg_lsn_minmax_ops | pg_lsn | <<= = >= > |
oid_minmax_ops | oid | <<= = >= > |
range_inclusion_ops | bet kokio tipo diapazonas | << &< && &> >> @> <@ - |
float4_minmax_ops | tikras | < <= = >= > |
reltime_minmax_ops | retime | <<= = >= > |
int2_minmax_ops | smallint | <<= = >= > |
text_minmax_ops | tekstą | <<= = >= > |
tid_minmax_ops | tid | < <= = >= > |
timestamp_minmax_ops | laiko žyma be laiko juostos | <<= = >= > |
timestampz_minmax_ops | laiko žyma su laiko juosta | <<= = >= > |
time_minmax_ops | laikas be laiko juostos | < <= = >= > |
timetz_minmax_ops | laikas su laiko juosta | <<= = >= > |
uuid_minmax_ops | uuid | < <= = >= > |
Daugeliui tipų yra minmax operacijos, o egzotiškesnėms struktūroms – įtraukimo operacijos. Tai yra, galite indeksuoti ne tik paprastus skaičius ir eilutes, bet ir sudėtingus tipus. Pavyzdžiui, box_inclusion_ops leis ieškoti objektų tam tikroje geografinėje vietovėje, greitai iškirsdamas zonas, kuriose nėra tinkamų objektų.
Optimizavimo pavyzdys
Tarkime, kad yra užsakymų lentelė su daugybe eilučių:
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
order_date DATE NOT NULL,
customer_id BIGINT NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
Duomenų krūvos įterpimas:
INSERT INTO orders (order_date, customer_id, total_amount)
SELECT
(DATE '2023-01-01' + (RANDOM()*365)::INT),
(RANDOM()*1000000)::BIGINT,
(RANDOM()*1000)::NUMERIC(10,2)
FROM generate_series(1,10000000) g;
Dabar sukuriame BRIN indeksą:
CREATE INDEX idx_orders_date_brin ON orders
USING BRIN(order_date date_minmax_ops)
WITH (pages_per_range = 64);
date_minmax_ops
sako: mes saugosime minimalią ir maksimalią datą kiekvienai 64 puslapių zonai.
Kai klausiate:
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-15';
Planas parodys, kad PostgreSQL pirmiausia patikrins BRIN indeksą, nustatys, kuriose zonose gali būti norimos datos, o likusias dalis praleis. Jei duomenys apytiksliai surūšiuoti pagal datą (pvz., kaip įterpti), papildomų zonų skaičius bus sumažintas.
Parametrų subtilybės
pages_per_range
- Mažesnės vertybės: Pateikite tikslesnį filtravimą (kiekviena zona yra mažesnė, todėl mažiau šiukšlių), bet padidinkite indekso dydį.
- Didesnės vertės: Sumažinkite indekso dydį, bet pabloginkite tikslumą.
- Rekomendacija: patikrinkite tikrus duomenis, kad rastumėte optimalų balansą.
autovacuum
ir brin_summarize_new_values
Individualizuotos operatoriaus klasės
Jei jokia standartinė operatoriaus klasė neatitinka jūsų poreikių, galite parašyti savo. Tai pažangesnis metodas, reikalaujantis:
- Įdiekite C funkcijas, kad apibūdintumėte, kaip:
- Suvestinės vertės,
- Sujungti zonas ir
- Patikrinkite sankryžas su užklausomis.
- Tai leidžia indeksuoti unikalias ar egzotiškas duomenų struktūras, tačiau tai sudėtinga, labai techninė užduotis.
Išvada
BRIN indeksai nepakeis B-Tree indeksų visur. Tačiau tais atvejais, kai duomenys yra sutvarkyti arba turi natūralią koreliaciją su jų fizine vieta, BRIN gali užtikrinti nuostabų našumą su minimaliu indekso dydžiu.
Jei tekste radote klaidą, siųskite pranešimą autoriui pažymėdami klaidą ir paspausdami Ctrl-Enter.