skip to content

In a columnar table, why does selecting 3 columns of 200 read far less data than SELECT *?

level: juniorimportance: must knowfreq 80%

answer

  1. one column's values live together on disk
  2. metadata records where each chunk starts
  3. the scan asks for byte ranges, not rows
  4. columns you never name are never opened

basics

~20 s

Each column's values sit contiguously in their own chunks, and the file metadata records where every chunk starts. The scan reads only the byte ranges of the columns the query names; the other columns are never opened or decompressed.

solid answer

~50 s

A columnar table is cut into horizontal slices (row groups or stripes); inside each slice, every column's values for those rows form one contiguous **column chunk**, and the metadata records each chunk's offset and length. The scan operator applies **projection pushdown**: it resolves the columns the query actually references, looks up their offsets, and issues reads only for those byte ranges — often as ranged GETs against object storage. The remaining 197 columns are never fetched, decompressed or decoded, so bytes read track the *width of the projection*, not the width of the table. `SELECT *` throws that away: it reads every chunk of every slice and decodes values the query then discards. On an engine billed by bytes scanned that is a direct multiplier on the bill; on a fixed cluster it is wasted I/O, memory and CPU. Note that filter, join and group-by columns are read too, even when they are not in the SELECT list.

code

sql · 7 lines
sql
-- reads every column chunk of every row group it visits
SELECT * FROM events WHERE event_date = DATE '2026-01-01';

-- reads only the three named chunks plus the filter column
SELECT user_id, event_type, amount
FROM events
WHERE event_date = DATE '2026-01-01';

go deeper

for a junior

Be ready to say that values of one column are stored together, so a query reads only the columns it names. Know that SELECT * defeats this and that bytes read scale with the projection.

for a middle

Explain the mechanics: row groups, per-column chunks, the offset metadata that lets the reader jump straight to a chunk, and projection pushdown as the optimizer step that hands the scan its column set.

for a senior

Show you can quantify it — quote the engine's bytes-scanned figure for both query shapes, trace a runaway cost back to a BI connector issuing SELECT *, and reason in compressed bytes per column rather than column counts.

for a principal

Own the governance angle: exposing wide tables through projecting views, treating SELECT * in scheduled jobs as a defect, and understanding that on bytes-scanned pricing the projection discipline is a budget control, not a style rule.

## What "columnar" means physically A columnar table does not store rows end to end. It is first cut into horizontal slices — commonly called row groups, stripes or blocks, typically tens of thousands to a few million rows each. Inside one slice, all the values of a single column are written next to each other as one contiguous byte range: a **column chunk**. A slice of a 200-column table therefore contains 200 chunks, one per column, laid out one after another. Alongside the data, the storage layer keeps metadata that lists, for every slice and every column, at least the chunk's byte offset, its compressed and uncompressed length, and its value count. That offset table is the thing that makes selective reading possible at all: without it the reader would have to walk the bytes sequentially to find where a column begins. ## Projection pushdown When a query is planned, the engine collects the set of columns the query genuinely references — the SELECT list plus anything used in WHERE, JOIN conditions, GROUP BY, ORDER BY, and window definitions — and pushes that set down into the scan operator. This is **projection pushdown**. The scan then consults the metadata, computes the byte ranges for exactly those columns in each surviving slice, and issues reads for those ranges only. On object storage this becomes a set of ranged GET requests; on local storage, a set of seeks. Everything else is not "read and skipped". It is never transferred, never decompressed, never decoded into values. That is the whole economic argument for the layout: an analytical query typically touches a handful of columns out of a very wide table, and the layout makes the cost proportional to what it touches. ## Reason in bytes, not in column counts "3 of 200 columns, so 1.5% of the data" is the right instinct but the wrong arithmetic. Columns are not the same size. A single JSON or free-text column can be larger on disk than a hundred integer columns combined, and encoding widens the gap further: a low-cardinality dictionary-encoded column can shrink to a few bits per row while a high-entropy string column barely compresses. So the useful mental model is: bytes read ≈ the sum of the compressed sizes of the chunks you named. Dropping one wide column from a projection can save more than dropping fifty narrow ones. ## What `SELECT *` actually costs here In a row store, `SELECT *` mostly costs extra network and result-set width — the engine had to fetch the whole row anyway. In a columnar engine it is qualitatively different: it converts a query that touches a thin sliver of the table into one that reads and decodes every chunk of every slice it visits. Consequences in production: - On a warehouse priced by bytes scanned, the invoice scales with the projection. The same logical query can differ by one or two orders of magnitude. - On a fixed-size cluster it burns I/O bandwidth, decompression CPU and memory that other queries needed. - It makes the table hostile to evolution: adding one wide column silently raises the cost of every `SELECT *` consumer, even those that ignore the new column. The usual production source is not hand-written SQL but tooling: a BI connector that pulls all columns and discards them client-side, or an ELT step written as `INSERT INTO ... SELECT * FROM ...`. ## Where the saving does not apply - **Predicate and join columns count.** `SELECT id FROM t WHERE country = 'FR'` reads the `country` chunks as well as `id`. - **`LIMIT` does not narrow the projection.** It caps rows returned; the set of columns opened is unchanged, and with a filter the engine may still scan a lot before it can stop. - **Single-row lookups are not cheap.** Fetching one row by key still means touching one chunk per projected column, so a point lookup pays roughly per column, not per row. Columnar layout rewards narrow reads over many rows, not wide reads of one row. - **Nested data varies.** Whether an engine can read one field of a struct without its siblings depends on how the nested type is shredded into leaf columns. - **Very narrow tables.** With five columns, per-chunk and metadata overhead makes the ratio far less dramatic. ## How to demonstrate it in an interview Run both forms and quote the engine's own reported bytes-read or bytes-scanned figure for each; that number is the direct evidence, and every analytical engine surfaces it. The standard remediation is equally concrete: name columns explicitly, expose wide tables through views that project only the used columns, and treat a `SELECT *` in a scheduled job or dashboard as a defect rather than a style preference.

  • Does a LIMIT 100 on a SELECT * make it cheap again?
    No. LIMIT caps the rows returned, not the columns opened — the scan still resolves every column's chunk in whatever slices it reads. With no filter an engine may stop after the first row group, but with a selective predicate it can read a great deal before it accumulates 100 rows, and it decodes all columns while doing so.
  • If a query projects two columns but filters on a third, what does the scan read?
    All three. Projection pushdown uses the columns the query references anywhere — SELECT list, WHERE, JOIN keys, GROUP BY, ORDER BY — not just the output list. The filter column is read first so the engine knows which rows survive; whether it then reads the projected columns for all rows or only surviving positions depends on whether the engine materializes early or late.
  • Why is a single-row lookup by primary key not a strength of this layout?
    Because cost is paid per column, not per row. Retrieving one row with twenty projected columns means locating and decoding twenty separate chunks, each of which may need a whole compression block decompressed to expose one value. Narrow scans over many rows are the layout's sweet spot; wide reads of one row are the case a row store handles better.

A columnar table is a filing cabinet with one drawer per field rather than one folder per record. Naming three fields opens three drawers; SELECT * means pulling open all two hundred and carrying them to the desk.

saying these in an interview costs you the question

  • Says the whole file is read and then filtered in memory
  • Claims columnar storage makes every query faster, including point lookups
  • Forgets that WHERE and JOIN columns are read even when not projected
  • Thinks adding LIMIT reduces the columns a scan opens
  • Estimates savings by counting columns while ignoring column width

context

resolving links…