Count Customers in One Country — Reading EXPLAIN Output in SQL

The problem

Scenario: Brightlane's data analyst was investigating a slow customer lookup and saw EXPLAIN reporting only 2 rows for the restriction on country = 'US' — well below what the analyst expected, given the size of the US customer base.

Task: Write a query to return the actual count of customers whose country is 'US', so the analyst can confirm the gap between the planner's estimate and reality.

Assumptions:

The customers table holds one row per customer, with the customer's country stored in country.
A US-based customer has country equal to 'US'.

Output:

One row, holding the US customer count.
Columns in this order: us_customer_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 us_customer_count
FROM
  customers
WHERE
  country = 'US'

The shape

The planner thought the country = 'US' restriction matched 2 rows; the table actually carries 16. COUNT(*) with the same predicate is how you measure that gap directly, without rerunning the plan.

Clause by clause

SELECT COUNT(*) AS us_customer_count returns the single number the analyst needs and labels it as the US-customer total, so the result reads as a measured quantity rather than a raw aggregate.
FROM customers reads the customer records — the same table the slow lookup was scanning.
WHERE country = 'US' applies the exact predicate the planner was estimating selectivity for. The count of rows that survive this filter is the actual selectivity, against which the planner's 2-row estimate can be judged.

Why this matters for the plan

The planner picks its scan strategy from row estimates. When it expects 2 rows out of a large table, it leans toward an index scan (chase a few tuples through the index, done). When the reality is 16, the same plan still runs but no longer fits the data — and on a real-world table where the gap is 2-versus-thousands, the same misestimate is what drives queries from milliseconds to seconds. The single number this query returns is the input to that judgment.

You practiced cross-checking a planner row estimate against the real count — the kind of statistics gap that drives bad plan choices.

Return the actual count of `customers` whose `country` is `'US'`, so the analyst can confirm the gap between the planner's estimate and reality

The shape

Clause by clause

Why this matters for the plan

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