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

Commite4cc87b

Browse files
committed
auto projections
1 parent9c0b9ad commite4cc87b

File tree

8 files changed

+1404
-74
lines changed

8 files changed

+1404
-74
lines changed

‎expected/test.out‎

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -301,3 +301,35 @@ select sum(x) over (order by id rows between 9 preceding and current row) from s
301301
955
302302
(100 rows)
303303

304+
create table it(i interval, t varchar(4));
305+
insert into it values ('1 second','sec'), ('1 minute','min'), ('1 hour','hour');
306+
select create_projection('vit','it',array['i','t']);
307+
create_projection
308+
-------------------
309+
310+
(1 row)
311+
312+
select vit_refresh();
313+
vit_refresh
314+
-------------
315+
3
316+
(1 row)
317+
318+
select * from vit;
319+
i | t
320+
--------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------
321+
{1000000,60000000,3600000000,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,} | {sec,min,hour,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,}
322+
(1 row)
323+
324+
select count(*) from vit where t='min'::text;
325+
count
326+
-------
327+
1
328+
(1 row)
329+
330+
select count(*) from vit where i>='1 minute'::interval;
331+
count
332+
-------
333+
2
334+
(1 row)
335+

‎projections.sql‎

Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,74 @@
1+
create extension vops;
2+
3+
createtablelineitem(
4+
l_orderkeyinteger,
5+
l_partkeyinteger,
6+
l_suppkeyinteger,
7+
l_linenumberinteger,
8+
l_quantityreal,
9+
l_extendedpricereal,
10+
l_discountreal,
11+
l_taxreal,
12+
l_returnflag"char",
13+
l_linestatus"char",
14+
l_shipdatedate,
15+
l_commitdatedate,
16+
l_receiptdatedate,
17+
l_shipinstructchar(25),
18+
l_shipmodechar(10),
19+
l_commentchar(44),
20+
l_dummychar(1));
21+
22+
select create_projection('vops_lineitem','lineitem',array['l_shipdate','l_quantity','l_extendedprice','l_discount','l_tax'],array['l_returnflag','l_linestatus']);
23+
24+
\timing
25+
26+
copy lineitemfrom'/mnt/data/lineitem.tbl' delimiter'|' csv;
27+
28+
select vops_lineitem_refresh();
29+
30+
select
31+
l_returnflag,
32+
l_linestatus,
33+
sum(l_quantity)as sum_qty,
34+
sum(l_extendedprice)as sum_base_price,
35+
sum(l_extendedprice*(1-l_discount))as sum_disc_price,
36+
sum(l_extendedprice*(1-l_discount)*(1+l_tax))as sum_charge,
37+
avg(l_quantity)as avg_qty,
38+
avg(l_extendedprice)as avg_price,
39+
avg(l_discount)as avg_disc,
40+
count(*)as count_order
41+
from
42+
lineitem
43+
where
44+
l_shipdate<='1998-12-01'
45+
group by
46+
l_returnflag,
47+
l_linestatus
48+
order by
49+
l_returnflag,
50+
l_linestatus;
51+
52+
setvops.auto_substitute_projections TOon;
53+
54+
select
55+
l_returnflag,
56+
l_linestatus,
57+
sum(l_quantity)as sum_qty,
58+
sum(l_extendedprice)as sum_base_price,
59+
sum(l_extendedprice*(1-l_discount))as sum_disc_price,
60+
sum(l_extendedprice*(1-l_discount)*(1+l_tax))as sum_charge,
61+
avg(l_quantity)as avg_qty,
62+
avg(l_extendedprice)as avg_price,
63+
avg(l_discount)as avg_disc,
64+
count(*)as count_order
65+
from
66+
lineitem
67+
where
68+
l_shipdate<='1998-12-01'
69+
group by
70+
l_returnflag,
71+
l_linestatus
72+
order by
73+
l_returnflag,
74+
l_linestatus;

‎sql/test.sql‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,3 +23,11 @@ select populate(destination:='v2'::regclass, source:='s2'::regclass,sort:='id');
2323
select unnest(t.*)from (select msum(x,10) over (order by first(id))from v2) t;
2424
selectsum(x) over (order by id rows between9 precedingand current row)from s2;
2525

26+
createtableit(i interval, tvarchar(4));
27+
insert into itvalues ('1 second','sec'), ('1 minute','min'), ('1 hour','hour');
28+
select create_projection('vit','it',array['i','t']);
29+
select vit_refresh();
30+
31+
select*from vit;
32+
selectcount(*)from vitwhere t='min'::text;
33+
selectcount(*)from vitwhere i>='1 minute'::interval;

‎vops--1.0.sql‎

Lines changed: 193 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ create type vops_float4;
1313
createtypevops_float8;
1414
createtypevops_timestamp;
1515
createtypevops_interval;
16+
createtypevops_text;
1617

1718

1819
createfunctionvops_bool_input(cstring) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
@@ -35,6 +36,9 @@ create function vops_timestamp_input(cstring) returns vops_timestamp as 'MODULE_
3536
createfunctionvops_timestamp_output(vops_timestamp) returns cstringas'MODULE_PATHNAME','vops_int8_output' language C parallel safe immutable strict;
3637
createfunctionvops_interval_input(cstring) returns vops_intervalas'MODULE_PATHNAME','vops_int8_input' language C parallel safe immutable strict;
3738
createfunctionvops_interval_output(vops_interval) returns cstringas'MODULE_PATHNAME','vops_int8_output' language C parallel safe immutable strict;
39+
createfunctionvops_text_input(cstring,oid,integer) returns vops_textas'MODULE_PATHNAME' language C parallel safe immutable strict;
40+
createfunctionvops_text_output(vops_text) returns cstringas'MODULE_PATHNAME' language C parallel safe immutable strict;
41+
createfunctionvops_text_typmod_in(cstring[]) returnsintegeras'MODULE_PATHNAME' language C parallel safe immutable strict;
3842

3943
createtypevops_bool (
4044
input= vops_bool_input,
@@ -110,6 +114,72 @@ create type vops_interval (
110114
internallength=528-- 16 + 64*8
111115
);
112116

117+
createtypevops_text (
118+
input= vops_text_input,
119+
output= vops_text_output,
120+
typmod_in= vops_text_typmod_in,
121+
alignment= double
122+
);
123+
124+
-- text tile
125+
126+
createfunctionvops_text_const(opdtext, widthinteger) returns vops_textas'MODULE_PATHNAME' language C parallel safe immutable strict;
127+
128+
createfunctionvops_text_concat(left vops_text, right vops_text) returns vops_int2as'MODULE_PATHNAME' language C parallel safe immutable strict;
129+
create operator|| (leftarg=vops_text, rightarg=vops_text, procedure=vops_text_concat);
130+
131+
createfunctionvops_text_eq(left vops_text, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
132+
createfunctionvops_text_eq_rconst(left vops_text, righttext) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
133+
createfunctionvops_text_eq_lconst(lefttext, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
134+
create operator= (leftarg=vops_text, rightarg=vops_text, procedure=vops_text_eq, commutator==);
135+
create operator= (leftarg=vops_text, rightarg=text, procedure=vops_text_eq_rconst, commutator==);
136+
create operator= (leftarg=text, rightarg=vops_text, procedure=vops_text_eq_lconst, commutator==);
137+
138+
createfunctionvops_text_ne(left vops_text, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
139+
createfunctionvops_text_ne_rconst(left vops_text, righttext) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
140+
createfunctionvops_text_ne_lconst(lefttext, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
141+
create operator<> (leftarg=vops_text, rightarg=vops_text, procedure=vops_text_ne, commutator=<>);
142+
create operator<> (leftarg=vops_text, rightarg=text, procedure=vops_text_ne_rconst, commutator=<>);
143+
create operator<> (leftarg=text, rightarg=vops_text, procedure=vops_text_ne_lconst, commutator=<>);
144+
145+
createfunctionvops_text_gt(left vops_text, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
146+
createfunctionvops_text_gt_rconst(left vops_text, righttext) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
147+
createfunctionvops_text_gt_lconst(lefttext, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
148+
create operator> (leftarg=vops_text, rightarg=vops_text, procedure=vops_text_gt, commutator=<);
149+
create operator> (leftarg=vops_text, rightarg=text, procedure=vops_text_gt_rconst, commutator=<);
150+
create operator> (leftarg=text, rightarg=vops_text, procedure=vops_text_gt_lconst, commutator=<);
151+
152+
createfunctionvops_text_lt(left vops_text, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
153+
createfunctionvops_text_lt_rconst(left vops_text, righttext) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
154+
createfunctionvops_text_lt_lconst(lefttext, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
155+
create operator< (leftarg=vops_text, rightarg=vops_text, procedure=vops_text_lt, commutator=>);
156+
create operator< (leftarg=vops_text, rightarg=text, procedure=vops_text_lt_rconst, commutator=>);
157+
create operator< (leftarg=text, rightarg=vops_text, procedure=vops_text_lt_lconst, commutator=>);
158+
159+
createfunctionvops_text_ge(left vops_text, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
160+
createfunctionvops_text_ge_rconst(left vops_text, righttext) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
161+
createfunctionvops_text_ge_lconst(lefttext, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
162+
create operator>= (leftarg=vops_text, rightarg=vops_text, procedure=vops_text_ge, commutator=<=);
163+
create operator>= (leftarg=vops_text, rightarg=text, procedure=vops_text_ge_rconst, commutator=<=);
164+
create operator>= (leftarg=text, rightarg=vops_text, procedure=vops_text_ge_lconst, commutator=<=);
165+
166+
createfunctionvops_text_le(left vops_text, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
167+
createfunctionvops_text_le_rconst(left vops_text, righttext) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
168+
createfunctionvops_text_le_lconst(lefttext, right vops_text) returns vops_boolas'MODULE_PATHNAME' language C parallel safe immutable strict;
169+
create operator<= (leftarg=vops_text, rightarg=vops_text, procedure=vops_text_le, commutator=>=);
170+
create operator<= (leftarg=vops_text, rightarg=text, procedure=vops_text_le_rconst, commutator=>=);
171+
create operator<= (leftarg=text, rightarg=vops_text, procedure=vops_text_le_lconst, commutator=>=);
172+
173+
createfunctionbetwixt(opd vops_text, lowtext, hightext) returns vops_boolas'MODULE_PATHNAME','vops_betwixt_text' language C parallel safe immutable strict;
174+
175+
createfunctionifnull(opd vops_text, substtext) returns vops_textas'MODULE_PATHNAME','vops_ifnull_text' language C parallel safe immutable strict;
176+
createfunctionifnull(opd vops_text, subst vops_text) returns vops_textas'MODULE_PATHNAME','vops_coalesce_text' language C parallel safe immutable strict;
177+
178+
createfunctionfirst(tile vops_text) returnstextas'MODULE_PATHNAME','vops_text_first' language C parallel safe immutable strict;
179+
createfunctionlast(tile vops_text) returnstextas'MODULE_PATHNAME','vops_text_last' language C parallel safe immutable strict;
180+
createfunctionlow(tile vops_text) returnstextas'MODULE_PATHNAME','vops_text_low' language C parallel safe immutable strict;
181+
createfunctionhigh(tile vops_text) returnstextas'MODULE_PATHNAME','vops_text_high' language C parallel safe immutable strict;
182+
113183
-- char tile
114184

115185
createfunctionvops_char_const(opd"char") returns vops_charas'MODULE_PATHNAME' language C parallel safe immutable strict;
@@ -442,10 +512,10 @@ CREATE AGGREGATE mcount(vops_char) (
442512
PARALLEL= SAFE
443513
);
444514

445-
createfunctionfirst(tile vops_char) returnscharas'MODULE_PATHNAME','vops_char_first' language C parallel safe immutable strict;
446-
createfunctionlast(tile vops_char) returnscharas'MODULE_PATHNAME','vops_char_last' language C parallel safe immutable strict;
447-
createfunctionlow(tile vops_char) returnscharas'MODULE_PATHNAME','vops_char_low' language C parallel safe immutable strict;
448-
createfunctionhigh(tile vops_char) returnscharas'MODULE_PATHNAME','vops_char_high' language C parallel safe immutable strict;
515+
createfunctionfirst(tile vops_char) returns"char"as'MODULE_PATHNAME','vops_char_first' language C parallel safe immutable strict;
516+
createfunctionlast(tile vops_char) returns"char"as'MODULE_PATHNAME','vops_char_last' language C parallel safe immutable strict;
517+
createfunctionlow(tile vops_char) returns"char"as'MODULE_PATHNAME','vops_char_low' language C parallel safe immutable strict;
518+
createfunctionhigh(tile vops_char) returns"char"as'MODULE_PATHNAME','vops_char_high' language C parallel safe immutable strict;
449519

450520
-- int2 tile
451521

@@ -3031,6 +3101,8 @@ create cast (vops_bool as bool) with function filter(vops_bool) AS IMPLICIT;
30313101
createfunctionis_null(anyelement) returns vops_boolas'MODULE_PATHNAME','vops_is_null' language C parallel safe immutable;
30323102
createfunctionis_not_null(anyelement) returns vops_boolas'MODULE_PATHNAME','vops_is_not_null' language C parallel safe immutable;
30333103

3104+
-- VOPS FDW
3105+
30343106
CREATEFUNCTIONvops_fdw_handler()
30353107
RETURNS fdw_handler
30363108
AS'MODULE_PATHNAME'
@@ -3046,4 +3118,120 @@ CREATE FOREIGN DATA WRAPPER vops_fdw
30463118
VALIDATOR vops_fdw_validator;
30473119

30483120
CREATE SERVER vops_server FOREIGN DATA WRAPPER vops_fdw;
3049-
3121+
3122+
-- Projection generator
3123+
3124+
createtablevops_projections(projectiontextprimary key, source_tableoid, vector_columnsinteger[], scalar_columnsinteger[], key_nametext);
3125+
createindexon vops_projections(source_table);
3126+
3127+
3128+
createfunctiondrop_projection(projection_nametext) returns voidas $drop$
3129+
begin
3130+
execute'drop table'||projection_name;
3131+
execute'drop function'||projection_name||'_refresh()';
3132+
deletefrom vops_projectionswhere projection=projection_name;
3133+
end;
3134+
$drop$ language plpgsql;
3135+
3136+
3137+
createfunctioncreate_projection(projection_nametext, source_table regclass, vector_columnstext[], scalar_columnstext[] defaultnull, order_bytext defaultnull) returns voidas $create$
3138+
declare
3139+
create_tabletext;
3140+
create_functext;
3141+
create_indextext;
3142+
vector_attnointeger[];
3143+
scalar_attnointeger[];
3144+
att_numinteger;
3145+
att_nametext;
3146+
att_typnametext;
3147+
att_typidinteger;
3148+
septext :='';
3149+
key_typetext;
3150+
min_valuetext;
3151+
iinteger;
3152+
att_typmodinteger;
3153+
begin
3154+
create_table :='create table'||projection_name||'(';
3155+
create_func :='create function'||projection_name||'_refresh() returns bigint as $$ select populate(source:='''||source_table::text||''',destination:='''||projection_name||''',sort:=''';
3156+
if scalar_columnsis not null
3157+
then
3158+
create_index :='create index on'||projection_name||' using brin(';
3159+
foreach att_nameIN ARRAY scalar_columns
3160+
loop
3161+
select atttypid,attnum,typname into att_typid,att_num,att_typnamefrom pg_attribute,pg_typewhere attrelid=source_table::oidand attname=att_nameand atttypid=pg_type.oid;
3162+
if att_typid isnull
3163+
then
3164+
raise exception'No attribute % in table %',att_name,source_table;
3165+
end if;
3166+
scalar_attno := scalar_attno||att_num;
3167+
if att_typname='char'
3168+
then
3169+
att_typname:='"char"';
3170+
end if;
3171+
create_table := create_table||sep||att_name||''||att_typname;
3172+
create_func := create_func||sep||att_name;
3173+
create_index := create_index||sep||att_name;
3174+
sep :=',';
3175+
end loop;
3176+
end if;
3177+
3178+
if order_byis not null
3179+
then
3180+
create_func := create_func||sep||order_by;
3181+
end if;
3182+
create_func := create_func||'''';-- end of sort list
3183+
3184+
foreach att_nameIN ARRAY vector_columns
3185+
loop
3186+
select atttypid,attnum,typname,atttypmod into att_typid,att_num,att_typname,att_typmodfrom pg_attribute,pg_typewhere attrelid=source_table::oidand attname=att_nameand atttypid=pg_type.oid;
3187+
if att_typid isnull
3188+
then
3189+
raise exception'No attribute % in table %',att_name,source_table;
3190+
end if;
3191+
if att_typname='bpchar'or att_typname='varchar'
3192+
then
3193+
att_typname:='text('||att_typmod||')';
3194+
end if;
3195+
vector_attno := vector_attno||att_num;
3196+
create_table := create_table||sep||att_name||' vops_'||att_typname;
3197+
sep :=',';
3198+
if att_name=order_by
3199+
then
3200+
key_type= typname;
3201+
end if;
3202+
end loop;
3203+
3204+
create_table := create_table||')';
3205+
execute create_table;
3206+
3207+
if create_indexis not null
3208+
then
3209+
create_index := create_index||')';
3210+
execute create_index;
3211+
end if;
3212+
3213+
if order_byis not null
3214+
then
3215+
if key_type isnull
3216+
then
3217+
raise exception'Invalid order column % for projection %',order_by,projection_name;
3218+
end if;
3219+
create_index :='create index on'||projection_name||' using brin(first('||order_by||'))';
3220+
execute create_index;
3221+
create_index :='create index on'||projection_name||' using brin(last('||order_by||'))';
3222+
execute create_index;
3223+
if key_type='timestamp'or key_type='date'
3224+
then
3225+
min_value :='''-infinity''';
3226+
else
3227+
min_value :='-1';-- assume that key have only non-negative values
3228+
end if;
3229+
create_func := create_func||',predicate:='''||order_by||'>(select coalesce(max(last('||order_by||')),'||min_value||') from'||projection_name||')''';
3230+
end if;
3231+
create_func := create_func||'); $$ language sql';
3232+
execute create_func;
3233+
3234+
insert into vops_projectionsvalues (projection_name, source_table, vector_attno, scalar_attno, order_by);
3235+
end;
3236+
$create$ language plpgsql;
3237+

0 commit comments

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