Query Optimization

Query optimization is the work of reducing the time and resources a database query consumes. It happens automatically in the database planner and manually when developers add indexes or rewrite queries.

Why it matters

Slow queries are a leading cause of slow pages and timeouts, and one bad query can saturate a database and take a whole site down. The usual workflow is to find the slowest queries, inspect their execution plans, then fix the cause, often a missing index, a function on an indexed column, or fetching far more rows than needed.

Example

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders ... rows=1200000

CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Index Scan using idx_orders_customer ... rows=18

Common mistakes

• Optimizing by guesswork instead of reading the execution plan and measuring.
• Selecting every column and row, then filtering in application code.
• Adding indexes for one query without noticing the slowdown they cause on writes.

Related

• Query execution plan
• Database index
• N+1 query
• SQL JOIN

यह पेज सामान्य जानकारी है, जिसे हमारी संपादकीय टीम ने लिखा है और दस्तावेज़ीकरण बदलने पर अपडेट किया जाता है। सर्वर और प्रदाता अलग-अलग होते हैं, इसलिए बदलाव पहले सुरक्षित तरीके से जाँचें। देखें: संपादकीय नीति और अस्वीकरण.