SELECT *

Severity: Info

SELECT * retrieves every column from a table, even columns the caller doesn’t use. This increases I/O, network transfer, and memory usage. It also prevents PostgreSQL from using an Index Only Scan — a fast path where all needed data comes directly from the index without touching the main table (heap).

Example

-- Fetches all columns, including large text or JSONB fields
SELECT * FROM orders WHERE customer_id = 42;

Why it matters

More data than needed

If the application only uses id, status, and total but the table has 20 columns including large jsonb or text fields, SELECT * reads and transfers all of that unused data.

Prevents Index Only Scans

An Index Only Scan is one of PostgreSQL’s most efficient access methods. It satisfies a query entirely from the index, skipping the heap. But it only works when every column in the SELECT list is present in the index. SELECT * includes all columns, so unless you have a covering index on every column (unlikely), PostgreSQL must fall back to an Index Scan or Seq Scan that reads the heap.

-- With an index on (customer_id, status, total):

-- This CAN use an Index Only Scan
SELECT status, total FROM orders WHERE customer_id = 42;

-- This CANNOT — it needs columns not in the index
SELECT * FROM orders WHERE customer_id = 42;

Fragile application code

SELECT * couples your query to the table schema. Adding a column to the table silently changes the result set, which can break application code that unpacks results positionally.

How to fix it

List only the columns you need:

SELECT id, status, total FROM orders WHERE customer_id = 42;

Detection

Query Doctor flags this nudge when it finds a SELECT statement with * in the target list. The nudge marker points to the * token in the formatted SQL view.