|
| 1 | +<!-- doc/src/sgml/tablesample-method.sgml --> |
| 2 | + |
| 3 | +<chapter id="tablesample-method"> |
| 4 | + <title>Writing A TABLESAMPLE Sampling Method</title> |
| 5 | + |
| 6 | + <indexterm zone="tablesample-method"> |
| 7 | + <primary>tablesample method</primary> |
| 8 | + </indexterm> |
| 9 | + |
| 10 | + <para> |
| 11 | + The <command>TABLESAMPLE</command> clause implementation in |
| 12 | + <productname>PostgreSQL</> supports creating a custom sampling methods. |
| 13 | + These methods control what sample of the table will be returned when the |
| 14 | + <command>TABLESAMPLE</command> clause is used. |
| 15 | + </para> |
| 16 | + |
| 17 | + <sect1 id="tablesample-method-functions"> |
| 18 | + <title>Tablesample Method Functions</title> |
| 19 | + |
| 20 | + <para> |
| 21 | + The tablesample method must provide following set of functions: |
| 22 | + </para> |
| 23 | + |
| 24 | + <para> |
| 25 | +<programlisting> |
| 26 | +void |
| 27 | +tsm_init (TableSampleDesc *desc, |
| 28 | + uint32 seed, ...); |
| 29 | +</programlisting> |
| 30 | + Initialize the tablesample scan. The function is called at the beginning |
| 31 | + of each relation scan. |
| 32 | + </para> |
| 33 | + <para> |
| 34 | + Note that the first two parameters are required but you can specify |
| 35 | + additional parameters which then will be used by the <command>TABLESAMPLE</> |
| 36 | + clause to determine the required user input in the query itself. |
| 37 | + This means that if your function will specify additional float4 parameter |
| 38 | + named percent, the user will have to call the tablesample method with |
| 39 | + expression which evaluates (or can be coerced) to float4. |
| 40 | + For example this definition: |
| 41 | +<programlisting> |
| 42 | +tsm_init (TableSampleDesc *desc, |
| 43 | + uint32 seed, float4 pct); |
| 44 | +</programlisting> |
| 45 | +Will lead to SQL call like this: |
| 46 | +<programlisting> |
| 47 | +... TABLESAMPLE yourmethod(0.5) ... |
| 48 | +</programlisting> |
| 49 | + </para> |
| 50 | + |
| 51 | + <para> |
| 52 | +<programlisting> |
| 53 | +BlockNumber |
| 54 | +tsm_nextblock (TableSampleDesc *desc); |
| 55 | +</programlisting> |
| 56 | + Returns the block number of next page to be scanned. InvalidBlockNumber |
| 57 | + should be returned if the sampling has reached end of the relation. |
| 58 | + </para> |
| 59 | + |
| 60 | + <para> |
| 61 | +<programlisting> |
| 62 | +OffsetNumber |
| 63 | +tsm_nexttuple (TableSampleDesc *desc, BlockNumber blockno, |
| 64 | + OffsetNumber maxoffset); |
| 65 | +</programlisting> |
| 66 | + Return next tuple offset for the current page. InvalidOffsetNumber should |
| 67 | + be returned if the sampling has reached end of the page. |
| 68 | + </para> |
| 69 | + |
| 70 | + <para> |
| 71 | +<programlisting> |
| 72 | +void |
| 73 | +tsm_end (TableSampleDesc *desc); |
| 74 | +</programlisting> |
| 75 | + The scan has finished, cleanup any left over state. |
| 76 | + </para> |
| 77 | + |
| 78 | + <para> |
| 79 | +<programlisting> |
| 80 | +void |
| 81 | +tsm_reset (TableSampleDesc *desc); |
| 82 | +</programlisting> |
| 83 | + The scan needs to rescan the relation again, reset any tablesample method |
| 84 | + state. |
| 85 | + </para> |
| 86 | + |
| 87 | + <para> |
| 88 | +<programlisting> |
| 89 | +void |
| 90 | +tsm_cost (PlannerInfo *root, Path *path, RelOptInfo *baserel, |
| 91 | + List *args, BlockNumber *pages, double *tuples); |
| 92 | +</programlisting> |
| 93 | + This function is used by optimizer to decide best plan and is also used |
| 94 | + for output of <command>EXPLAIN</>. |
| 95 | + </para> |
| 96 | + |
| 97 | + <para> |
| 98 | + There is one more function which tablesampling method can implement in order |
| 99 | + to gain more fine grained control over sampling. This function is optional: |
| 100 | + </para> |
| 101 | + |
| 102 | + <para> |
| 103 | +<programlisting> |
| 104 | +bool |
| 105 | +tsm_examinetuple (TableSampleDesc *desc, BlockNumber blockno, |
| 106 | + HeapTuple tuple, bool visible); |
| 107 | +</programlisting> |
| 108 | + Function that enables the sampling method to examine contents of the tuple |
| 109 | + (for example to collect some internal statistics). The return value of this |
| 110 | + function is used to determine if the tuple should be returned to client. |
| 111 | + Note that this function will receive even invisible tuples but it is not |
| 112 | + allowed to return true for such tuple (if it does, |
| 113 | + <productname>PostgreSQL</> will raise an error). |
| 114 | + </para> |
| 115 | + |
| 116 | + <para> |
| 117 | + As you can see most of the tablesample method interfaces get the |
| 118 | + <structname>TableSampleDesc</> as a first parameter. This structure holds |
| 119 | + state of the current scan and also provides storage for the tablesample |
| 120 | + method's state. It is defined as following: |
| 121 | +<programlisting> |
| 122 | +typedef struct TableSampleDesc { |
| 123 | + HeapScanDesc heapScan; |
| 124 | + TupleDesc tupDesc; |
| 125 | + |
| 126 | + void *tsmdata; |
| 127 | +} TableSampleDesc; |
| 128 | +</programlisting> |
| 129 | + Where <structfield>heapScan</> is the descriptor of the physical table scan. |
| 130 | + It's possible to get table size info from it. The <structfield>tupDesc</> |
| 131 | + represents the tuple descriptor of the tuples returned by the scan and passed |
| 132 | + to the <function>tsm_examinetuple()</> interface. The <structfield>tsmdata</> |
| 133 | + can be used by tablesample method itself to store any state info it might |
| 134 | + need during the scan. If used by the method, it should be <function>pfree</>d |
| 135 | + in <function>tsm_end()</> function. |
| 136 | + </para> |
| 137 | + </sect1> |
| 138 | + |
| 139 | +</chapter> |