SQL SELECT Only Needed Columns

SELECT * costs more than it looks like — in I/O, and in whether an index-only scan is even possible.

What & Why

Requesting every column means the database has to fetch every column's data for every matching row, even the ones you'll never look at. Naming only the columns you need reduces I/O — and can sometimes unlock an index-only scan, where the answer comes entirely from the index without touching the table at all.

See How It Works

BUSINESS QUESTION

Growth wants a focused recent-event export without the large properties payload.

id	user_id	event_name	created_at	properties
1001	101	signup	2024-01-15 09:12:00+00	{"source":"organic"}
1002	101	activated	2024-01-16 14:25:00+00	{"step":"workspace"}
1003	102	signup	2024-02-10 11:08:00+00	{"source":"paid"}
1004	103	report_viewed	2024-03-12 16:40:00+00	{"report":"retention"}

EXAMPLE QUERY

SELECT
  id AS event_id,
  user_id,
  event_name,
  created_at
FROM growth.events
ORDER BY created_at DESC, event_id
LIMIT 100;

RESULT — focused event projection

event_id	user_id	event_name	created_at
1004	103	report_viewed	2024-03-12 16:40:00+00
1003	102	signup	2024-02-10 11:08:00+00
1002	101	activated	2024-01-16 14:25:00+00
1001	101	signup	2024-01-15 09:12:00+00

Only the four requested columns are returned, newest first.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice