Newcomers to SQL often find themselves asking: Why are my queries so slow? Sure, they have half a million rows in their table, but they are only fetching a handful using a WHERE clause. How can it take so long just to return a few rows?
The simple answer is: databases don’t know how you’re going to filter the column in your queries. They’re smart, but they can’t read your mind. If you find yourself querying your table using the value of one or two columns (e.g. SELECT * FROM my_table WHERE my_column = my_value;), you should tell Postgres which columns you’re going to frequently filter by creating an index. Creating an index is simple:
CREATE INDEX my_index ON my_table (my_column);
This creates an index with my_column, so queries that filter solely using my_column will be much faster. But what about more complex filtering, say WHERE my_column = my_value AND another_column = another_value? We can cover both columns with a index:
CREATE INDEX my_index ON my_table (my_column, another_column);
After creating your index, you should notice an immediate improvement in the speed of your queries that are filtered based on your index’ columns.