Theka la chaka chapitacho - pagulu ku PostgreSQL.
Miyezi yapitayi tachita za iye , anakonza chidule kutengera malingaliro omwe amapereka ...
Ndipo tsopano takonzeka kukuuzani za zatsopano zomwe mungagwiritse ntchito.
Thandizo lamitundu yosiyanasiyana yamapulani
Konzani kuchokera pachipika, pamodzi ndi pempho
Mwachindunji kuchokera ku console, timasankha chipika chonse, kuyambira pamzere ndi Mawu Ofunsa, ndi mipata yonse yotsogolera:
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)
... ndikuponya zonse zomwe zakopedwa mwachindunji m'mundamo, osalekanitsa chilichonse:
Pazotulutsa, timapezanso bonasi ku dongosolo lophwanyidwa nkhani tabu, pamene pempho lathu likuperekedwa mu ulemerero wake wonse:
JSON ndi 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
}
]"Ngakhale ndi mawu akunja, monga makope a pgAdmin, ngakhale opanda - timaponyera m'munda womwewo, zotsatira zake ndi kukongola:
Kuwoneka Mwapamwamba
Nthawi Yokonzekera / Nthawi Yochita
Tsopano mutha kuwona bwino komwe nthawi yowonjezera idapita pofunsa funso:
I/O Nthawi
Nthawi zina mumayenera kuthana ndi vuto lomwe, pankhani yazachuma, zikuwoneka kuti sizinawerengedwe ndikulembedwa, koma zikuwoneka kuti nthawi yophedwayo ndi yayikulu mosagwirizana pazifukwa zina.
Izo ziyenera kunenedwa apa:O, mwina, panthawiyo diski pa seva inali yodzaza kwambiri, ndichifukwa chake zidatenga nthawi yayitali kuti ziwerenge!"Koma mwanjira ina sizolondola ...
Koma zikhoza kutsimikiziridwa modalirika kwambiri. Chowonadi ndi chakuti pakati pa zosankha za kasinthidwe ka seva ya PG pali :
Imayatsa ntchito za I/O zanthawi yake. Izi zimayimitsidwa mwachisawawa, chifukwa zimafuna kuti makina ogwiritsira ntchito azifunsa nthawi zonse, zomwe zingachepetse zinthu kwambiri pamapulatifomu ena. Mutha kugwiritsa ntchito pg_test_timing kuti muyerekeze kuchuluka kwa nthawi papulatifomu yanu. Ziwerengero za I/O zitha kupezeka kudzera pa pg_stat_database view, mu EXPLAIN output (pamene BUFFERS parameter ikugwiritsidwa ntchito) ndi kudzera pa pg_stat_statements view.
Izi zithanso kuyatsidwa mkati mwa gawo lapafupi:
SET track_io_timing = TRUE;Chabwino, tsopano gawo labwino kwambiri ndikuti taphunzira kumvetsetsa ndikuwonetsa izi, poganizira masinthidwe onse a mtengo wakupha:
Apa mutha kuwona kuti mwa 0.790ms ya nthawi yonse yophedwa, 0.718ms idatenga kuwerenga tsamba limodzi la data, 0.044ms - ndikulemba, ndipo 0.028ms yokha idagwiritsidwa ntchito pazinthu zina zonse!
Tsogolo ndi PostgreSQL 13
Kuti muwone mwachidule zatsopano, onani , ndipo tikukamba za kusintha kwa mapulani.
Kupanga mabafa
Kuwerengera kwazinthu zomwe zaperekedwa kwa wokonza zikuwonekera pachigamba china chomwe sichikugwirizana ndi pg_stat_statements. Fotokozani ndi njira ya BUFFERS ifotokoza kuchuluka kwa mabafa omwe amagwiritsidwa ntchito panthawi yokonzekera:
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
Mtundu wowonjezera
Ngati kusanja ndi makiyi ambiri (k1, k2, k3β¦) kumafunika, wokonza mapulani tsopano atha kutengapo mwayi podziwa kuti deta yasanjidwa kale ndi makiyi angapo oyamba (monga k1 ndi k2). Pankhaniyi, simungathe kusinthanso deta yonse mwatsopano, koma muwagawe m'magulu otsatizana omwe ali ndi mfundo zofanana za k1 ndi k2, ndi "kukonzanso" ndi kiyi k3.
Chifukwa chake, kusanja konseko kumagawika m'magulu angapo motsatizana ang'onoang'ono. Izi zimachepetsa kuchuluka kwa kukumbukira komwe kumafunikira, komanso kumakupatsani mwayi wobweza deta yoyamba musanamalize kusanja.
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
Kusintha kwa UI/UX
Zithunzi zili paliponse!
Tsopano pa tabu iliyonse pali mwayi mwamsanga jambulani chithunzi cha tabu ku clipboard kwa m'lifupi ndi kuya kwa tabu - "kuona" kumanja pamwamba:
Kwenikweni, zithunzi zambiri za mβbukuli zinapezedwa motere.
Malangizo pa mfundo
Palibe zambiri za iwo, koma za aliyense yemwe mungathe potsatira ulalo:
Kuchotsa pankhokwe
Ena apempha kuti athe kufufuta "mtheradi" ngakhale mapulani omwe sanasindikizidwe pazosungidwa - chonde, ingodinani chizindikiro chofananira:
Chabwino, tisaiwale kuti tatero komwe mungalembe ndemanga zanu ndi malingaliro anu.
Source: www.habr.com