Ingxenye yonyaka edlule - umphakathi ku-PostgreSQL.
Ezinyangeni ezedlule senze ngaye , walungiselela isifinyezo ngokusekelwe ezincomweni ezinikezayo ... kodwa okubaluleke kakhulu, siqoqe impendulo yakho futhi sabheka izimo zokusetshenziswa kwangempela.
Futhi manje sesilungele ukukutshela ngezici ezintsha ongazisebenzisa.
Ukusekelwa kwamafomethi wepulani ahlukene
Hlela kusukela kulogi, kanye nesicelo
Ngokuqondile kusuka kukhonsoli, sikhetha ibhulokhi yonke, siqala emgqeni nge Umbhalo Wombuzo, nazo zonke izikhala eziholayo:
Query Text: INSERT INTO dicquery_20200604 VALUES ($1.*) ON CONFLICT (query)
DO NOTHING;
Insert on dicquery_20200604 (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
Conflict Resolution: NOTHING
Conflict Arbiter Indexes: dicquery_20200604_pkey
Tuples Inserted: 1
Conflicting Tuples: 0
Buffers: shared hit=9 read=1 dirtied=1
-> Result (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)
... bese ujikijela yonke into ekopishwe ngqo endaweni yohlelo, ngaphandle kokuhlukanisa noma yini:
Kokukhiphayo, siphinde sithole ibhonasi kuhlelo oluhlakaziwe ithebhu yokuqukethwe, lapho isicelo sethu sivezwa khona ngobuhle baso:
I-JSON kanye ne-YAML
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pg_class;"[
{
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Relation Name": "pg_class",
"Alias": "pg_class",
"Startup Cost": 0.00,
"Total Cost": 1336.20,
"Plan Rows": 13804,
"Plan Width": 539,
"Actual Startup Time": 0.006,
"Actual Total Time": 1.838,
"Actual Rows": 10266,
"Actual Loops": 1,
"Shared Hit Blocks": 646,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 5.135,
"Triggers": [
],
"Execution Time": 2.389
}
]"Ngisho nezingcaphuno zangaphandle, njengamakhophi we-pgAdmin, ngisho nangaphandle - siphonsa ensimini efanayo, okuphumayo kungubuhle:
Ukubuka Okuthuthukile
Isikhathi Sokuhlela / Isikhathi Sokwenza
Manje ungabona kangcono ukuthi sishonephi isikhathi esengeziwe lapho usebenzisa umbuzo:
I/O Isikhathi
Kwesinye isikhathi kufanele ubhekane nesimo lapho, ngokwezinsiza, kubonakala sengathi akufundwanga kakhulu futhi kubhaliwe, kepha kubonakala sengathi isikhathi sokubulawa sikhulu ngokungahambisani nesizathu esithile.
Kufanele kushiwo lapha:Oh, mhlawumbe, ngaleso sikhathi idiski kuseva yayigcwele kakhulu, yingakho kuthathe isikhathi eside kangaka ukufunda!"Kodwa ngandlela-thile akunembile kakhulu ...
Kodwa kunganqunywa ngendlela enokwethenjelwa ngokuphelele. Iqiniso liwukuthi phakathi kwezinketho zokucushwa kweseva ye-PG kukhona :
Inika amandla imisebenzi ye-I/O enesikhathi. Lesi silungiselelo sikhutshazwe ngokuzenzakalela, njengoba sidinga ukuthi isistimu yokusebenza ihlale ibuza ngesikhathi samanje, okungabambezela izinto kakhulu kwezinye izinkundla. Ungasebenzisa insiza ye-pg_test_timing ukuze ulinganisele phezulu kwesikhathi endaweni yakho. Izibalo ze-I/O zingatholwa ngokubuka kwe-pg_stat_database, kokukhishwayo kwe-CHAZA (uma kusetshenziswa ipharamitha ye-BUFFERS) nangokubuka pg_stat_statements.
Le nketho ingaphinda inikwe amandla ngaphakathi kweseshini yasendaweni:
SET track_io_timing = TRUE;Nokho, manje ingxenye engcono kakhulu ukuthi sifunde ukuqonda nokubonisa le datha, sicabangela zonke izinguquko zesihlahla sokubulala:
Lapha ungabona ukuthi ku-0.790ms wesikhathi esiphelele sokwenza, u-0.718ms uthathe ukufunda ikhasi elilodwa ledatha, u-0.044ms - ukulibhala, futhi u-0.028ms kuphela owachithwa kuwo wonke omunye umsebenzi owusizo!
Ikusasa nge-PostgreSQL 13
Ukuze uthole ukubuka konke okusha, bona , futhi sikhuluma ngokuqondile ngezinguquko ezinhlelweni.
Amabhafa wokuhlela
Ukubalwa kwezimali kwezinsiza ezinikezwe isihleli kubonakala kwesinye isiqeshana esingahlobene ne-pg_stat_statements. CHAZA ngenketho ye-BUFFERS izobika inani lamabhafa asetshenziswe phakathi nesigaba sokuhlela:
Seq Scan on pg_class (actual rows=386 loops=1) Buffers: shared hit=9 read=4 Planning Time: 0.782 ms Buffers: shared hit=103 read=11 Execution Time: 0.219 ms
Uhlobo olukhuphukayo
Ezimweni lapho ukuhlunga ngokhiye abaningi (k1, k2, k3β¦) kudingekile, umhleli manje angakwazi ukusizakala ngokwazi ukuthi idatha isihlungwe ngokhiye abambalwa bokuqala (isb. u-k1 no-k2). Kulokhu, awukwazi ukuhlela kabusha yonke idatha kabusha, kodwa uwahlukanise ngamaqembu alandelanayo anamanani afanayo we-k1 ne-k2, futhi "uhlele kabusha" ngokhiye u-k3.
Ngakho-ke, konke ukuhlunga kuhlukana kube ukuhlelwa okuningana okulandelanayo kosayizi omncane. Lokhu kunciphisa inani lememori edingekayo, futhi kukuvumela ukuthi ubuyisele idatha yokuqala ngaphambi kokuthi lonke uhlobo luqedwe.
Incremental Sort (actual rows=2949857 loops=1) Sort Key: ticket_no, passenger_id Presorted Key: ticket_no Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB -> Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1) Planning Time: 2.137 ms Execution Time: 2230.019 ms
Ukuthuthukiswa kwe-UI/UX
Izithombe-skrini zikhona yonke indawo!
Manje kuthebhu ngayinye kukhona ithuba lokushesha thatha isithombe-skrini sethebhu ebhodini lokunamathisela kubo bonke ububanzi nokujula kwethebhu - "ukubona" ββkwesokudla phezulu:
Empeleni, izithombe eziningi zale ncwadi zitholwe ngale ndlela.
Izincomo kumanodi
Azikho nje kuphela eziningi zazo, kodwa mayelana ngamunye ongakwenza ngokulandela isixhumanisi:
Isusa kusukela kungobo yomlando
Abanye baye bacela ikhono susa "absolutely" ngisho nezinhlelo ezingashicilelwe kungobo yomlando - sicela, vele uchofoze isithonjana esihambisanayo:
Hhayi-ke, singakhohlwa ukuthi sinakho lapho ungabhala khona imibono neziphakamiso zakho.
Source: www.habr.com