Count Shipped Orders — Reading EXPLAIN Output in SQL

The problem

Scenario: Brightlane's data analyst ran EXPLAIN on a fulfillment health report and saw the planner estimating only 5 rows for orders whose status is 'shipped' — a number the analyst suspects is wildly off because table statistics have not been refreshed since a recent data import.

Task: Write a query to return the actual count of orders whose status is 'shipped', so the analyst can compare the real number against the planner's estimate.

Assumptions:

The orders table holds one row per placed order, with the order's outcome stored in status.
A shipped order has status equal to 'shipped'.

Output:

One row, holding the shipped-order count.
Columns in this order: shipped_order_count.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  COUNT(*) AS shipped_order_count
FROM
  orders
WHERE
  status = 'shipped'

The shape

The planner said 5; the table actually has 17 shipped orders, and COUNT(*) with the same restriction is the most direct way to measure that gap. The query is a one-line truth check against the row-estimate the analyst saw in the plan.

Clause by clause

SELECT COUNT(*) AS shipped_order_count returns one row with the total count, labeled so the result reads as the diagnostic number rather than an unnamed column.
FROM orders reads the order records — the same table the EXPLAIN plan was built against.
WHERE status = 'shipped' applies the same restriction the planner was estimating selectivity for. The actual surviving row count is what the planner was trying to predict.

Why this and not a per-status breakdown

A per-status GROUP BY would also surface the shipped count, alongside every other status. That's a useful query for a different diagnostic — checking distribution skew — but it overshoots this question. The analyst is comparing one specific estimate (5) against one specific reality, so a single-row answer is the cleanest comparison surface. A grouped result would require the analyst to scan the output for the right row before doing the same subtraction.

You practiced verifying a planner row estimate by computing the real count — the gap between estimated and actual rows is the headline diagnostic in EXPLAIN ANALYZE output.

Return the actual count of `orders` whose `status` is `'shipped'`, so the analyst can compare the real number against the planner's estimate

The shape

Clause by clause

Why this and not a per-status breakdown

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.