tablesample method PostgreSQL's implementation of the TABLESAMPLE clause supports custom sampling methods, in addition to the BERNOULLI and SYSTEM methods that are required by the SQL standard. The sampling method determines which rows of the table will be selected when the TABLESAMPLE clause is used. At the SQL level, a tablesample method is represented by a single SQL function, typically implemented in C, having the signature method_name(internal) RETURNS tsm_handler The name of the function is the same method name appearing in the TABLESAMPLE clause. The internal argument is a dummy (always having value zero) that simply serves to prevent this function from being called directly from a SQL command. The result of the function must be a palloc'd struct of type TsmRoutine, which contains pointers to support functions for the tablesample method. These support functions are plain C functions and are not visible or callable at the SQL level. The support functions are described in . In addition to function pointers, the TsmRoutine struct must include these additional fields: List *parameterTypes This is an OID list containing the data type OIDs of the parameter(s) that will be accepted by the TABLESAMPLE clause when this sampling method is used. For example, for the built-in methods, this list contains a single item with value FLOAT4OID, which represents the sampling percentage. Custom sampling methods can have more or different parameters. bool repeatable_across_queries If true, the sampling method can deliver identical samples across successive queries, if the same REPEATABLE seed value is supplied each time and the table contents have not changed. When this is false, the REPEATABLE clause is not accepted for use with the sampling method. bool repeatable_across_scans If true, the sampling method can deliver identical samples across successive scans in the same query (with an unchanging snapshot). When this is false, the planner will not select plans that would require scanning the sampled table more than once, since that might result in inconsistent query output. The TsmRoutine struct type is declared in src/include/access/tsmapi.h, which see for additional details. The TSM handler function returns a palloc'd TsmRoutine struct containing pointers to the support functions described below. Most of the functions are required, but some are optional, and those pointers can be NULL. void SampleScanCost (PlannerInfo *root, RelOptInfo *baserel, List *paramexprs, BlockNumber *pages, double *tuples); This function is called during planning. It must estimate the number of relation pages that will be read during a sample scan, and the number of tuples that will be selected by the scan. (For example, these might be determined by estimating the sampling fraction, and then multiplying the baserel->pages and baserel->tuples numbers by that, being sure to round the results to integral values.) The paramexprs list holds the expression(s) that are parameters to the TABLESAMPLE clause. It is recommended to use estimate_expression_value() to try to reduce these expressions to constants, if their values are needed for estimation purposes; but the function must provide cost estimates even if they cannot be reduced, and it should not fail even if the values appear invalid (remember that they're only estimates of what the run-time values will be). The pages and tuples parameters are outputs. void BeginSampleScan (SampleScanState *node, int eflags, uint32 seed, Datum *params, bool *paramisnull, int nparams); Initialize for execution of a SampleScan plan node. This is called during executor startup. It should perform any initialization needed before the scan can start. The SampleScanState node has already been created, but its tsm_state field is NULL. The BeginSampleScan function can palloc whatever internal state data is needed by the tablesample method, and store a pointer to it in node->tsm_state. Information about the table to scan is accessible through other fields of the SampleScanState node. eflags contains flag bits describing the executor's operating mode for this plan node. seed contains a seed to use for any random numbers generated within the sampling method; it is either a hash derived from the REPEATABLE value if one was given, or the result of random() if not. The params and paramisnull arrays, each of length nparams, contain the values of the parameters supplied in the TABLESAMPLE clause. These will have the number and types specified in the tablesample method's parameterTypes list; but note that the core code does not reject null values. This function can also adjust the fields node->use_bulkread and node->use_pagemode. If node->use_bulkread is true, which it is by default, the scan will use a buffer access strategy that encourages recycling buffers after use. It might be reasonable to set this to false if the scan will visit only a small fraction of the table's pages. If node->use_pagemode is true, which it is by default, the scan will perform visibility checking in a single pass for all tuples on each visited page. It might be reasonable to set this to false if the scan will select only a small fraction of the tuples on each visited page. That will result in fewer tuple visibility checks being performed, though each one will be more expensive because it will require more locking. Note that when (eflags & EXEC_FLAG_EXPLAIN_ONLY) is true, this function should only do the minimum required to make the node state valid for EXPLAIN and EndSampleScan. BlockNumber NextSampleBlock (SampleScanState *node); Returns the block number of the next page to be scanned, or InvalidBlockNumber if no pages remain to be scanned. This function can be omitted (set the pointer to NULL), in which case the core code will perform a sequential scan of the entire relation. Such a scan can use synchronized scanning, so that the tablesample method cannot assume that the relation pages are visited in the same order on each scan. OffsetNumber NextSampleTuple (SampleScanState *node, BlockNumber blockno, OffsetNumber maxoffset); Returns the offset number of the next tuple to be sampled on the specified page, or InvalidOffsetNumber if no tuples remain to be sampled. maxoffset is the largest offset number in use on the page. NextSampleTuple is not explicitly told which of the offset numbers in the range 1 .. maxoffset actually contain valid tuples. This is not normally a problem since the core code ignores requests to sample missing or invisible tuples; that should not result in any bias in the sample. However, if necessary, the function can examine node->ss.ss_currentScanDesc->rs_vistuples[] to identify which tuples are valid and visible. (This requires node->use_pagemode to be true.) NextSampleTuple must not assume that blockno is the same page number returned by the most recent NextSampleBlock call. It was returned by some previous NextSampleBlock call, but the core code is allowed to call NextSampleBlock in advance of actually scanning pages, so as to support prefetching. It is OK to assume that once sampling of a given page begins, successive NextSampleTuple calls all refer to the same page until InvalidOffsetNumber is returned. void ReScanSampleScan (SampleScanState *node); Restart the scan from the beginning. If the tablesample method is marked repeatable_across_scans, it must be able to select the same set of tuples during a rescan. void EndSampleScan (SampleScanState *node); End the scan and release resources. It is normally not important to release palloc'd memory, but any externally-visible resources should be cleaned up. This function can be omitted (set the pointer to NULL) in the common case where no such resources exist.