PostgreSQL EXPLAIN ANALYZE izpilda jūsu vaicājumu un parāda plānotāja izvēlēto plānu ar reāliem laikiem un rindu skaitu blakus katram novērtējumam. Tas ir pats noderīgākais rīks lēnam vaicājumam un arī visbiežāk pārprastais: cilvēki skatās uz izmaksu skaitļiem, kas ir minējumi, un izlaiž rindas, kas patiešām izskaidro problēmu.
Šī rokasgrāmata ir par to, kā izvadi izlasīt ātri: kuri skaitļi ir svarīgi, daži modeļi, kas slēpjas aiz vairuma slikto plānu, un kam pievērst uzmanību, ja tabulā ir miljards rindu, nevis tūkstotis. Ja vēl jāatrod, kuru vaicājumu skatīt, sāciet ar mūsu pg_stat_statements rokasgrāmatu.
Palaidiet to pareizi
Vienkāršs EXPLAIN vaicājumu tikai plāno. EXPLAIN ANALYZE to izpilda. Rokasgrāmata skaidri saka, ka blakusefekti notiek kā parasti, un tās ieteikums INSERT, UPDATE, DELETE, MERGE un līdzīgiem vaicājumiem ir ietīt tos transakcijā un atcelt (EXPLAIN):
-- Read-only query: run it for real
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- Anything that writes: run it inside a transaction and throw it away
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'archived' WHERE created_at < '2025-01-01';
ROLLBACK;
Vienmēr prasiet buferus. Kopš PostgreSQL 18 BUFFERS tiek iekļauts automātiski kopā ar ANALYZE (PostgreSQL 18 laidiena piezīmes); vecākās versijās pievienojiet to paši. Citas opcijas, ko vērts zināt:
SETTINGSuzskaita ar plānotāju saistītos iestatījumus, kas atšķiras no iebūvētajiem noklusējumiem. Noder, ja plāns divos serveros atšķiras.SERIALIZE(PostgreSQL 17+) iekļauj izvades konvertēšanas izmaksas klientam. Pēc noklusējuma EXPLAIN ANALYZE to neiekļauj, tāpat kā tīkla pārsūtīšanu, tāpēc vaicājums, kas atgriež milzīgas rindas, izskatās ātrāks, nekā ir (PostgreSQL 17 laidiena piezīmes).TIMING OFFsaglabā faktisko rindu skaitu, bet atmet katra mezgla laika mērīšanu, kas samazina mērīšanas papildslodzi.GENERIC_PLANplāno vaicājumu ar$1tipa vietturiem, kā to dara sagatavotie vaicājumi. To nevar apvienot arANALYZE.FORMAT JSON, ja gribat plānu ielīmēt vizualizētājā.
Izlasiet vienu mezglu
Index Scan using orders_customer_id_idx on orders
(cost=0.43..8.45 rows=1 width=64)
(actual time=0.031..0.120 rows=12.00 loops=2500)
Index Cond: (customer_id = c.id)
Buffers: shared hit=9800 read=240
cost=0.43..8.45ir sākuma izmaksas un kopējās izmaksas plānotāja nosacītās vienībās, tradicionāli attiecībā pretseq_page_cost = 1.0. Tas nav laiks. Salīdziniet izmaksas starp viena vaicājuma plāniem; nesalīdziniet tās ar milisekundēm (EXPLAIN lietošana).rows=1pirmajās iekavās ir novērtējums rindām, ko mezgls izdos, nevis rindām, ko tas skenē.widthir novērtētais vidējais rindas izmērs baitos.actual time=0.031..0.120ir milisekundēs: laiks līdz pirmajai rindai, tad laiks līdz pēdējai.loops=2500maina visu. Faktiskais laiks un rindas ir vidējie uz vienu izpildi, tāpēc šis mezgls patiesībā aizņēma ap 0,12 ms × 2500 = 300 ms un kopā atgrieza ap 30 000 rindu. Lielākā daļa brīžu "lēnā daļa nav tur, kur gaidīju" rodas, aizmirstot reizināt ar loops.Buffers: shared hit=9800 read=240: hit nozīmē, ka 8 kB lapa jau bija shared buffers, read nozīmē, ka tā nāca no OS kešatmiņas vai diska. Skaitļi ietver visus bērnu mezglus un nav unikālas lapas, tāpēc divreiz nolasīta lapa tiek skaitīta divreiz.
PostgreSQL 18 faktiskās rindas rāda ar decimāldaļām (rows=12.00), jo tie ir vidējie pa cilpām; vecākas versijas tos noapaļo, kas paslēpj atšķirību starp 0,4 un 0 rindām cilpā.
Pieci modeļi aiz vairuma slikto plānu
- Novērtējumi kļūdās 10 reizes vai vairāk. Katrā mezglā, sākot no apakšas, salīdziniet novērtēto
rowsar faktiskorows×loops. Pirmais mezgls, kur tie stipri atšķiras, parasti ir cēlonis; viss virs tā plānots uz nepareiziem skaitļiem. Tipiski risinājumi: palaistANALYZE, palielināt statistikas mērķi šai kolonnai vai pievienot paplašināto statistiku korelētām kolonnām. - Nested loop ar milzīgu cilpu skaitu. Plānotājs gaidīja 10 ārējās rindas un saņēma 200 000, tāpēc iekšējais indeksa skenējums izpildījās 200 000 reižu. Bieži tas ir 1. modelis citā izskatā.
- Daudz
Rows Removed by Filter. Skenējums nolasa miljonu rindu, lai paturētu piecdesmit. Vai nu trūkst indeksa, vai esošais indekss neatbilst nosacījumam (nepareiza kolonnu secība, funkcija uz kolonnas, tipu neatbilstība). - Darbs izlīst uz diska. Sort mezgls parāda, vai kārtots atmiņā vai uz diska, un Hash mezgls ar vairāk nekā vienu batch arī izmanto diska vietu (EXPLAIN lietošana):
Parastais risinājums ir lielāksSort Method: external merge Disk: 812344kB ... Hash (actual time=... rows=... loops=1) Buckets: 131072 Batches: 16 Memory Usage: 4097kBwork_memšim vaicājumam vai sesijai, nevis globāli, vai plāns, kam nav jākārto tik daudz. - Index Only Scan ar daudz
Heap Fetches. Mūsu pieredzē tas nozīmē, ka redzamības karte (visibility map) atpaliek, tāpēc "tikai indeksa" skenējums lielākajai daļai rindu tomēr apmeklē tabulu. Pārbaudiet, vai vacuum šai tabulai tiek līdzi.
(never executed) nozīmē, ka PostgreSQL to izpildes laikā izlaida, piemēram, savienojuma iekšējo pusi, kad ārējā puse neatgrieza nevienu rindu. Tā nav kļūda, un tā izmaksas nav problēmas daļa.Sagatavotie vaicājumi un vispārīgie plāni
Vaicājums, kas psql ir ātrs, bet no lietotnes lēns, bieži ir plānu kešatmiņas problēma. Sagatavotiem vaicājumiem PostgreSQL pirmās piecas izpildes veic ar pielāgotiem (custom) plāniem, tad salīdzina vispārīgo (generic) plānu ar to vidējām izmaksām un var pārslēgties uz vispārīgo (PREPARE). Vispārīgais plāns nevar izmantot faktisko parametra vērtību, tāpēc nevienmērīgi sadalītiem datiem tas var būt briesmīgs, piemēram, ja vienam klientam pieder 40% rindu. Salīdziniet EXPLAIN (GENERIC_PLAN) ar parastu EXPLAIN ar reālu vērtību un, ja vajag, iestatiet plan_cache_mode = force_custom_plan attiecīgajai lomai vai sesijai.
Lielās datubāzēs
EXPLAIN ANALYZE uz 50 rindu izstrādes tabulas pasaka ļoti maz. Rokasgrāmata to saka tieši: rezultātus uz rotaļu izmēra tabulas nevar attiecināt uz lielām tabulām, jo plānotāja izmaksu novērtējumi nav lineāri un tas var izvēlēties citu plānu. Lielās produkcijas sistēmās:
- Sāciet ar vienkāršu EXPLAIN. EXPLAIN ANALYZE izpilda visu vaicājumu. Pārskats, kas aizņem 40 minūtes, arī ar EXPLAIN aizņem 40 minūtes, visu laiku tur savas bloķēšanas un momentuzņēmumu un noslogotā primārajā serverī var aizturēt vacuum. Pirms palaišanas sesijā iestatiet
statement_timeout. - Testējiet uz produkcijas izmēra datiem. Testa kopijai ar 1% rindu ir cita statistika un citi plāni. Ja nevarat testēt produkcijā, izmantojiet nesenu pilna izmēra atjaunošanu, kas vienlaikus pārbauda arī jūsu rezerves kopijas.
- Auksta un silta kešatmiņa ir dažādi vaicājumi. Otrā palaišana bieži ir daudz ātrāka, jo lapas pārgāja no
readuzhit. Tabulām, kas lielākas par RAM, aukstais skaitlis ir tas, ko lietotāji redz pēc restarta vai pārslēgšanās. - Ieslēdziet
track_io_timing, lai buferu izvadē redzētu I/O laiku. Pēc noklusējuma tas ir izslēgts, jo atkārtoti vaicā pulksteni; vispirms pārbaudiet papildslodzi uz savas aparatūras arpg_test_timing(statistikas iestatījumi). - Laika mērīšanas papildslodze summējas. Mezgliem, kas izpildās miljoniem reižu, katra mezgla laika mērīšana var padarīt EXPLAIN ANALYZE daudz lēnāku par pašu vaicājumu. Izmantojiet
TIMING OFF, ja vajag tikai rindu skaitu. - Notveriet plānus produkcijā ar
auto_explain. Tas žurnālā ieraksta plānus vaicājumiem, kas lēnāki par slieksni. Ar ieslēgtulog_analyzekatra mezgla laika mērīšana attiecas uz katru vaicājumu, ne tikai ierakstītajiem, un rokasgrāmata brīdina, ka tam var būt ārkārtīgi negatīva ietekme uz veiktspēju, tāpēc apvienojiet to arlog_timing = offun izlases biežumu (auto_explain):# postgresql.conf session_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '2s' # default -1 = off auto_explain.log_analyze = on # default off auto_explain.log_buffers = on # needs log_analyze auto_explain.log_timing = off # keeps overhead down auto_explain.sample_rate = 0.1 # explain ~10% of statements in each session
Kad zināt, ko plāns stāsta, risinājumi ir ierastie: indeksi, statistika, vacuum, tad atmiņa. Mūsu lēno vaicājumu optimizācijas kontrolsaraksts tos iziet secīgi.
Galvenās atziņas
- EXPLAIN ANALYZE izpilda vaicājumu; rakstīšanu ietiniet
BEGIN/ROLLBACK. - Cost ir plānotāja vienībās, nevis milisekundēs; faktiskais laiks un rindas ir uz cilpu, tāpēc reiziniet ar
loops. - Atrodiet zemāko mezglu, kur novērtētās un faktiskās rindas atšķiras; parasti tas ir pamatcēlonis.
- Meklējiet lielu
Rows Removed by Filter, kārtošanu uz diska, hash ar vairākiem batch un daudz heap fetches. - PostgreSQL 18 buferus iekļauj automātiski un rāda daļskaitļu rindu skaitu.
- Lielās datubāzēs: vispirms vienkāršs EXPLAIN, statement timeout, produkcijas izmēra dati un auto_explain ar izlasi un bez katra mezgla laika mērīšanas.