1
/*-------------------------------------------------------------------------
4
* support routines for SYSTEM_TIME tablesample method
6
* The desire here is to produce a random sample with as many rows as possible
7
* in no more than the specified amount of time. We use a block-sampling
8
* approach. To ensure that the whole relation will be visited if necessary,
9
* we start at a randomly chosen block and then advance with a stride that
10
* is randomly chosen but is relatively prime to the relation's nblocks.
12
* Because of the time dependence, this method is necessarily unrepeatable.
13
* However, we do what we can to reduce surprising behavior by selecting
14
* the sampling pattern just once per query, much as in tsm_system_rows.
16
* Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group
17
* Portions Copyright (c) 1994, Regents of the University of California
20
* contrib/tsm_system_time/tsm_system_time.c
22
*-------------------------------------------------------------------------
28
#include <float.h> /* for _isnan */
32
#include "access/relscan.h"
33
#include "access/tsmapi.h"
34
#include "catalog/pg_type.h"
35
#include "miscadmin.h"
36
#include "optimizer/clauses.h"
37
#include "optimizer/cost.h"
38
#include "utils/sampling.h"
39
#include "utils/spccache.h"
43
PG_FUNCTION_INFO_V1(tsm_system_time_handler);
49
uint32 seed; /* random seed */
50
double millis; /* time limit for sampling */
51
instr_time start_time; /* scan start time */
52
OffsetNumber lt; /* last tuple returned from current block */
53
BlockNumber doneblocks; /* number of already-scanned blocks */
54
BlockNumber lb; /* last block visited */
55
/* these three values are not changed during a rescan: */
56
BlockNumber nblocks; /* number of blocks in relation */
57
BlockNumber firstblock; /* first block to sample from */
58
BlockNumber step; /* step size, or 0 if not set yet */
59
} SystemTimeSamplerData;
61
static void system_time_samplescangetsamplesize(PlannerInfo *root,
66
static void system_time_initsamplescan(SampleScanState *node,
68
static void system_time_beginsamplescan(SampleScanState *node,
72
static BlockNumber system_time_nextsampleblock(SampleScanState *node);
73
static OffsetNumber system_time_nextsampletuple(SampleScanState *node,
75
OffsetNumber maxoffset);
76
static uint32 random_relative_prime(uint32 n, SamplerRandomState randstate);
80
* Create a TsmRoutine descriptor for the SYSTEM_TIME method.
83
tsm_system_time_handler(PG_FUNCTION_ARGS)
85
TsmRoutine *tsm = makeNode(TsmRoutine);
87
tsm->parameterTypes = list_make1_oid(FLOAT8OID);
89
/* See notes at head of file */
90
tsm->repeatable_across_queries = false;
91
tsm->repeatable_across_scans = false;
93
tsm->SampleScanGetSampleSize = system_time_samplescangetsamplesize;
94
tsm->InitSampleScan = system_time_initsamplescan;
95
tsm->BeginSampleScan = system_time_beginsamplescan;
96
tsm->NextSampleBlock = system_time_nextsampleblock;
97
tsm->NextSampleTuple = system_time_nextsampletuple;
98
tsm->EndSampleScan = NULL;
100
PG_RETURN_POINTER(tsm);
104
* Sample size estimation.
107
system_time_samplescangetsamplesize(PlannerInfo *root,
115
double spc_random_page_cost;
119
/* Try to extract an estimate for the limit time spec */
120
limitnode = (Node *) linitial(paramexprs);
121
limitnode = estimate_expression_value(root, limitnode);
123
if (IsA(limitnode, Const) &&
124
!((Const *) limitnode)->constisnull)
126
millis = DatumGetFloat8(((Const *) limitnode)->constvalue);
127
if (millis < 0 || isnan(millis))
129
/* Default millis if the value is bogus */
135
/* Default millis if we didn't obtain a non-null Const */
139
/* Get the planner's idea of cost per page read */
140
get_tablespace_page_costs(baserel->reltablespace,
141
&spc_random_page_cost,
145
* Estimate the number of pages we can read by assuming that the cost
146
* figure is expressed in milliseconds. This is completely, unmistakably
147
* bogus, but we have to do something to produce an estimate and there's
150
if (spc_random_page_cost > 0)
151
npages = millis / spc_random_page_cost;
153
npages = millis; /* even more bogus, but whatcha gonna do? */
155
/* Clamp to sane value */
156
npages = clamp_row_est(Min((double) baserel->pages, npages));
158
if (baserel->tuples > 0 && baserel->pages > 0)
160
/* Estimate number of tuples returned based on tuple density */
161
double density = baserel->tuples / (double) baserel->pages;
163
ntuples = npages * density;
167
/* For lack of data, assume one tuple per page */
171
/* Clamp to the estimated relation size */
172
ntuples = clamp_row_est(Min(baserel->tuples, ntuples));
179
* Initialize during executor setup.
182
system_time_initsamplescan(SampleScanState *node, int eflags)
184
node->tsm_state = palloc0(sizeof(SystemTimeSamplerData));
185
/* Note the above leaves tsm_state->step equal to zero */
189
* Examine parameters and prepare for a sample scan.
192
system_time_beginsamplescan(SampleScanState *node,
197
SystemTimeSamplerData *sampler = (SystemTimeSamplerData *) node->tsm_state;
198
double millis = DatumGetFloat8(params[0]);
200
if (millis < 0 || isnan(millis))
202
(errcode(ERRCODE_INVALID_TABLESAMPLE_ARGUMENT),
203
errmsg("sample collection time must not be negative")));
205
sampler->seed = seed;
206
sampler->millis = millis;
207
sampler->lt = InvalidOffsetNumber;
208
sampler->doneblocks = 0;
209
/* start_time, lb will be initialized during first NextSampleBlock call */
210
/* we intentionally do not change nblocks/firstblock/step here */
214
* Select next block to sample.
216
* Uses linear probing algorithm for picking next block.
219
system_time_nextsampleblock(SampleScanState *node)
221
SystemTimeSamplerData *sampler = (SystemTimeSamplerData *) node->tsm_state;
222
HeapScanDesc scan = node->ss.ss_currentScanDesc;
225
/* First call within scan? */
226
if (sampler->doneblocks == 0)
228
/* First scan within query? */
229
if (sampler->step == 0)
231
/* Initialize now that we have scan descriptor */
232
SamplerRandomState randstate;
234
/* If relation is empty, there's nothing to scan */
235
if (scan->rs_nblocks == 0)
236
return InvalidBlockNumber;
238
/* We only need an RNG during this setup step */
239
sampler_random_init_state(sampler->seed, randstate);
241
/* Compute nblocks/firstblock/step only once per query */
242
sampler->nblocks = scan->rs_nblocks;
244
/* Choose random starting block within the relation */
245
/* (Actually this is the predecessor of the first block visited) */
246
sampler->firstblock = sampler_random_fract(randstate) *
249
/* Find relative prime as step size for linear probing */
250
sampler->step = random_relative_prime(sampler->nblocks, randstate);
253
/* Reinitialize lb and start_time */
254
sampler->lb = sampler->firstblock;
255
INSTR_TIME_SET_CURRENT(sampler->start_time);
258
/* If we've read all blocks in relation, we're done */
259
if (++sampler->doneblocks > sampler->nblocks)
260
return InvalidBlockNumber;
262
/* If we've used up all the allotted time, we're done */
263
INSTR_TIME_SET_CURRENT(cur_time);
264
INSTR_TIME_SUBTRACT(cur_time, sampler->start_time);
265
if (INSTR_TIME_GET_MILLISEC(cur_time) >= sampler->millis)
266
return InvalidBlockNumber;
269
* It's probably impossible for scan->rs_nblocks to decrease between scans
270
* within a query; but just in case, loop until we select a block number
271
* less than scan->rs_nblocks. We don't care if scan->rs_nblocks has
272
* increased since the first scan.
276
/* Advance lb, using uint64 arithmetic to forestall overflow */
277
sampler->lb = ((uint64) sampler->lb + sampler->step) % sampler->nblocks;
278
} while (sampler->lb >= scan->rs_nblocks);
284
* Select next sampled tuple in current block.
286
* In block sampling, we just want to sample all the tuples in each selected
289
* When we reach end of the block, return InvalidOffsetNumber which tells
290
* SampleScan to go to next block.
293
system_time_nextsampletuple(SampleScanState *node,
295
OffsetNumber maxoffset)
297
SystemTimeSamplerData *sampler = (SystemTimeSamplerData *) node->tsm_state;
298
OffsetNumber tupoffset = sampler->lt;
300
/* Advance to next possible offset on page */
301
if (tupoffset == InvalidOffsetNumber)
302
tupoffset = FirstOffsetNumber;
307
if (tupoffset > maxoffset)
308
tupoffset = InvalidOffsetNumber;
310
sampler->lt = tupoffset;
316
* Compute greatest common divisor of two uint32's.
319
gcd(uint32 a, uint32 b)
334
* Pick a random value less than and relatively prime to n, if possible
338
random_relative_prime(uint32 n, SamplerRandomState randstate)
342
/* Safety check to avoid infinite loop or zero result for small n. */
347
* This should only take 2 or 3 iterations as the probability of 2 numbers
348
* being relatively prime is ~61%; but just in case, we'll include a
349
* CHECK_FOR_INTERRUPTS in the loop.
353
CHECK_FOR_INTERRUPTS();
354
r = (uint32) (sampler_random_fract(randstate) * n);
355
} while (r == 0 || gcd(r, n) > 1);