TABLESAMPLE for random sampling in PostgreSQL - Step-by-Step Execution

Concept Flow - TABLESAMPLE for random sampling

Start Query

↓

Identify Table

↓

Apply TABLESAMPLE Method

↓

Randomly Select Rows Based on Sample Size

↓

Return Sampled Rows

↓

End Query

The query starts by identifying the table, then applies the TABLESAMPLE method to randomly select a portion of rows, and finally returns those sampled rows.

Execution Sample

PostgreSQL

SELECT * FROM employees TABLESAMPLE SYSTEM (10);

This query selects approximately 10% of rows randomly from the employees table using the SYSTEM sampling method.

Execution Table

Step	Action	Evaluation	Result
1	Start Query	Parse SQL statement	Ready to execute
2	Identify Table	Table: employees	Table located
3	Apply TABLESAMPLE	Method: SYSTEM, Percentage: 10%	Sampling method set
4	Randomly Select Rows	Randomly pick ~10% rows	Subset of rows chosen
5	Return Sampled Rows	Output sampled rows	Rows returned to user
6	End Query	Query execution complete	Finished

💡 Query ends after returning the sampled rows from the employees table.

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
Table	None	employees	employees	employees
Sampling Method	None	SYSTEM (10%)	SYSTEM (10%)	SYSTEM (10%)
Sampled Rows	None	None	Subset (~10%)	Subset (~10%)

Key Moments - 3 Insights

Why does the number of rows returned by TABLESAMPLE vary each time?

Because TABLESAMPLE uses random selection (see execution_table step 4), the exact rows and count can differ on each execution.

What does SYSTEM (10) mean in TABLESAMPLE?

It means approximately 10% of the table's rows are randomly selected using the SYSTEM method (execution_table step 3).

Does TABLESAMPLE guarantee exactly 10% rows?

No, it returns an approximate percentage, not an exact count, due to the random nature of sampling (execution_table step 4).

Visual Quiz - 3 Questions

Look at the execution_table, at which step are the rows randomly selected?

A Step 2

B Step 5

C Step 4

D Step 3

💡 Hint

Check the 'Action' column in execution_table for the step describing random row selection.

According to variable_tracker, what is the value of Sampling Method after Step 3?

A None

B SYSTEM (10%)

C Subset (~10%)

D employees

💡 Hint

Look at the Sampling Method row under 'After Step 3' in variable_tracker.

If you change SYSTEM (10) to SYSTEM (50), what happens to Sampled Rows in variable_tracker?

A Sampled Rows become a larger subset (~50%)

B Sampled Rows become a smaller subset

C Sampled Rows remain None

D Sampling Method changes to SYSTEM (10%)

💡 Hint

Consider how increasing the percentage affects the size of the sampled subset in variable_tracker.

Concept Snapshot

TABLESAMPLE lets you pick a random sample of rows from a table.
Syntax: SELECT * FROM table TABLESAMPLE method (percentage);
SYSTEM method picks approx percentage of rows randomly.
Result varies each time due to randomness.
Useful for quick data checks or testing.

Full Transcript

This visual execution trace shows how the TABLESAMPLE clause works in PostgreSQL. The query starts by parsing and locating the table. Then it applies the TABLESAMPLE SYSTEM method with a specified percentage, for example 10%. The database engine randomly selects approximately that percentage of rows from the table. These sampled rows are then returned as the query result. Because the selection is random, the exact rows and count can vary each time the query runs. The variable tracker shows the table name, sampling method, and the subset of sampled rows changing through the steps. Key moments clarify that TABLESAMPLE returns an approximate sample, not an exact count, and explain the meaning of SYSTEM (percentage). The quiz questions help reinforce understanding of the steps and variable changes during execution.