Ishga tayyor · SQL performance, index va EXPLAIN
Daraja: Ilg'or
Taxmin bilan index qo‘shmasdan, real query plan va o‘lchov bilan optimallashtirish.
Tushuntirish
## Ishdagi vazifa Sekin endpointni query plan orqali topish. `EXPLAIN` planner tanlovini, `EXPLAIN ANALYZE` esa queryni bajarib haqiqiy vaqt va qatorlarni ko‘rsatadi; productionda yozuvchi queryga ehtiyot bo‘ling. Estimated va actual rows katta farq qilsa statistika yoki data taqsimoti muammo bo‘lishi mumkin.
## Index Composite index tartibi real `WHERE`, `JOIN` va `ORDER BY`ga mos bo‘ladi. Har index SELECTni tezlatishi mumkin, lekin write, disk va maintenance narxini oshiradi. `%text%` qidiruvi oddiy B-tree bilan hal bo‘lmasligi mumkin. Keyset pagination katta offsetdan barqarorroq va arzonroq.
## Manba [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html)
Kod misoli
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE user_id = 42 AND created_at < :cursor
ORDER BY created_at DESC, id DESC LIMIT 20;
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC, id DESC);Keng tarqalgan xatolar
- Query plan ko‘rmasdan har bir foreign key va filterga alohida index qo‘shish.
Mashqlar
- 10 ming qatorli test datada indexdan oldin/keyin plan va vaqtni solishtiring.
- Offset paginationni keyset paginationga o‘tkazing.
Teglar: ishga-tayyor, postgresql, career-03, performance