ืึท ืืึทืื ืืึธืจ ืฆืืจืืง - ืขืคื ืืืขื ืฆื PostgreSQL.
ืืื ืื ืืขืฆืืข ืืืฉืื ืืืจ ืืึธืื ืืขืืืื ืืืขืื ืืื , ืฆืืืขืืจืืื ืึท ืงืืฆืขืจ ืืืืืจื ืืืืฃ ืื ืจืขืงืึทืืึทื ืืืืฉืึทื ื ืขืก ืืื ... ืึธืืขืจ ืจืืึฟ ืืืคึผืึธืจืืึทื ืืื, ืืืจ ืืขืืืืื ืืืื ืืึทืืขืจืงืื ืืขื ืืื ืืขืงืืงื ืืืืฃ ืคืึทืงืืืฉ ื ืืฆื ืงืึทืกืขืก.
ืืื ืืืฆื ืืืจ ืืขื ืขื ืืจืืื ืฆื ืจืขืื ืืืขืื ื ืืึทืข ืึทืคึผืขืจืืื ืึทืืื ืืืึธืก ืืืจ ืงืขื ืขื ื ืืฆื.
ืฉืืืฆื ืคึฟืึทืจ ืคืึทืจืฉืืืขื ืข ืคึผืืึทื ืคึฟืึธืจืืึทืืืจืื ืืขื
ืคึผืืึทื ืคืื ืื ืงืืึธืฅ, ืฆืืืืืขื ืืื ืื ืืงืฉื
ืืืืื ืคึฟืื ืื ืงืึทื ืกืึธืื, ืืืืกืงืืืึทืื ืื ืืื ืฆืข ืืืึธืง, ืกืืึทืจืืื ื ืคืื ืื ืฉืืจื ืืื ืึธื ืคึฟืจืขื ืืขืงืกื, ืืื ืึทืืข ืืืืื ื ืกืคึผืืืกืึทื:
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)
... ืืื ืฉืืขืื ืึทืืฅ ืงืึทืคึผืื ืืืืึทื ืืื ืื ืคึผืืึทื ืคืขืื, ืึธื ืกืขืคึผืขืจืืืืื ื ืขืคึผืขืก:
ืืื ืื ืกืืฃ ืืืจ ืืึทืงืืืขื ืึท ืืึธื ืืก ืฆื ืื ืืืกืึทืกืขืืืึทืื ืคึผืืึทื ืืื "ืงืึธื ืืขืงืกื" ืงืืืืื, ื ืฐ ืืื ืืืข ืจ ืืงืฉื ืฐืขืจ ื ืืขืืขื ื ืื ื ืืื ืฆ ื ืืืื :
JSON ืืื 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
}
]"ืึธืืขืจ ืืื ืคืื ืืจืืืกื ืืืง ืฆืืืืจื, ืืื ืคึผืืึทืืืื ืงืืคืืขืก, ืึธืืขืจ ืึธื - ืืืจ ืืืึทืจืคื ืขืก ืืื ืื ืืขืืืข ืคืขืื, ืืื ืืขืจ ืจืขืืืืืึทื ืืื ืฉืืื ืงืืื:
ืึทืืืึทื ืกืืจืืข ืืืืกืืึทืืืืึทืืืึธื
ืคึผืืึทื ืืจืื ื ืฆืืื / ืืืจืืคืืจืื ื ืฆืืื
ืืืฆื ืืืจ ืงืขื ืขื ืืขืกืขืจ ืืขื ืืื ืื ืขืงืกืืจืข ืฆืืื ืืื ืคืืจืืจืืื ืฆื ืืืจืืคืืจื ืื ืึธื ืคึฟืจืขื:
ืืื / ืึธ ืืืืืื ื
ืืื ืืืจ ืืึธืื ืฆื ืืึทื ืืืขื ืืื ืึท ืกืืืืึทืฆืืข ืืื, ืืื ืืขืจืืื ืขื ืคืื ืจืขืกืืจืกื, ืขืก ืืืื ื ืึทื ื ืืฉื ืฆื ืคืื ืืื ืืขืืืขื ืืืืขื ืขื ืืื ืืขืฉืจืืื, ืึธืืขืจ ืื ืืืจืืคืืจืื ื ืฆืืื ืืืื ื ืฆื ืืืื ืื ืงืึทื ืืจืืึทืกืื ืืึทื ื.
ืืึธ ืืืจ ืืึธืื ืฆื ืืึธืื: "ืึธื, ืืืกืืึธืืข ืืื ืืขื ืืึธืืขื ื ืื ืืืกืง ืืืืฃ ืื ืกืขืจืืืขืจ ืืื ืืืื ืึธืืืืขืจืืึธืืืื, ืืึธืก ืืื ืืืึธืก ืขืก ืืขื ืืืขื ืึทืืื ืืึทื ื ืฆื ืืืืขื ืขื!"ืึธืืขืจ ืขืคืขืก ืืื ืืึธืก ื ืืฉื ืืืืขืจ ืคึผืื ืืืขื ...
ืืืขืจ ืืขื ืงืขื ืขื ืืืื ืืืฉืืืกื ืืขืืึทืืจืข ืจืืืืืึทืืื. ืืขืจ ืคืึทืงื ืืื ืึทื ืฆืืืืฉื ืื PG ืกืขืจืืืขืจ ืงืึทื ืคืืืืขืจืืืฉืึทื ืึธืคึผืฆืืขืก ืขืก ืืื :
ืื ืืืืึทืื ืืืืืื ื ืคืื ื / ืึธ ืึทืคึผืขืจืืืฉืึทื ื. ืืขืจ ืึธืคึผืฆืืข ืืื ืคืึทืจืงืจืืคึผืื ืืืจื ืคืขืืืงืืึทื ืืืืึทื ืขืก ืจืืงืืืืืขืจื ืงืขืกืืืืขืจืืืง ืึธื ืคึฟืจืขื ืื ืึธืคึผืขืจืืืืื ื ืกืืกืืขื ืคึฟืึทืจ ืื ืงืจืึทื ื ืฆืืื, ืืืึธืก ืงืขื ืขื ืืืืืืืืง ืคึผืึทืืขืืขื ืื ืคืึธืจืฉืืขืืื ื ืืืืฃ ืขืืืขืืข ืคึผืืึทืืคืึธืจืืก. ืฆื ืึธืคึผืฉืึทืฆื ืื ืคึผืจืืึทื ืคืื ืืืืืื ื ืืืืฃ ืืืื ืคึผืืึทืืคืึธืจืืข, ืืืจ ืงืขื ืขื ื ืืฆื ืื pg_test_timing ื ืืฆื. ืืื / ืึธ ืกืืึทืืืกืืืง ืงืขื ืขื ืืืื ืืืงืืืขื ืืืจื ืื pg_stat_database View, ืืื ืื EXPLAIN ืจืขืืืืืึทื (ืืืขื ืื BUFFERS ืคึผืึทืจืึทืืขืืขืจ ืืื ืืขื ืืฆื) ืืื ืืืจื ืื pg_stat_statements ืืืื ืื ื.
ืื ืึธืคึผืฆืืข ืงืขื ืขื ืืืื ืืืื ืขื ืืืืึทืื ืืื ืึท ืืืืข ืกืขืกืืข:
SET track_io_timing = TRUE;ื ื, ืืขืจ ืืขืกืืขืจ ืืืื ืืื ืึทื ืืืจ ืืึธืื ืืขืืขืจื ื ืฆื ืคึฟืึทืจืฉืืืื ืืื ืืืืึทืื ืื ืืึทืื ืืื ืึทืืข ืื ืืจืึทื ืกืคืขืจืืืืฉืึทื ื ืคืื ืื ืืืจืืคืืจืื ื ืืืื:
ืืึธ ืืืจ ืงืขื ืขื ืืขื ืึทื ืคืื 0.790ms ืคืื ืื ืืึทื ืฅ ืืืจืืคืืจืื ื ืฆืืื, 0.718ms ืืขื ืืืขื ืืืืขื ืขื ืืืื ืืึทืื ืืืึทื, 0.044ms ืืขื ืืืขื ืฉืจืืืื ืขืก, ืืื ืืืืื 0.028ms ืืื ืคืืจืืจืืื ืคึฟืึทืจ ืึทืืข ืื ืืขืจืข ื ืืฆืืง ืึทืงืืืืืืืขืื!
ืื ืฆืืงืื ืคึฟื ืืื PostgreSQL 13
ืืืจ ืงืขื ืขื ืืขืคึฟืื ืขื ืึท ืคืื ืืืืขืจืืืืง ืคืื ืื ืึธืืืืืืฉืึทื ื , ืืื ืืืจ ืืขื ืขื ืกืคึผืึทืกืืคืืงืื ืืขืจืขืื ืืืขืื ืขื ืืขืจืื ืืขื ืืื ืคึผืืึทื ื.
ืคึผืืึทื ืืจืื ื ืืึทืคืขืจื
ืึทืงืึทืื ืืื ื ืคึฟืึทืจ ืจืขืกืืจืกื ืึทืืึทืงืืืืื ืฆื ืื ืกืงืขืืืฉืืืขืจ ืืื ืฉืคืืืื ืืื ืื ืื ืืขืจ ืืึทืืข ืืืึธืก ืืื ื ืืฉื ืฉืืื ืฆื pg_stat_statements. ืืขืจืงืืขืจื ืืื ืื BUFFERS ืึธืคึผืฆืืข ืืืขื ืืึทืจืืื ืื ื ืืืขืจ ืคืื ืืึทืคืขืจื ืืขื ืืฆื ืืขืฉืึทืก ืื ืคึผืืึทื ืืจืื ื ืคืึทืกืข:
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
ืื ืงืจืขืืขื ืืึทื ืกืึธืจืืื ื
ืืื ืงืึทืกืขืก ืืื ืกืึธืจืืื ื ืืืืฃ ืคืืืข ืฉืืืกืืขื ืืื ืืืจืฃ (ืง1, ืง2, ืง3...), ืืขืจ ืคึผืืึทื ืขืจ ืงืขื ืขื ืืืฆื ื ืืฆื ืื ืืืืกื ืึทื ืื ืืึทืื ืืขื ืขื ืฉืืื ืืืืกืืขืฉืืขืื ืืืืฃ ืขืืืขืืข ืคืื โโืื ืขืจืฉืืขืจ ืฉืืืกืืขื (ืืืฉื, ืง1 ืืื ืง2). ืืื ืืขื ืคืึทื, ืืืจ ืงืขื ืขื ื ืืฉื ืฉืืึทืขื-ืกืึธืจื ืึทืืข ืื ืืึทืื ืืืืืขืจ, ืึธืืขืจ ืืืืื ืขืก ืืื ืกืึทืงืกืขืกืืื ืืจืืคึผืขืก ืืื ืื ืืขืืืข ืืืึทืืืขืก ืคืื ืง1 ืืื ืง2, ืืื "ืฉืืึทืขื-ืกืึธืจื" ืขืก ืืืจื ืฉืืืกื ืง3.
ืืืื, ืื ืืื ืฆืข ืกืึธืจืืืจืื ื ืืื ืฆืขืืืืื ืืื ืขืืืขืืข ืกืึทืงืกืขืกืืื ืกืึธืจืฅ ืคืื ืงืืขื ืขืจืขืจ ืืจืืืก. ืืึธืก ืจืึทืืืกืึทื ืื ืกืืืข ืคืื โโโโืืืงืึธืจื ืคืืจืืื ืื ืืื ืืืื ืึทืืึทืื ืื ืขืจืฉืืขืจ ืืึทืื ืฆื ืืืื ืคึผืจืึธืืืงืฆืืข ืืืืืขืจ ืื ืืื ืฆืข ืกืึธืจืืื ื ืืื ืืขืขื ืืืงื.
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
UI / UX ืืืคึผืจืืืืืึทื ืฅ
ืกืงืจืขืขื ืฉืึธืฅ, ืืื ืืขื ืขื ืืืืขืืื!
ืืืฆื ืืืืฃ ืืขืืขืจ ืงืืืืื ืขืก ืืื ืึท ืืขืืขืื ืืืื ืฆื ืืขืฉืืืื ื ื ืขืืขื ืึท ืกืงืจืขืขื ืฉืึธื ืคืื ืื ืงืืืืื ืฆื ืื ืงืืืคึผืืึธืจื ืื ืืื ืฆืข ืืจืืื ืืื ืืืคืงืืื ืคืื ืื ืงืืืืื - "ืืขืจืืขื" ืืืืฃ ืื ืจืขืื ืฉืคึผืืฅ:
ืืื ืคืึทืงื, ืจืืึฟ ืคืื ืื ืืืืืขืจ ืคึฟืึทืจ ืืขื ืืืกืืึทืืข ืืขื ืขื ืืืงืืืขื ืึทืืื.
ืจืขืงืึทืืึทื ืืืืฉืึทื ื ืืืืฃ ื ืึธืืื
ื ืื ืืืืื ืืึธืื ืขืก ืืืขืจื ืืขืจ ืคืื ืืื, ืึธืืขืจ ืืืจ ืงืขื ืขื ืืืื ืจืขืื ืืืขืื ืืขืืขืจ ืืืจื ืืขื ืืื ืง:
ืืืืืืื ื ืคืื ืื ืึทืจืงืืืื
ืขืืืขืืข ืืขื ืืฉื ืืึทืงืข ืืขืืขืื ืฆื ืืืืื ืื ืึธืคึผืฆืืข ืืขืงื "ืืึทื ืฅ" ืืคืืื ืคึผืืึทื ื ืืืึธืก ืืขื ืขื ื ืืฉื ืืจืืืก ืืื ืืขืจ ืึทืจืงืืืื - ืืืืข ื ืึธืจ ืืื ืื ืฆืื ืขืืขื ืืืืื:
ื ื, ืคึฟืึทืจืืขืกื ื ืืฉื, ืึทื ืืืจ ืืึธืื , ืืื ืืืจ ืงืขื ืขื ืฉืจืืึทืื ืืืื ืืึทืืขืจืงืื ืืขื ืืื ืคึฟืืจืืืืื.
ืืงืืจ: www.habr.com