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.
- 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.
- 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.
postgresql indexing sequence