23.2. Sampling Table Rows (TABLESAMPLE) #
The TABLESAMPLE stored procedure returns a subset of rows from an analytical table. This is useful for exploratory data analysis, data profiling, and performance testing on large datasets.
Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.
Execute the following command on the Postgres Pro AXE server:
SELECT * FROMtableTABLESAMPLEsampling_method(percentage) ON CONFLICT DO NOTHING;
Where:
table: The analytical table from which rows must be sampled.sampling_method: The sampling method.Possible values:
SYSTEM: Random sampling at the storage level.This method is faster but returns the requested percentage approximately.
BERNOULLI: Row-by-row random sampling.This method is slower but returns the requested percentage exactly.
percentage: The percentage of rows from 0 to 100.
Postgres Pro AXE returns the sampled rows.
Example 23.2. Executing the TABLESAMPLE Stored Procedure
Sampling 10% of rows from the orders analytical table using the SYSTEM method:
SELECT * FROM orders TABLESAMPLE SYSTEM(10) ON CONFLICT DO NOTHING;
Sampling 5% of rows using the BERNOULLI method:
SELECT * FROM orders TABLESAMPLE BERNOULLI(5) ON CONFLICT DO NOTHING;
Profiling data with a 2% sample:
SELECT
status,
COUNT(*) AS sample_count,
AVG(amount) AS avg_amount
FROM orders TABLESAMPLE SYSTEM(2)
GROUP BY status;
Sampling rows in a join with the customers table:
SELECT c.name, COUNT(o.id) AS order_count FROM customers c JOIN orders o TABLESAMPLE SYSTEM(10) ON c.id = o.customer_id GROUP BY c.name;