Common query optimization patterns in PostgreSQL

Introduction

Query optimization helps your database find answers faster. It saves time and computer power.

When your database queries take too long to finish.

When you want to reduce the load on your database server.

When you have many users accessing data at the same time.

When you want to make your app or website feel faster.

When you want to save money on database resources.

Syntax

PostgreSQL

-- Example pattern: Use indexes
CREATE INDEX index_name ON table_name(column_name);

-- Example pattern: Use EXPLAIN to check query plan
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

Indexes help the database find rows quickly, like an index in a book.

EXPLAIN shows how the database plans to run your query, helping you spot slow parts.

Examples

This creates an index on the email column in the users table to speed up searches by email.

PostgreSQL

CREATE INDEX idx_users_email ON users(email);

This shows the query plan so you can see if the index is used.

PostgreSQL

EXPLAIN SELECT * FROM users WHERE email = '[e-mail]';

Select only needed columns to reduce data transfer and speed up the query.

PostgreSQL

SELECT id, name FROM users WHERE active = true;

Use range queries with proper conditions to allow index use.

PostgreSQL

SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';

Sample Program

This example creates a products table, adds some data, creates an index on the category column, and then runs a query filtered by category. EXPLAIN ANALYZE shows how the index helps speed up the query.

PostgreSQL

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price NUMERIC NOT NULL
);

INSERT INTO products (name, category, price) VALUES
('Pen', 'Stationery', 1.20),
('Notebook', 'Stationery', 2.50),
('Coffee Mug', 'Kitchen', 5.00),
('Desk Lamp', 'Electronics', 15.00);

-- Create index on category
CREATE INDEX idx_products_category ON products(category);

-- Query using the index
EXPLAIN ANALYZE SELECT * FROM products WHERE category = 'Stationery';

Important Notes

Indexes speed up searches but slow down inserts and updates, so use them wisely.

Always test your queries with EXPLAIN or EXPLAIN ANALYZE to see if your changes help.

Selecting only needed columns reduces data size and speeds up queries.

Summary

Use indexes to help the database find data faster.

Check query plans with EXPLAIN to understand performance.

Write queries that only get the data you need.