Anza na mpango wa query, kisha rekebisha gharama kubwa zaidi. Tumia EXPLAIN/ kuona kile ambacho hifadhidata inafanya, ongeza sahihi, kuondoa queries, na chagua data unayohitaji tu.
Anza na mpango wa query, kisha rekebisha gharama kubwa zaidi. Tumia EXPLAIN/ kuona kile ambacho hifadhidata inafanya, ongeza sahihi, kuondoa queries, na chagua data unayohitaji tu.
EXPLAIN ANALYZEEXPLAIN ANALYZE inaonyesha mpango wa utekelezaji na matango halisi. Seq Scan juu ya meza kubwa ni bendera nyekundu ya kawaida:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders (cost=0.00..18500 rows=120)
-- Filter: (customer_id = 42)
-- rows removed by filter: 999880 ← scanned the whole table!
Index hubadilisha scan kamili kuwa utaftaji wa haraka:
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Now: Index Scan using idx_orders_customer (cost=0.42..8.5 rows=120)
-- 1,000,000-row scan → ~120-row lookup
Indexes ina gharama ya uandishi: kila INSERT/UPDATE lazima pia isasanisha index, kwa hivyo indexa safu ambapo unapochuja/kuunganisha/kuagiza — si kila safu. Index ya kitengano (customer_id, created_at) inatumika kwa WHERE customer_id = ? ORDER BY created_at katika muundo mmoja.
Tatizo la N+1 — query moja kwa orodha, kisha moja kwa kila safu — ni sababu ya juu ya polepole. Ibadilishe na kutaka haraka, JOIN, au kundi IN (...):
-- N+1: 1 + 100 queries
SELECT * FROM orders; -- then per order: SELECT * FROM users WHERE id = ?
-- Fixed: one JOIN, only needed columns
SELECT o.id, o.total, u.name
FROM orders o JOIN users u ON u.id = o.user_id;
Ria chagua safu tu inayohitajika (epuka SELECT *) na paginejte na LIMIT/keyset pagination ili usiweze kumia kamu milioni.
Hifadhidata ni maadhimisho ya kawaida. Kusoma mpango inakuambia kwa nini query ni polepole badala ya kushangilia; index sahihi, kuondoa N+1, na kufanya matokeo jela mara kwa mara hubadilisha queries za sekunde kuwa milisekunde — bila kuongeza hardware.