tsm_system_rows

The tsm_system_rows module provides the table sampling method SYSTEM_ROWS, which can be used in the TABLESAMPLE clause of a SELECT command.

This table sampling method accepts an integer parameter, which is the maximum number of rows to read. The resulting sample will always contain exactly this many rows, unless the table does not have enough rows, in which case the entire table is selected.

Like the built-in SYSTEM sampling method, SYSTEM_ROWS performs block-level sampling, so the sampling is not completely random but is subject to clustering effects, especially when only a small number of rows are requested.

SYSTEM_ROWS does not support the REPEATABLE clause.

This module is considered "trusted", that is, it can be installed by non-superusers who have CREATE privilege on the current database.

C.41.1. Examples

Here is an example of using SYSTEM_ROWS to select a table sample. First install the extension:

CREATE EXTENSION tsm_system_rows;

Then you can use it in a SELECT command, for example:

SELECT * FROM my_table TABLESAMPLE SYSTEM_ROWS(100);

test=## SELECT * FROM my_table TABLESAMPLE SYSTEM_ROWS(100);

id | xml

----+-----

(0 rows)

This command returns a 100-row sample from the table my_table (unless the table has fewer than 100 visible rows, in which case all rows are returned).