SELECT *

Claim: Performance

In a query’s outermost SELECT, 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 usually rules out an Index Only Scan, a fast path where all needed data comes directly from the index without touching the main table (heap), because that needs every selected column in the index.

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 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 reports this Finding on an unqualified * in the select list of a SELECT statement, outside any subquery or CTE. Postgres returns that select list’s columns to the caller. The Finding’s marker points to the * token in the formatted SQL view.

The Finding is not raised in these cases:

• A qualified star such as SELECT users.*. Scoping the star to one table is deliberate.
• An outer * whose FROM lists only subqueries that name their own columns, as in SELECT * FROM (SELECT x FROM foo) sub. It returns only the columns those subqueries chose. When one of them selects * in its own select list, the outer * is reported. A subquery made of a UNION, INTERSECT or EXCEPT counts as one that lists its columns.
• A * in any subquery or CTE. Postgres usually drops the columns nothing reads from these, so the * costs nothing at run time. Some keep every column all the same, such as a CTE marked MATERIALIZED or one that needs every column, as SELECT DISTINCT * does, and Query Doctor does not report those either.
• A SELECT … INTO or an INSERT … SELECT *. Both write every column they select into a table, so none is returned for nothing.
• A * in an arm of UNION, INTERSECT or EXCEPT. Query Doctor does not check these arms.