The last plan read 50 000 rows to find one. An indexindexA sorted structure of a column's values, each with a pointer to its row, kept next to the table lets PostgreSQL go straight to the row.

Create an index

CREATE INDEX members_email_idx ON members (email);

A common name pattern is table_column_idx. The index is a separate structure. The rows of members do not change.

The catalog view pg_indexes lists the indexes of a table. indexdef is the statement that creates each one:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'members';

What an index holds

The default index type is a B-tree. It keeps every value of the column in sorted order, each with a pointer to its row. The values sit in a tree of pages, so PostgreSQL reaches any value in a few steps, not 50 000.

Text sorts character by character, so user7799 comes before user779@: the character 9 sorts before @.

Read the new plan

Index Scan using members_email_idx on members  (cost=0.41..8.43 rows=1 width=44)
  Index Cond: (email = 'user77@example.com'::text)

The total cost fell from 1093.00 to 8.43. With EXPLAIN ANALYZE, the execution time falls from about 4 ms to about 0.1 ms.

When more rows match, the plan has two steps:

Bitmap Heap Scan on members  (cost=4.48..87.46 rows=25 width=44)
  Recheck Cond: (joined_on = '2021-06-01'::date)
  ->  Bitmap Index Scan on members_joined_idx  (cost=0.00..4.48 rows=25 width=0)
        Index Cond: (joined_on = '2021-06-01'::date)

The heap is the table itself. Both plans use the index; a Seq Scan does not.

Remove an index

DROP INDEX members_email_idx;

The rows stay. Plans go back to the Seq Scan.