What's the difference between SYSTEM and BERNOULLI sampling?

SYSTEM samples at the block level — fast, but rows clustered in a block are sampled together. BERNOULLI considers each row independently — more statistically uniform, but it still scans the whole table.

Sample Scan

Appears in EXPLAIN asSample Scan

Reads a random sample of a table for TABLESAMPLE queries.

What it is

A Sample Scan implements the SQL TABLESAMPLE clause, returning a pseudo-random subset of a table's rows using a sampling method such as SYSTEM (block-level) or BERNOULLI (row-level).

When the planner picks it

Only when the query explicitly uses TABLESAMPLE. It is never chosen automatically.

Is it good or bad?

Expected whenever you asked for a sample. SYSTEM sampling is fast but coarse (whole blocks); BERNOULLI is more uniform but reads the whole table.

In depth

TABLESAMPLE in action

A Sample Scan implements the SQL TABLESAMPLE clause: it returns a pseudo-random subset of a table's rows. The clause names a sampling method and a percentage:

SELECT * FROM big TABLESAMPLE SYSTEM    (10);     -- ~10% of pages
SELECT * FROM big TABLESAMPLE BERNOULLI (10);     -- ~10% of rows

The two built-in methods differ in what they sample:

SYSTEM picks whole blocks at random with the given probability and returns every row in those blocks. Very fast — it touches few pages — but rows clustered on the same page are sampled together (the sample is biased toward block-locality).
BERNOULLI considers each row independently. More statistically uniform, but it still has to scan the whole table to evaluate the per-row coin flip.

A REPEATABLE (seed) clause makes the sample deterministic for that seed — useful for reproducible analytics.

When to use which

Quick "what does the data look like" — SYSTEM at a few percent. Fast. Beware the clustering bias if rows aren't randomly distributed on disk.
Statistically representative samples — BERNOULLI. The cost is effectively a full scan, so for very large tables consider sampling at the application level instead, or sampling from an index.

TABLESAMPLE is only chosen when you ask for it — never automatically — so seeing a Sample Scan in a plan just means the SQL contained the clause.

FAQ

What's the difference between SYSTEM and BERNOULLI sampling?: SYSTEM samples at the block level — fast, but rows clustered in a block are sampled together. BERNOULLI considers each row independently — more statistically uniform, but it still scans the whole table.