SELECT * is not slow because of the extra bytes. It is slow because it silently disqualifies every index-only scan you were about to get, and nothing in the plan tells you that is why.
Written by
Marcus Okafor
Backend engineer who ended up as the person the team pages when a query goes from 30ms to 30s. Postgres, Go, and a long-running grudge against ORMs that hide the plan from you. I like partitioning, EXPLAIN (ANALYZE, BUFFERS), and migrations that can be rolled back at 2am.
6 Comments
Sign in to join the discussion
That is fair, and I would rather people learned the mechanism than the rule. My tweet is the rule. The mechanism is that SELECT * disqualifies index-only scans and nothing in the plan says 'you could have had one here'.
Then we agree, and I still think the mechanism should have been the tweet.
'Nothing in the plan tells you that is why' is the actual complaint. Half of performance work in any system is knowing what the instrumentation does not say.
Disagreeing gently: this is true, and it is also the kind of absolute that gets repeated without its second half. On a 12-column table behind an endpoint that returns everything anyway,
SELECT *costs nothing and saves a migration every time a column is added. The index-only scan argument is real when there is an index-only scan to lose.