Writing A TABLESAMPLE Sampling Method

>From 813e641c9ae202b8bfb4be4b978bb2c22a60eea4 Mon Sep 17 00:00:00 2001 From: Petr Jelinek Date: Sun, 15 Mar 2015 17:39:22 +0100 Subject: [PATCH 4/4] tablesample api doc v1 --- doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/postgres.sgml | 1 + doc/src/sgml/tablesample-method.sgml | 169 +++++++++++++++++++++++++++++++++++ 3 files changed, 171 insertions(+) create mode 100644 doc/src/sgml/tablesample-method.sgml diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 89fff77..23d932d 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -98,6 +98,7 @@ + diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml index e378d69..dc1f020 100644 --- a/doc/src/sgml/postgres.sgml +++ b/doc/src/sgml/postgres.sgml @@ -250,6 +250,7 @@ &gin; &brin; &storage; + &tablesample-method; &bki; &planstats; diff --git a/doc/src/sgml/tablesample-method.sgml b/doc/src/sgml/tablesample-method.sgml new file mode 100644 index 0000000..2d6d323 --- /dev/null +++ b/doc/src/sgml/tablesample-method.sgml @@ -0,0 +1,169 @@ + + + + Writing A TABLESAMPLE Sampling Method + + + tablesample method + + + + The TABLESAMPLE clause implementation in + PostgreSQL supports creating a custom sampling methods. + These methods control what sample of the table will be returned when the + TABLESAMPLE clause is used. + + + + Tablesample Method Functions + + + The tablesample method must provide following set of functions: + + + + +void +tsm_init (SampleScanState *scanstate, + uint32 seed, ...); + + Initialize the tablesample scan. The function is called at the beginning + of each relation scan. + + + Note that the first two parameters are required but you can specify + additional parameters which then will be used by the TABLESAMPLE + clause to determine the required user input in the query itself. + This means that if your function will specify additional float4 parameter + named percent, the user will have to call the tablesample method with + expression which evaluates (or can be coerced) to float4. + For example this definition: + +tsm_init (SampleScanState *scanstate, + uint32 seed, float4 pct); + +Will lead to SQL call like this: + +... TABLESAMPLE yourmethod(0.5) ... + + + + + +BlockNumber +tsm_nextblock (SampleScanState *scanstate); + + Returns the block number of next page to be scanned. InvalidBlockNumber + should be returned if the sampling has reached end of the relation. + + + + +OffsetNumber +tsm_nexttuple (SampleScanState *scanstate, BlockNumber blockno, + OffsetNumber maxoffset); + + Return next tuple offset for the current page. InvalidOffsetNumber should + be returned if the sampling has reached end of the page. + + + + +void +tsm_end (SampleScanState *scanstate); + + The scan has finished, cleanup any left over state. + + + + +void +tsm_reset (SampleScanState *scanstate); + + The scan needs to rescan the relation again, reset any tablesample method + state. + + + + +void +tsm_cost (PlannerInfo *root, Path *path, RelOptInfo *baserel, + List *args, BlockNumber *pages, double *tuples); + + This function is used by optimizer to decide best plan and is also used + for output of EXPLAIN. + + + + There is function that tablesampling method can implement in order to gain + more fine grained control over sampling. This function is optional: + + + + +bool +tsm_examinetuple (SampleScanState *scanstate, BlockNumber blockno, + HeapTuple tuple, bool visible); + + Function that enables the sampling method to examine contents of the tuple + (for example to collect some internal statistics). The return value of this + function is used to determine if the tuple should be returned to client. + Note that this function will receive even invisible tuples but it is not + allowed to return true for such tuple (if it does, + PostgreSQL will raise an error). + + + + As you can see most of the tablesample method interfaces get the + SampleScanState as a first parameter. This structure holds + state of the current scan and also provides storage for the tablesample + method's state. It is defined as following: + +typedef struct SampleScanState +{ + ScanState ss; + FmgrInfo tsminit; + FmgrInfo tsmnextblock; + FmgrInfo tsmnexttuple; + FmgrInfo tsmexaminetuple; + FmgrInfo tsmend; + FmgrInfo tsmreset; + void *tsmdata; +} SampleScanState; + + Where ss is the ScanState itself. From it, you + can get ss_currentRelation (currently scanned relation) and + ss_currentScanDesc (information about the scan). + Those are usually useful for the tsm_init function. + The tsminit, tsmnextblock, + tsmnexttuple, tsmend and + tsmreset are pointers to the tablesample method functions for + use by the sample scan itself and the tablesample method does not need to be + concerned about these values. The tsmdata can be used by + tablesample method to store any state info it might need during the scan. + + + + + Tablesample Method Installation + + + Once you have written and built the custom tablesample method, you can + install it using the SQL statement + and removed again using + . + + + + + + Tablesample Method Example + + + Example of how to implement custom tablesample method can be found in the + PostgreSQL's sources under + src/test/modules/tablesample directory. + + + + -- 1.9.1