tsm_system_time

The tsm_system_time module implements the SYSTEM_TIME table sampling method. This method is used in the TABLESAMPLE clause of a SELECT command.

The tsm_system_time module is a Greenplum Database extension.

Installing and Registering the Module

The tsm_system_time module is installed when you install Greenplum Database. Before you can use any of the functions defined in the module, you must register the tsm_system_time extension in each database where you want to use the functions. Refer to Installing Additional Supplied Modules for more information.

Using the tsm_system_time Module

The SYSTEM_TIME table sampling method takes a single floating-point argument that specifies the maximum number of milliseconds to spend reading the table. This allows you to control how long the query takes; the tradeoff is that the size of the sample becomes hard to predict. The resulting sample will contain as many rows as could be read in the specified time, unless the whole table has been read first.

Like the built-in SYSTEM sampling method, SYSTEM_TIME performs block-level sampling, so that, rather than being completely random, the sample may be subject to clustering effects, particularly when a small number of rows are selected.

Example

The following command return as large a sample of tableA as it can read in 1/2 second (500 milliseconds). If the entire table can be read in under 1 second, all of its rows will be returned.

SELECT * FROM tableA TABLESAMPLE SYSTEM_TIME(500);