Ka hapalua makahiki aku nei - lehulehu i PostgreSQL.
I nā mahina i hala iho nei ua hana mākou e pili ana iā ia , hoʻomākaukau i kahi hōʻuluʻulu e pili ana i nā ʻōlelo aʻoaʻo e hāʻawi ai ... akā ʻo ka mea nui loa, ua hōʻiliʻili mākou i kāu manaʻo a nānā i nā hihia hoʻohana maoli.
A i kēia manawa ua mākaukau mākou e kamaʻilio e pili ana i nā manawa hou e hiki ai iā ʻoe ke hoʻohana.
Kākoʻo no nā ʻano hoʻolālā like ʻole
E hoʻolālā mai ka log, me ka noi
Mai ka console, koho i ka poloka holoʻokoʻa, e hoʻomaka ana mai ka laina me Palapala Nīnau, me nā wahi alakaʻi āpau:
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)
... a kau i nā mea a pau i kope pololei ʻia i loko o ke kahua hoʻolālā, me ka hoʻokaʻawale ʻole i kekahi mea:
I ka hopena, loaʻa iā mākou kahi bonus i ka hoʻolālā i hoʻokaʻawale ʻia a "context" tab, kahi i hoikeia mai ai ko makou noi me kona nani.
ʻO JSON a me 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
}
]"Me nā huaʻōlelo waho, e like me ka pgAdmin kope, a i ʻole - hoʻolei mākou iā ia i loko o ka māla like, a nani ka hopena:
ʻIkeʻike kiʻekiʻe
Manawa Hoʻolālā / Manawa Hoʻokō
I kēia manawa hiki iā ʻoe ke ʻike maikaʻi i kahi i hoʻohana ʻia ai ka manawa hou i ka hoʻokō ʻana i ka nīnau:
I/O manawa
I kekahi manawa pono ʻoe e hoʻoponopono i kahi kūlana, ma ke ʻano o nā kumuwaiwai, me he mea lā ʻaʻole i nui ka heluhelu ʻana a kākau ʻia, akā ʻo ka manawa hoʻokō me he mea lā incongruously lōʻihi.
Eia mākou e ʻōlelo: "ʻAe, i kēlā manawa ua hoʻomāhuahua loa ka disk ma ke kikowaena, ʻo ia ke kumu i lōʻihi ai ka heluhelu ʻana!"Akā ʻaʻole pololei loa kēia ...
Akā, hiki ke hoʻoholo me ka hilinaʻi loa. ʻO ka ʻoiaʻiʻo aia ma waena o nā koho hoʻonohonoho PG server aia :
Hiki i ka manawa o nā hana I/O. Hoʻopaʻa ʻia kēia koho no ka mea pono e nīnau mau i ka ʻōnaehana hana no kēia manawa, hiki ke hoʻolōʻihi i ka hana ma kekahi mau paepae. No ke koho ʻana i ke kumukūʻai o ka manawa ma kāu kahua, hiki iā ʻoe ke hoʻohana i ka pono pg_test_timing. Hiki ke loaʻa nā ʻikepili I/O ma o ka nānā pg_stat_database, i ka hoʻopuka EXPLAIN (ke hoʻohana ʻia ka ʻāpana BUFFERS) a ma o ka ʻike pg_stat_statements.
Hiki ke hoʻohana ʻia kēia koho i loko o kahi kau kūloko:
SET track_io_timing = TRUE;ʻAe, ʻo ka ʻāpana maikaʻi loa ua aʻo mākou e hoʻomaopopo a hōʻike i kēia ʻikepili me ka noʻonoʻo ʻana i nā loli āpau o ka lāʻau hoʻokō:
Maʻaneʻi hiki iā ʻoe ke ʻike mai ka 0.790ms o ka manawa hoʻokō, 0.718ms ka heluhelu ʻana i hoʻokahi ʻaoʻao ʻikepili, 0.044ms i kākau iā ia, a ʻo 0.028ms wale nō i hoʻohana ʻia ma nā hana pono ʻē aʻe!
ʻO ka wā e hiki mai ana me PostgreSQL 13
Hiki iā ʻoe ke ʻike i ka ʻike piha o nā mea hou , a ke kamaʻilio nei mākou e pili ana i nā loli i nā hoʻolālā.
Nā pale hoʻolālā
Hōʻike ʻia ka moʻohelu kālā no nā kumuwaiwai i hāʻawi ʻia i ka mea hoʻonohonoho i kahi pāpili ʻaʻole pili i pg_stat_statements. E hōʻike ka EXPLAIN me ke koho BUFFERS i ka helu o nā pale i hoʻohana ʻia i ka wā hoʻolālā:
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
Hoʻokaʻawale hoʻonui
I nā hihia e pono ai ka hoʻokaʻawale ʻana i nā kī he nui (k1, k2, k3...), hiki i ka mea hoʻolālā ke hoʻohana i ka ʻike ua hoʻokaʻawale ʻia ka ʻikepili ma kekahi o nā kī mua (e laʻa, k1 a me k2). I kēia hihia, ʻaʻole hiki iā ʻoe ke hoʻokaʻawale hou i nā ʻikepili āpau, akā e hoʻokaʻawale iā ia i nā pūʻulu kūleʻa me nā waiwai like o k1 a me k2, a "hoʻokaʻawale" iā ia ma ke kī k3.
No laila, ua māhele ʻia ka hoʻokaʻina holoʻokoʻa i nā ʻano like ʻole o ka nui liʻiliʻi. Hoʻemi kēia i ka nui o ka hoʻomanaʻo e pono ai a hiki i ka ʻikepili mua ke hoʻopuka ʻia ma mua o ka pau ʻana o ka hoʻokaʻawale ʻana.
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
Hoʻomaikaʻi UI/UX
Nā kiʻi paʻi kiʻi, aia lākou ma nā wahi a pau!
I kēia manawa ma kēlā me kēia pā he manawa kūpono e wikiwiki e kiʻi i ke kiʻi paʻi kiʻi o ka pā i ka papa ʻoki ka laulā a me ka hohonu o ka pā - "ʻike" ʻākau-luna:
ʻO ka ʻoiaʻiʻo, ʻo ka hapa nui o nā kiʻi no kēia puke i loaʻa ma kēia ala.
Manaʻo nodes
ʻAʻole wale ka nui o lākou, akā hiki iā ʻoe ke kamaʻilio e pili ana i kēlā me kēia ma ka hahai ʻana i ka loulou:
Holoi mai ka waihona
Ua noi maoli kekahi poʻe e hoʻohui i ke koho holoi "holo loa" ʻo nā hoʻolālā ʻaʻole i paʻi ʻia ma ka waihona - e kaomi wale i ka ikona kūpono:
ʻAe, mai poina iā mākou , kahi e hiki ai iā ʻoe ke kākau i kāu mau manaʻo a me nā manaʻo.
Source: www.habr.com