Monitorings

Neizmantotie indeksi PostgreSQL: kā tos atrast un droši dzēst

Katrs indekss maksā diska vietu, kešatmiņu un rakstīšanas laiku, pat ja neviens vaicājums to neizmanto. Kā PostgreSQL atrast neizmantotus, dublētus un nederīgus indeksus, ko statistika nepasaka un kā tos dzēst bez pārsteigumiem.

· 14 min lasīšanai · AG Data komanda

Neizmantotie indeksi PostgreSQL ir viens no lētākajiem veiktspējas uzlabojumiem nobriedušā datubāzē. Neviens vairs neatceras, kāpēc pastāv orders_status_created_idx, bet katrs ieraksts joprojām raksta tajā, katrs vacuum to skenē, un tas aizņem vietu shared buffers un katrā rezerves kopijā. Tabulās ar desmit vai piecpadsmit indeksiem lieko indeksu noņemšana bieži ievērojami paātrina rakstīšanu un atbrīvo desmitiem vai simtiem gigabaitu.

Āķis ir tas, ka "neizmantots" ir apgalvojums par statistiku, un statistika var maldināt. Tālāk: vaicājums kandidātu atrašanai, indeksi, kurus nedrīkst dzēst pat ar nulli skenējumu, dublikāti un nederīgi indeksi, un dzēšanas procedūra, kas ir droša lielā produkcijas sistēmā.

Kāpēc neizmantots indekss nav bez maksas

Cik daudz HOT jūs iegūstat šodien, var redzēt ar šo vaicājumu. Zems procents uz aktīvas tabulas ar daudziem indeksiem ir spēcīgs signāls, ka indeksu pārskatīšana atmaksāsies:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 20;

Kandidātu atrašana ar pg_stat_user_indexes

PostgreSQL uzskaita indeksu lietojumu skatā pg_stat_user_indexes. idx_scan ir indeksā uzsākto skenējumu skaits, un kopš PostgreSQL 16 last_idx_scan rāda, kad indekss pēdējo reizi izmantots. Vaicājums zemāk parāda indeksus ar nulli skenējumu, lielākos vispirms, un izslēdz indeksus, kas kaut ko nodrošina:

SELECT s.schemaname,
       s.relname       AS table_name,
       s.indexrelname  AS index_name,
       s.idx_scan,
       s.last_idx_scan,                       -- PostgreSQL 16+
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique                     -- includes primary keys
  AND NOT i.indisexclusion
  AND NOT i.indisreplident
  AND NOT EXISTS (SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

PostgreSQL 15 un vecākās versijās izņemiet rindu ar last_idx_scan. Filtri balstās uz pg_index katalogu: indisunique aptver arī primārās atslēgas, indisexclusion apzīmē izslēgšanas ierobežojumus, un indisreplident apzīmē indeksu, kas izvēlēts kā replica identity loģiskajai replikācijai. Pārbaude pret pg_constraint notver visu pārējo, no kā atkarīgs kāds ierobežojums.

Unikāliem indeksiem nulle skenējumu nenozīmē nevajadzīgs. Unikāls indekss vai primārā atslēga dara savu darbu katrā ierakstā, noraidot dublikātus, un ārējai atslēgai vajadzīgs unikāls indekss uz atsauktajām kolonnām. Šādi indeksi var mūžīgi rādīt idx_scan = 0 un tomēr būt būtiski. Nekad nedzēsiet tos, balstoties uz lietojuma skaitļiem.

Pārbaudiet, cik ilgi skaitītāji ir skaitījuši

Nulle ir jēgpilna tikai tad, ja skaitītāji aptver pietiekami ilgu laiku. Statistikas dokumentācijā teikts, ka skaitītāji saglabājas pēc korektas apturēšanas, bet tiek atiestatīti pēc tūlītējas apturēšanas (immediate shutdown), avārijas, startēšanas no bāzes rezerves kopijas un atjaunošanas uz laika punktu (PITR). Ikviens ar atbilstošām tiesībām var tos atiestatīt arī ar pg_stat_reset() vai pg_stat_reset_single_table_counters().

SELECT stats_reset, pg_postmaster_start_time()
FROM pg_stat_database
WHERE datname = current_database();

stats_reset rāda pēdējo tiešo atiestatīšanu. Paskatieties arī servera starta laiku un žurnālus: ja pēdējais starts bija pēc avārijas vai pārslēgšanās uz tikko atjaunotu mezglu, skaitītāji ir jaunāki, nekā liek domāt stats_reset. Ja tie aptver divas dienas, jūs neesat redzējuši nedēļas atskaiti, mēneša slēgšanu vai ceturkšņa eksportu. Mēs dodam priekšroku vismaz vienam pilnam biznesa ciklam, parasti mēnesim, un ilgākam laikam sistēmām ar ceturkšņa vai gada darbiem.

Replikām ir sava statistika

Šis ir slazds, kas izraisa lielāko daļu kļūdainu dzēšanu. Statistika tiek vākta lokāli katrā serverī, un fiziskā replika glabā savu kopiju. Lasīšanas vaicājumi, kas darbojas uz standby, izmanto primārā servera indeksus, jo fiziskā replika ir to pašu failu kopija, bet šie skenējumi tiek uzskaitīti standby serverī, nevis primārajā.

Tāpēc indekss, kas primārajā rāda nulli skenējumu, var būt tieši tas indekss, kas uztur atskaišu repliku ātru. Pirms kaut ko dzēšat, palaidiet to pašu vaicājumu katrā replikā un uzskatiet indeksu par neizmantotu tikai tad, ja tas nav izmantots nekur. Tas pats attiecas uz loģiskās replikācijas abonentiem, lai gan tiem ir savi indeksi un savi lēmumi.

Dublēti un nederīgi indeksi

Dublikāti ir vienkāršāki un drošāki par neizmantotiem indeksiem, jo otrā kopija neko nepievieno. Šis vaicājums grupē indeksus pēc tabulas, kolonnām, operatoru klasēm, kolācijām, kārtošanas opcijām, izteiksmēm un daļējiem predikātiem:

SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass) AS same_definition
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, indcollation::text,
         indoption::text, coalesce(indexprs::text, ''), coalesce(indpred::text, '')
HAVING count(*) > 1;

Ja divi indeksi sakrīt, paturiet to, kas nodrošina ierobežojumu, vai to, kuru jūsu migrācijas zina pēc nosaukuma, un dzēsiet otru. Pārklājošiem indeksiem vajag vairāk pārdomu: indeksu uz (customer_id) parasti aizstāj indekss uz (customer_id, created_at), bet ne tad, ja īsākais ir unikāls, daļējs vai daudz mazāks un to izmanto aktīvs vaicājums, kam izmēra starpība ir svarīga. Pārskatiet tos pa vienam.

Nederīgi indeksi bieži paliek pāri, kad CREATE INDEX CONCURRENTLY neizdodas, kā apraksta CREATE INDEX dokumentācija. Saskaņā ar pg_index nederīgu indeksu nevar izmantot vaicājumiem, bet ieraksti un atjauninājumi to joprojām uztur, tāpēc tas ir tīras izmaksas. Indekss, ko pašlaik būvē strādājošs CREATE INDEX CONCURRENTLY, arī tiek rādīts kā nederīgs, tāpēc pirms dzēšanas pārbaudiet pg_stat_activity:

SELECT indexrelid::regclass AS index_name,
       indrelid::regclass   AS table_name
FROM pg_index
WHERE NOT indisvalid;

Dzēsiet to un izveidojiet no jauna, ja tas patiešām bija vajadzīgs. Pārbūvi aprakstām mūsu rokasgrāmatā par REINDEX CONCURRENTLY.

Droša neizmantoto indeksu dzēšana

  1. Saglabājiet definīciju. Ielieciet pg_get_indexdef izvadi migrāciju repozitorijā, lai indeksa atjaunošana būtu kopēšana un ielīmēšana, nevis arheoloģija.
  2. Pārbaudiet lietotni. Meklējiet indeksa nosaukumu kodā un migrācijās. Daži ORM un vaicājumu norādes atsaucas uz indeksiem pēc nosaukuma, un daži upsert vaicājumi tieši nosauc unikālu indeksu vai ierobežojumu.
  3. Dzēsiet konkurenti. Parasts DROP INDEX ņem ACCESS EXCLUSIVE bloķējumu uz tabulas. DROP INDEX CONCURRENTLY nebloķē lasīšanu un rakstīšanu, bet tā vietā gaida, līdz konfliktējošās transakcijas beidzas.
  4. Vērojiet nedēļu. Salīdziniet vaicājumu latentumu, pg_stat_statements un lēno vaicājumu žurnālu ar bāzes līniju. Ja kāds vaicājums kļuva lēnāks, izveidojiet indeksu no jauna ar CREATE INDEX CONCURRENTLY.
-- 1. Keep the definition so you can recreate it
SELECT pg_get_indexdef('orders_status_created_idx'::regclass);

-- 2. Drop without blocking reads and writes (not inside BEGIN ... COMMIT)
DROP INDEX CONCURRENTLY orders_status_created_idx;

Dokumentācija uzskaita CONCURRENTLY ierobežojumus: viens indekss vienā komandā, bez CASCADE, ne transakcijas blokā un ne indeksiem uz sadalītām tabulām. Dzēsiet vienu indeksu vienā migrācijas solī un izslēdziet transakcijas ietvaru, ko jūsu migrāciju rīks pievieno pēc noklusējuma.

Kas mainās lielās datubāzēs

Galvenās atziņas

PostgreSQL Indeksi Veiktspēja Monitorings
Sadarbība ar mums

Pārāk daudz indeksu, lēna rakstīšana?

Mēs pārskatām indeksu lietojumu primārajā serverī un katrā replikā, atrodam, ko var droši noņemt, un dzēšam to bez dīkstāves lielās, noslogotās datubāzēs.

Sazināties Vairāk no emuāra