Without help, the only way Postgres can find rows is to read the whole table and test each one. That's a sequential scan. It's fine for 13 rows and painful for 300,000.
An index is a sorted lookup structure on one or more columns, like the index at the back of a book. With it, Postgres jumps straight to the matching rows.
CREATE INDEX index_name ON table (column);
Put EXPLAIN ANALYZE in front of a query to run it and see the plan. Look for two things: Seq Scan versus Index Scan (or Bitmap Index Scan), and Execution Time at the bottom.
What gets indexed automatically: primary keys and UNIQUE columns. Foreign key columns do not.posts.user_id has no index until you make one.
Index the columns your queries filter on (WHERE), join on, and sort by (ORDER BY).
A composite index covers several columns: (user_id, created_at). Order matters. It helps queries that filter on user_id, or on user_id and then sort by created_at. It does not help a query that only uses created_at.
A partial index covers only some rows: CREATE INDEX ... ON posts (created_at) WHERE published;
Indexes aren't free. Each one takes disk space and must be updated on every insert, update and delete. Add them for queries you actually run, not for every column.
An index can't help ILIKE '%word%'. A leading wildcard means the match could start anywhere, so there's nothing sorted to jump to.