tsm_system_time

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

This table sampling method accepts a floating-point parameter, which is the maximum number of milliseconds to spend reading the table. This allows you to directly control how long the query takes, at the cost of making the sample size difficult to predict. The resulting sample will contain as many rows as can be read within the specified time, unless the entire table is read first.

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

SYSTEM_TIME 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.

1. Examples

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

CREATE EXTENSION tsm_system_time;

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

SELECT * FROM my_table TABLESAMPLE SYSTEM_TIME(1000);

test=## CREATE TABLE my_table

(id int PRIMARY KEY,

xml text);

CREATE TABLE

test=## SELECT * FROM my_table TABLESAMPLE SYSTEM_TIME(1000);

id | xml

----+-----

(0 rows)

This command will return a sample from my_table that can be read within 1 second (1000 milliseconds). Of course, if the entire table can be read within 1 second, all rows will be returned.