Speed Up Your PostgreSQL Queries: Understanding When Indexes Aren't Used

-- Table with customer data
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  city VARCHAR(255)
);

-- Index on the city column
CREATE INDEX idx_city ON customers(city);

-- Scenario 1: Retrieving a specific customer by city (uses index)
SELECT * FROM customers WHERE city = 'New York';

-- Scenario 2: Retrieving all customers in California (might use sequential scan)
SELECT * FROM customers WHERE city = 'Los Angeles' OR city = 'San Francisco';

In scenario 1, searching for a specific city (New York) is likely to leverage the index for faster lookup.

1. Refine your query: Restructure your query to target a smaller subset of data. This can significantly reduce the need for a full scan.

• Use more specific conditions: Instead of searching for "OR" conditions on indexed columns, see if you can break them down into separate queries or use a more specific filter.

1. Optimize your indexes:

• Partial indexes: If you only query on a portion of the indexed column, consider creating a partial index that covers just the relevant data.
• Multiple indexes: Depending on your queries, you might benefit from creating additional indexes on frequently used columns or combinations of columns.