Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4d40494

Browse files
contrib/tsm_system_rows
1 parent149f6f1 commit4d40494

File tree

7 files changed

+390
-0
lines changed

7 files changed

+390
-0
lines changed

‎contrib/tsm_system_rows/.gitignore

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
# Generated subdirectories
2+
/log/
3+
/results/
4+
/tmp_check/

‎contrib/tsm_system_rows/Makefile

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
# src/test/modules/tsm_system_rows/Makefile
2+
3+
MODULE_big = tsm_system_rows
4+
OBJS = tsm_system_rows.o$(WIN32RES)
5+
PGFILEDESC = "tsm_system_rows - SYSTEM TABLESAMPLE method which accepts number of rows as a limit"
6+
7+
EXTENSION = tsm_system_rows
8+
DATA = tsm_system_rows--1.0.sql
9+
10+
REGRESS = tsm_system_rows
11+
12+
ifdefUSE_PGXS
13+
PG_CONFIG = pg_config
14+
PGXS :=$(shell$(PG_CONFIG) --pgxs)
15+
include$(PGXS)
16+
else
17+
subdir = contrib/tsm_system_rows
18+
top_builddir = ../..
19+
include$(top_builddir)/src/Makefile.global
20+
include$(top_srcdir)/contrib/contrib-global.mk
21+
endif
Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
CREATE EXTENSION tsm_system_rows;
2+
CREATE TABLE test_tablesample (id int, name text) WITH (fillfactor=10); -- force smaller pages so we don't have to load too much data to get multiple pages
3+
INSERT INTO test_tablesample SELECT i, repeat(i::text, 1000) FROM generate_series(0, 30) s(i) ORDER BY i;
4+
ANALYZE test_tablesample;
5+
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (1000);
6+
count
7+
-------
8+
31
9+
(1 row)
10+
11+
SELECT id FROM test_tablesample TABLESAMPLE system_rows (8) REPEATABLE (5432);
12+
id
13+
----
14+
7
15+
14
16+
21
17+
28
18+
4
19+
11
20+
18
21+
25
22+
(8 rows)
23+
24+
EXPLAIN SELECT id FROM test_tablesample TABLESAMPLE system_rows (20) REPEATABLE (10);
25+
QUERY PLAN
26+
-----------------------------------------------------------------------------------
27+
Sample Scan (system_rows) on test_tablesample (cost=0.00..80.20 rows=20 width=4)
28+
(1 row)
29+
30+
-- done
31+
DROP TABLE test_tablesample CASCADE;
Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
CREATE EXTENSION tsm_system_rows;
2+
3+
CREATETABLEtest_tablesample (idint, nametext) WITH (fillfactor=10);-- force smaller pages so we don't have to load too much data to get multiple pages
4+
5+
INSERT INTO test_tablesampleSELECT i, repeat(i::text,1000)FROM generate_series(0,30) s(i)ORDER BY i;
6+
ANALYZE test_tablesample;
7+
8+
SELECTcount(*)FROM test_tablesample TABLESAMPLE system_rows (1000);
9+
SELECT idFROM test_tablesample TABLESAMPLE system_rows (8) REPEATABLE (5432);
10+
11+
EXPLAINSELECT idFROM test_tablesample TABLESAMPLE system_rows (20) REPEATABLE (10);
12+
13+
-- done
14+
DROPTABLE test_tablesample CASCADE;
Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
/* src/test/modules/tablesample/tsm_system_rows--1.0.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use"CREATE EXTENSION tsm_system_rows" to load this file. \quit
5+
6+
CREATEFUNCTIONtsm_system_rows_init(internal, int4, int4)
7+
RETURNS void
8+
AS'MODULE_PATHNAME'
9+
LANGUAGE C STRICT;
10+
11+
CREATEFUNCTIONtsm_system_rows_nextblock(internal)
12+
RETURNS int4
13+
AS'MODULE_PATHNAME'
14+
LANGUAGE C STRICT;
15+
16+
CREATEFUNCTIONtsm_system_rows_nexttuple(internal, int4, int2)
17+
RETURNS int2
18+
AS'MODULE_PATHNAME'
19+
LANGUAGE C STRICT;
20+
21+
CREATEFUNCTIONtsm_system_rows_examinetuple(internal, int4, internal, bool)
22+
RETURNS bool
23+
AS'MODULE_PATHNAME'
24+
LANGUAGE C STRICT;
25+
26+
CREATEFUNCTIONtsm_system_rows_end(internal)
27+
RETURNS void
28+
AS'MODULE_PATHNAME'
29+
LANGUAGE C STRICT;
30+
31+
CREATEFUNCTIONtsm_system_rows_reset(internal)
32+
RETURNS void
33+
AS'MODULE_PATHNAME'
34+
LANGUAGE C STRICT;
35+
36+
CREATEFUNCTIONtsm_system_rows_cost(internal, internal, internal, internal, internal, internal, internal)
37+
RETURNS void
38+
AS'MODULE_PATHNAME'
39+
LANGUAGE C STRICT;
40+
41+
INSERT INTO pg_tablesample_methodVALUES('system_rows', false, true,
42+
'tsm_system_rows_init','tsm_system_rows_nextblock',
43+
'tsm_system_rows_nexttuple','tsm_system_rows_examinetuple',
44+
'tsm_system_rows_end','tsm_system_rows_reset','tsm_system_rows_cost');
45+
Lines changed: 270 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,270 @@
1+
/*-------------------------------------------------------------------------
2+
*
3+
* tsm_system_rows.c
4+
* interface routines for system_rows tablesample method
5+
*
6+
*
7+
* Portions Copyright (c) 1996-2014, PostgreSQL Global Development Group
8+
*
9+
* IDENTIFICATION
10+
* contrib/tsm_system_rows_rowlimit/tsm_system_rows.c
11+
*
12+
*-------------------------------------------------------------------------
13+
*/
14+
15+
#include"postgres.h"
16+
17+
#include"fmgr.h"
18+
19+
#include"access/tablesample.h"
20+
#include"access/relscan.h"
21+
#include"miscadmin.h"
22+
#include"nodes/execnodes.h"
23+
#include"nodes/relation.h"
24+
#include"optimizer/clauses.h"
25+
#include"storage/bufmgr.h"
26+
#include"utils/sampling.h"
27+
28+
PG_MODULE_MAGIC;
29+
30+
/*
31+
* State
32+
*/
33+
typedefstruct
34+
{
35+
SamplerRandomStaterandstate;
36+
uint32seed;/* random seed */
37+
BlockNumbernblocks;/* number of block in relation */
38+
int32ntuples;/* number of tuples to return */
39+
int32donetuples;/* tuples already returned */
40+
OffsetNumberlt;/* last tuple returned from current block */
41+
BlockNumberstep;/* step size */
42+
BlockNumberlb;/* last block visited */
43+
BlockNumberdoneblocks;/* number of already returned blocks */
44+
}SystemSamplerData;
45+
46+
47+
PG_FUNCTION_INFO_V1(tsm_system_rows_init);
48+
PG_FUNCTION_INFO_V1(tsm_system_rows_nextblock);
49+
PG_FUNCTION_INFO_V1(tsm_system_rows_nexttuple);
50+
PG_FUNCTION_INFO_V1(tsm_system_rows_examinetuple);
51+
PG_FUNCTION_INFO_V1(tsm_system_rows_end);
52+
PG_FUNCTION_INFO_V1(tsm_system_rows_reset);
53+
PG_FUNCTION_INFO_V1(tsm_system_rows_cost);
54+
55+
staticuint32random_relative_prime(uint32n,SamplerRandomStaterandstate);
56+
57+
/*
58+
* Initializes the state.
59+
*/
60+
Datum
61+
tsm_system_rows_init(PG_FUNCTION_ARGS)
62+
{
63+
TableSampleDesc*tsdesc= (TableSampleDesc*)PG_GETARG_POINTER(0);
64+
uint32seed=PG_GETARG_UINT32(1);
65+
int32ntuples=PG_ARGISNULL(2) ?-1 :PG_GETARG_INT32(2);
66+
HeapScanDescscan=tsdesc->heapScan;
67+
SystemSamplerData*sampler;
68+
69+
if (ntuples<1)
70+
ereport(ERROR,
71+
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
72+
errmsg("invalid sample size"),
73+
errhint("Sample size must be positive integer value.")));
74+
75+
sampler=palloc0(sizeof(SystemSamplerData));
76+
77+
/* Remember initial values for reinit */
78+
sampler->seed=seed;
79+
sampler->nblocks=scan->rs_nblocks;
80+
sampler->ntuples=ntuples;
81+
sampler->donetuples=0;
82+
sampler->lt=InvalidOffsetNumber;
83+
sampler->doneblocks=0;
84+
85+
sampler_random_init_state(sampler->seed,sampler->randstate);
86+
87+
/* Find relative prime as step size for linear probing. */
88+
sampler->step=random_relative_prime(sampler->nblocks,sampler->randstate);
89+
/*
90+
* Randomize start position so that blocks close to step size don't have
91+
* higher probability of being chosen on very short scan.
92+
*/
93+
sampler->lb=sampler_random_fract(sampler->randstate)*
94+
(sampler->nblocks /sampler->step);
95+
96+
tsdesc->tsmdata= (void*)sampler;
97+
98+
PG_RETURN_VOID();
99+
}
100+
101+
/*
102+
* Get next block number or InvalidBlockNumber when we're done.
103+
*
104+
* Uses linear probing algorithm for picking next block.
105+
*/
106+
Datum
107+
tsm_system_rows_nextblock(PG_FUNCTION_ARGS)
108+
{
109+
TableSampleDesc*tsdesc= (TableSampleDesc*)PG_GETARG_POINTER(0);
110+
SystemSamplerData*sampler= (SystemSamplerData*)tsdesc->tsmdata;
111+
112+
sampler->lb= (sampler->lb+sampler->step) %sampler->nblocks;
113+
sampler->doneblocks++;
114+
115+
/* All blocks have been read, we're done */
116+
if (sampler->doneblocks>sampler->nblocks||
117+
sampler->donetuples >=sampler->ntuples)
118+
PG_RETURN_UINT32(InvalidBlockNumber);
119+
120+
PG_RETURN_UINT32(sampler->lb);
121+
}
122+
123+
/*
124+
* Get next tuple offset in current block or InvalidOffsetNumber if we are done
125+
* with this block.
126+
*/
127+
Datum
128+
tsm_system_rows_nexttuple(PG_FUNCTION_ARGS)
129+
{
130+
TableSampleDesc*tsdesc= (TableSampleDesc*)PG_GETARG_POINTER(0);
131+
OffsetNumbermaxoffset=PG_GETARG_UINT16(2);
132+
SystemSamplerData*sampler= (SystemSamplerData*)tsdesc->tsmdata;
133+
OffsetNumbertupoffset=sampler->lt;
134+
135+
if (tupoffset==InvalidOffsetNumber)
136+
tupoffset=FirstOffsetNumber;
137+
else
138+
tupoffset++;
139+
140+
if (tupoffset>maxoffset||
141+
sampler->donetuples >=sampler->ntuples)
142+
tupoffset=InvalidOffsetNumber;
143+
144+
sampler->lt=tupoffset;
145+
146+
PG_RETURN_UINT16(tupoffset);
147+
}
148+
149+
/*
150+
* Examine tuple and decide if it should be returned.
151+
*/
152+
Datum
153+
tsm_system_rows_examinetuple(PG_FUNCTION_ARGS)
154+
{
155+
TableSampleDesc*tsdesc= (TableSampleDesc*)PG_GETARG_POINTER(0);
156+
boolvisible=PG_GETARG_BOOL(3);
157+
SystemSamplerData*sampler= (SystemSamplerData*)tsdesc->tsmdata;
158+
159+
if (!visible)
160+
PG_RETURN_BOOL(false);
161+
162+
sampler->donetuples++;
163+
164+
PG_RETURN_BOOL(true);
165+
}
166+
167+
/*
168+
* Cleanup method.
169+
*/
170+
Datum
171+
tsm_system_rows_end(PG_FUNCTION_ARGS)
172+
{
173+
TableSampleDesc*tsdesc= (TableSampleDesc*)PG_GETARG_POINTER(0);
174+
175+
pfree(tsdesc->tsmdata);
176+
177+
PG_RETURN_VOID();
178+
}
179+
180+
/*
181+
* Reset state (called by ReScan).
182+
*/
183+
Datum
184+
tsm_system_rows_reset(PG_FUNCTION_ARGS)
185+
{
186+
TableSampleDesc*tsdesc= (TableSampleDesc*)PG_GETARG_POINTER(0);
187+
SystemSamplerData*sampler= (SystemSamplerData*)tsdesc->tsmdata;
188+
189+
sampler->lt=InvalidOffsetNumber;
190+
sampler->donetuples=0;
191+
sampler->doneblocks=0;
192+
193+
sampler_random_init_state(sampler->seed,sampler->randstate);
194+
sampler->step=random_relative_prime(sampler->nblocks,sampler->randstate);
195+
sampler->lb=sampler_random_fract(sampler->randstate)* (sampler->nblocks /sampler->step);
196+
197+
PG_RETURN_VOID();
198+
}
199+
200+
/*
201+
* Costing function.
202+
*/
203+
Datum
204+
tsm_system_rows_cost(PG_FUNCTION_ARGS)
205+
{
206+
PlannerInfo*root= (PlannerInfo*)PG_GETARG_POINTER(0);
207+
Path*path= (Path*)PG_GETARG_POINTER(1);
208+
RelOptInfo*baserel= (RelOptInfo*)PG_GETARG_POINTER(2);
209+
List*args= (List*)PG_GETARG_POINTER(3);
210+
BlockNumber*pages= (BlockNumber*)PG_GETARG_POINTER(4);
211+
double*tuples= (double*)PG_GETARG_POINTER(5);
212+
Node*limitnode;
213+
int32ntuples;
214+
215+
limitnode=linitial(args);
216+
limitnode=estimate_expression_value(root,limitnode);
217+
218+
if (IsA(limitnode,RelabelType))
219+
limitnode= (Node*) ((RelabelType*)limitnode)->arg;
220+
221+
if (IsA(limitnode,Const))
222+
ntuples=DatumGetInt32(((Const*)limitnode)->constvalue);
223+
else
224+
{
225+
/* Default ntuples if the estimation didn't return Const. */
226+
ntuples=1000;
227+
}
228+
229+
*pages=Min(baserel->pages,ntuples);
230+
*tuples=ntuples;
231+
path->rows=*tuples;
232+
233+
PG_RETURN_VOID();
234+
}
235+
236+
237+
staticuint32
238+
gcd (uint32a,uint32b)
239+
{
240+
uint32c;
241+
242+
while (a!=0)
243+
{
244+
c=a;
245+
a=b %a;
246+
b=c;
247+
}
248+
249+
returnb;
250+
}
251+
252+
staticuint32
253+
random_relative_prime(uint32n,SamplerRandomStaterandstate)
254+
{
255+
/* Pick random starting number, with some limits on what it can be. */
256+
uint32r= (uint32)sampler_random_fract(randstate)*n/2+n/4,
257+
t;
258+
259+
/*
260+
* This should only take 2 or 3 iterations as the probability of 2 numbers
261+
* being relatively prime is ~61%.
262+
*/
263+
while ((t=gcd(r,n))>1)
264+
{
265+
CHECK_FOR_INTERRUPTS();
266+
r /=t;
267+
}
268+
269+
returnr;
270+
}
Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
# tsm_system_rows extension
2+
comment = 'SYSTEM TABLESAMPLE method which accepts number rows as a limit'
3+
default_version = '1.0'
4+
module_pathname = '$libdir/tsm_system_rows'
5+
relocatable = true

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp