CREATE INDEX idx_orders_customer ON orders(customer_id); CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, total);

Index all join columns

Create composite indexes for multiple columns

Use covering indexes when possible

Update statistics regularly

Analyze with EXPLAIN

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

FILTER

Applies an aggregate only to rows that satisfy an extra predicate.

COUNT(*) FILTER (WHERE status = 'active')

ROWS / RANGE

Defines how a window frame is sliced around the current row.

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW

Index all join columns

Create composite indexes for multiple columns

Use covering indexes when possible

Update statistics regularly

Analyze with EXPLAIN

Index ALL join columns (foreign keys and primary keys)

Use covering indexes for frequently accessed columns

Filter with WHERE to reduce rows

Keep table statistics updated

Use EXPLAIN to verify index usage

Test with production data volumes

Monitor query performance

Consider denormalization for read-heavy workloads