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

Commit37fa849

Browse files
committed
Add first/last group aggregates
2 parentsba41dc6 +e4d4eb0 commit37fa849

File tree

6 files changed

+1222
-50
lines changed

6 files changed

+1222
-50
lines changed

‎README.md‎

Lines changed: 971 additions & 33 deletions
Large diffs are not rendered by default.

‎deparse.c‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -684,6 +684,7 @@ deparseTargetList(StringInfo buf,
684684
*retrieved_attrs=lappend_int(*retrieved_attrs,
685685
SelfItemPointerAttributeNumber);
686686
}
687+
#ifPG_VERSION_NUM<120000
687688
if (bms_is_member(ObjectIdAttributeNumber-FirstLowInvalidHeapAttributeNumber,
688689
attrs_used))
689690
{
@@ -700,7 +701,7 @@ deparseTargetList(StringInfo buf,
700701
*retrieved_attrs=lappend_int(*retrieved_attrs,
701702
ObjectIdAttributeNumber);
702703
}
703-
704+
#endif
704705
/* Don't generate bad syntax if no undropped columns */
705706
if (first&& !is_returning)
706707
appendStringInfoString(buf,"NULL");
@@ -1256,12 +1257,14 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root,
12561257
ADD_REL_QUALIFIER(buf,varno);
12571258
appendStringInfoString(buf,"ctid");
12581259
}
1260+
#ifPG_VERSION_NUM<120000
12591261
elseif (varattno==ObjectIdAttributeNumber)
12601262
{
12611263
if (qualify_col)
12621264
ADD_REL_QUALIFIER(buf,varno);
12631265
appendStringInfoString(buf,"oid");
12641266
}
1267+
#endif
12651268
elseif (varattno<0)
12661269
{
12671270
/*

‎expected/test.out‎

Lines changed: 102 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -360,11 +360,11 @@ select avg((open+close)/2),max(high-low) from stock group by symbol;
360360

361361
set vops.auto_substitute_projections=on;
362362
explain (costs off) select avg((open+close)/2),max(high-low) from stock group by symbol;
363-
QUERY PLAN
364-
--------------------------
363+
QUERY PLAN
364+
--------------------------------
365365
HashAggregate
366366
Group Key: symbol
367-
-> Seq Scan on vstock
367+
-> Seq Scan on vstock stock
368368
(3 rows)
369369

370370
select avg((open+close)/2),max(high-low) from stock group by symbol;
@@ -398,6 +398,105 @@ select avg((open+close)/2),max(high-low) from stock group by symbol;
398398
10.9200000762939 | 1
399399
(1 row)
400400

401+
create table wiki_data(
402+
cat_id bigint,
403+
page_id bigint,
404+
requests int,
405+
size bigint,
406+
dyear int,
407+
dmonth int,
408+
dday int,
409+
dhour int
410+
);
411+
create table wiki_cat
412+
( cat_id bigint primary key,
413+
category varchar(20))
414+
;
415+
insert into wiki_data values
416+
(101,1001,123,456),
417+
(101,1002,789,123),
418+
(101,1003,456,789),
419+
(102,2001,123,456),
420+
(102,2002,789,123),
421+
(103,3001,456,789);
422+
insert into wiki_cat values
423+
(101, 'cat 101'),
424+
(102, 'cat 102'),
425+
(103, 'cat 103');
426+
select create_projection('wiki_data_prj', 'wiki_data', array['page_id','requests','size'],array['cat_id']);
427+
create_projection
428+
-------------------
429+
430+
(1 row)
431+
432+
select wiki_data_prj_refresh();
433+
wiki_data_prj_refresh
434+
-----------------------
435+
6
436+
(1 row)
437+
438+
SELECT
439+
category,
440+
sum( requests ),
441+
sum( size )
442+
FROM
443+
wiki_data
444+
INNER JOIN wiki_cat
445+
ON wiki_data.cat_id = wiki_cat.cat_id
446+
GROUP BY
447+
category
448+
ORDER BY 3 DESC limit 5;
449+
category | sum | sum
450+
----------+------+------
451+
cat 101 | 1368 | 1368
452+
cat 103 | 456 | 789
453+
cat 102 | 912 | 579
454+
(3 rows)
455+
456+
set vops.auto_substitute_projections=on;
457+
SELECT
458+
category,
459+
sum( requests ),
460+
sum( size )
461+
FROM
462+
wiki_data
463+
INNER JOIN wiki_cat
464+
ON wiki_data.cat_id = wiki_cat.cat_id
465+
GROUP BY
466+
category
467+
ORDER BY 3 DESC limit 5;
468+
category | sum | sum
469+
----------+------+------
470+
cat 101 | 1368 | 1368
471+
cat 103 | 456 | 789
472+
cat 102 | 912 | 579
473+
(3 rows)
474+
475+
explain (costs off) SELECT
476+
category,
477+
sum( requests ),
478+
sum( size )
479+
FROM
480+
wiki_data
481+
INNER JOIN wiki_cat
482+
ON wiki_data.cat_id = wiki_cat.cat_id
483+
GROUP BY
484+
category
485+
ORDER BY 3 DESC limit 5;
486+
QUERY PLAN
487+
---------------------------------------------------------------------
488+
Limit
489+
-> Sort
490+
Sort Key: (sum(wiki_data.size)) DESC
491+
-> HashAggregate
492+
Group Key: wiki_cat.category
493+
-> Hash Join
494+
Hash Cond: (wiki_cat.cat_id = wiki_data.cat_id)
495+
-> Seq Scan on wiki_cat
496+
-> Hash
497+
-> Seq Scan on wiki_data_prj wiki_data
498+
(10 rows)
499+
401500
create table quote(symbol char(5), ts timestamp, ask_price real, ask_size integer, bid_price real, bid_size integer);
402501
insert into quote values
403502
('AAA', '03-12-2018 10:00', 10.0, 100, 10.1, 202),

‎sql/test.sql‎

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,77 @@ select avg((open+close)/2),max(high-low) from stock group by symbol;
5959
setvops.auto_substitute_projections=off;
6060
selectavg((open+close)/2),max(high-low)from stockgroup by symbol;
6161

62+
createtablewiki_data(
63+
cat_idbigint,
64+
page_idbigint,
65+
requestsint,
66+
sizebigint,
67+
dyearint,
68+
dmonthint,
69+
ddayint,
70+
dhourint
71+
);
72+
73+
createtablewiki_cat
74+
( cat_idbigintprimary key,
75+
categoryvarchar(20))
76+
;
77+
78+
insert into wiki_datavalues
79+
(101,1001,123,456),
80+
(101,1002,789,123),
81+
(101,1003,456,789),
82+
(102,2001,123,456),
83+
(102,2002,789,123),
84+
(103,3001,456,789);
85+
86+
insert into wiki_catvalues
87+
(101,'cat 101'),
88+
(102,'cat 102'),
89+
(103,'cat 103');
90+
91+
select create_projection('wiki_data_prj','wiki_data', array['page_id','requests','size'],array['cat_id']);
92+
93+
select wiki_data_prj_refresh();
94+
95+
SELECT
96+
category,
97+
sum( requests ),
98+
sum( size )
99+
FROM
100+
wiki_data
101+
INNER JOIN wiki_cat
102+
ONwiki_data.cat_id=wiki_cat.cat_id
103+
GROUP BY
104+
category
105+
ORDER BY3DESClimit5;
106+
107+
setvops.auto_substitute_projections=on;
108+
109+
SELECT
110+
category,
111+
sum( requests ),
112+
sum( size )
113+
FROM
114+
wiki_data
115+
INNER JOIN wiki_cat
116+
ONwiki_data.cat_id=wiki_cat.cat_id
117+
GROUP BY
118+
category
119+
ORDER BY3DESClimit5;
120+
121+
explain (costs off)SELECT
122+
category,
123+
sum( requests ),
124+
sum( size )
125+
FROM
126+
wiki_data
127+
INNER JOIN wiki_cat
128+
ONwiki_data.cat_id=wiki_cat.cat_id
129+
GROUP BY
130+
category
131+
ORDER BY3DESClimit5;
132+
62133
createtablequote(symbolchar(5), tstimestamp, ask_pricereal, ask_sizeinteger, bid_pricereal, bid_sizeinteger);
63134
insert into quotevalues
64135
('AAA','03-12-2018 10:00',10.0,100,10.1,202),
@@ -71,3 +142,4 @@ select create_projection('vquote','quote',array['ts','ask_price','ask_size','bid
71142
select vquote_refresh();
72143

73144
select first(bid_price,ts),last(ask_size,ts)from vquotegroup by symbol;
145+

‎vops.c‎

Lines changed: 68 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1037,15 +1037,20 @@ static void
10371037
UserTableUpdateOpenIndexes()
10381038
{
10391039
List*recheckIndexes=NIL;
1040+
#ifPG_VERSION_NUM>=120000
1041+
HeapTupletuple=ExecFetchSlotHeapTuple(slot, true,NULL);
1042+
#else
1043+
HeapTupletuple=slot->tts_tuple;
1044+
#endif
10401045

10411046
/* HOT update does not require index inserts */
1042-
if (HeapTupleIsHeapOnly(slot->tts_tuple))
1047+
if (HeapTupleIsHeapOnly(tuple))
10431048
return;
10441049

10451050
if (estate->es_result_relation_info->ri_NumIndices>0)
10461051
{
10471052
recheckIndexes=ExecInsertIndexTuples(slot,
1048-
&slot->tts_tuple->t_self,
1053+
&tuple->t_self,
10491054
estate, false,NULL,NIL);
10501055

10511056
if (recheckIndexes!=NIL)
@@ -1060,7 +1065,7 @@ static void begin_batch_insert(Oid oid)
10601065
{
10611066
ResultRelInfo*resultRelInfo;
10621067

1063-
rel=heap_open(oid,NoLock);
1068+
rel=heap_open(oid,RowExclusiveLock);
10641069

10651070
PushActiveSnapshot(GetTransactionSnapshot());
10661071

@@ -1075,7 +1080,9 @@ static void begin_batch_insert(Oid oid)
10751080
estate->es_num_result_relations=1;
10761081
estate->es_result_relation_info=resultRelInfo;
10771082
ExecOpenIndices(estate->es_result_relation_info, false);
1078-
#ifPG_VERSION_NUM>=110000
1083+
#ifPG_VERSION_NUM>=120000
1084+
slot=ExecInitExtraTupleSlot(estate,RelationGetDescr(rel),&TTSOpsHeapTuple);
1085+
#elifPG_VERSION_NUM>=110000
10791086
slot=ExecInitExtraTupleSlot(estate,RelationGetDescr(rel));
10801087
#else
10811088
slot=ExecInitExtraTupleSlot(estate);
@@ -1086,8 +1093,13 @@ static void begin_batch_insert(Oid oid)
10861093
staticvoidinsert_tuple(Datum*values,bool*nulls)
10871094
{
10881095
HeapTupletup=heap_form_tuple(RelationGetDescr(rel),values,nulls);
1096+
#ifPG_VERSION_NUM>=120000
1097+
ExecStoreHeapTuple(tup,slot, true);
1098+
simple_heap_insert(rel,ExecFetchSlotHeapTuple(slot, true,NULL));
1099+
#else
10891100
ExecStoreTuple(tup,slot,InvalidBuffer, true);
10901101
simple_heap_insert(rel,slot->tts_tuple);
1102+
#endif
10911103
UserTableUpdateOpenIndexes();
10921104
}
10931105

@@ -3162,7 +3174,11 @@ Datum vops_unnest(PG_FUNCTION_ARGS)
31623174
user_ctx->nulls= (bool*)palloc(sizeof(bool)*n_attrs);
31633175
user_ctx->types= (vops_type*)palloc(sizeof(vops_type)*n_attrs);
31643176
user_ctx->tiles= (vops_tile_hdr**)palloc(sizeof(vops_tile_hdr*)*n_attrs);
3177+
#ifPG_VERSION_NUM>=120000
3178+
user_ctx->desc=CreateTemplateTupleDesc(n_attrs);
3179+
#else
31653180
user_ctx->desc=CreateTemplateTupleDesc(n_attrs, false);
3181+
#endif
31663182
func_ctx->user_fctx=user_ctx;
31673183
user_ctx->n_attrs=n_attrs;
31683184
user_ctx->tile_pos=0;
@@ -3721,10 +3737,6 @@ vops_pullvars_walker(Node *node, vops_pullvar_context *ctx)
37213737
ctx->scope=scope;
37223738
node= (Node*)from->fromlist;
37233739
}
3724-
elseif (IsA(node,Query))
3725-
{
3726-
returnquery_tree_walker((Query*)node,vops_pullvars_walker,ctx,0);
3727-
}
37283740
(void)expression_tree_walker(node,vops_pullvars_walker,ctx);
37293741
ctx->scope=scope;
37303742
return false;
@@ -3838,6 +3850,42 @@ vops_add_literal_type_casts(Node* node, Const** consts)
38383850
returnnode;
38393851
}
38403852

3853+
staticRangeVar*
3854+
vops_get_join_rangevar(Node*node,int*relno)
3855+
{
3856+
if (IsA(node,JoinExpr))
3857+
{
3858+
RangeVar*rv;
3859+
JoinExpr*join= (JoinExpr*)node;
3860+
rv=vops_get_join_rangevar(join->larg,relno);
3861+
if (rv!=NULL)
3862+
returnrv;
3863+
rv=vops_get_join_rangevar(join->rarg,relno);
3864+
if (rv!=NULL)
3865+
returnrv;
3866+
}
3867+
else
3868+
{
3869+
Assert(IsA(node,RangeVar));
3870+
if (--*relno==0)
3871+
return (RangeVar*)node;
3872+
}
3873+
returnNULL;
3874+
}
3875+
3876+
staticRangeVar*
3877+
vops_get_rangevar(List*from,intrelno)
3878+
{
3879+
ListCell*cell;
3880+
foreach (cell,from)
3881+
{
3882+
Node*node= (Node*)lfirst(cell);
3883+
RangeVar*rv=vops_get_join_rangevar(node,&relno);
3884+
if (rv!=NULL)
3885+
returnrv;
3886+
}
3887+
Assert(false);
3888+
}
38413889
/*
38423890
* Try to substitute tables with their VOPS projections.
38433891
* Criterias for such substitution:
@@ -3871,7 +3919,7 @@ vops_substitute_tables_with_projections(char const* queryString, Query *query)
38713919
pullvar_ctx.consts= (Const**)palloc0((strlen(queryString)+1)*sizeof(Const*));
38723920
pullvar_ctx.scope=SCOPE_DEFAULT;
38733921
pullvar_ctx.refs=palloc0(n_rels*sizeof(vops_table_refs));
3874-
query_tree_walker(query,vops_pullvars_walker,&pullvar_ctx,0);
3922+
query_tree_walker(query,vops_pullvars_walker,&pullvar_ctx,QTW_IGNORE_CTE_SUBQUERIES|QTW_IGNORE_RANGE_TABLE);
38753923

38763924
SPI_connect();
38773925

@@ -3954,7 +4002,10 @@ vops_substitute_tables_with_projections(char const* queryString, Query *query)
39544002
}
39554003
/* Replace table with partition */
39564004
elog(DEBUG1,"Use projection %s instead of table %d",projectionName,rte->relid);
3957-
rv=list_nth_node(RangeVar,select->fromClause,fromno-1);
4005+
rv=vops_get_rangevar(select->fromClause,fromno);
4006+
Assert(rv!=NULL);
4007+
if (rv->alias==NULL)
4008+
rv->alias=makeAlias(rv->relname,NULL);
39584009
rv->relname=pstrdup(projectionName);
39594010

39604011
/* Update vector/scalar bitmap sets for this query for this projection */
@@ -4135,8 +4186,13 @@ static void vops_explain_hook(Query *query,
41354186
params==NULL)/* do not support prepared statements yet */
41364187
{
41374188
char*explain=pstrdup(queryString);
4138-
char*select=strstr(explain,"select");
4139-
size_tprefix= (select!=NULL) ? (select-explain) :7;
4189+
char*select;
4190+
size_tprefix;
4191+
select=strstr(explain,"select");
4192+
if (select==NULL) {
4193+
select=strstr(explain,"SELECT");
4194+
}
4195+
prefix= (select!=NULL) ? (select-explain) :7;
41404196
memset(explain,' ',prefix);/* clear "explain" prefix: we need to preseve node locations */
41414197
vops_substitute_tables_with_projections(explain,query);
41424198
}

0 commit comments

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