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

Commit0fcb708

Browse files
committed
Add test for substitution paritions in join
1 parentcb299ad commit0fcb708

File tree

5 files changed

+250
-17
lines changed

5 files changed

+250
-17
lines changed

‎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,3 +398,102 @@ 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+

‎sql/test.sql

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,3 +58,74 @@ select vstock_refresh();
5858
selectavg((open+close)/2),max(high-low)from stockgroup by symbol;
5959
setvops.auto_substitute_projections=off;
6060
selectavg((open+close)/2),max(high-low)from stockgroup by symbol;
61+
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;

‎vops.c

Lines changed: 68 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1011,15 +1011,20 @@ static void
10111011
UserTableUpdateOpenIndexes()
10121012
{
10131013
List*recheckIndexes=NIL;
1014+
#ifPG_VERSION_NUM>=120000
1015+
HeapTupletuple=ExecFetchSlotHeapTuple(slot, true,NULL);
1016+
#else
1017+
HeapTupletuple=slot->tts_tuple;
1018+
#endif
10141019

10151020
/* HOT update does not require index inserts */
1016-
if (HeapTupleIsHeapOnly(slot->tts_tuple))
1021+
if (HeapTupleIsHeapOnly(tuple))
10171022
return;
10181023

10191024
if (estate->es_result_relation_info->ri_NumIndices>0)
10201025
{
10211026
recheckIndexes=ExecInsertIndexTuples(slot,
1022-
&slot->tts_tuple->t_self,
1027+
&tuple->t_self,
10231028
estate, false,NULL,NIL);
10241029

10251030
if (recheckIndexes!=NIL)
@@ -1034,7 +1039,7 @@ static void begin_batch_insert(Oid oid)
10341039
{
10351040
ResultRelInfo*resultRelInfo;
10361041

1037-
rel=heap_open(oid,NoLock);
1042+
rel=heap_open(oid,RowExclusiveLock);
10381043

10391044
PushActiveSnapshot(GetTransactionSnapshot());
10401045

@@ -1049,7 +1054,9 @@ static void begin_batch_insert(Oid oid)
10491054
estate->es_num_result_relations=1;
10501055
estate->es_result_relation_info=resultRelInfo;
10511056
ExecOpenIndices(estate->es_result_relation_info, false);
1052-
#ifPG_VERSION_NUM>=110000
1057+
#ifPG_VERSION_NUM>=120000
1058+
slot=ExecInitExtraTupleSlot(estate,RelationGetDescr(rel),&TTSOpsHeapTuple);
1059+
#elifPG_VERSION_NUM>=110000
10531060
slot=ExecInitExtraTupleSlot(estate,RelationGetDescr(rel));
10541061
#else
10551062
slot=ExecInitExtraTupleSlot(estate);
@@ -1060,8 +1067,13 @@ static void begin_batch_insert(Oid oid)
10601067
staticvoidinsert_tuple(Datum*values,bool*nulls)
10611068
{
10621069
HeapTupletup=heap_form_tuple(RelationGetDescr(rel),values,nulls);
1070+
#ifPG_VERSION_NUM>=120000
1071+
ExecStoreHeapTuple(tup,slot, true);
1072+
simple_heap_insert(rel,ExecFetchSlotHeapTuple(slot, true,NULL));
1073+
#else
10631074
ExecStoreTuple(tup,slot,InvalidBuffer, true);
10641075
simple_heap_insert(rel,slot->tts_tuple);
1076+
#endif
10651077
UserTableUpdateOpenIndexes();
10661078
}
10671079

@@ -2976,7 +2988,11 @@ Datum vops_unnest(PG_FUNCTION_ARGS)
29762988
user_ctx->nulls= (bool*)palloc(sizeof(bool)*n_attrs);
29772989
user_ctx->types= (vops_type*)palloc(sizeof(vops_type)*n_attrs);
29782990
user_ctx->tiles= (vops_tile_hdr**)palloc(sizeof(vops_tile_hdr*)*n_attrs);
2991+
#ifPG_VERSION_NUM>=120000
2992+
user_ctx->desc=CreateTemplateTupleDesc(n_attrs);
2993+
#else
29792994
user_ctx->desc=CreateTemplateTupleDesc(n_attrs, false);
2995+
#endif
29802996
func_ctx->user_fctx=user_ctx;
29812997
user_ctx->n_attrs=n_attrs;
29822998
user_ctx->tile_pos=0;
@@ -3535,10 +3551,6 @@ vops_pullvars_walker(Node *node, vops_pullvar_context *ctx)
35353551
ctx->scope=scope;
35363552
node= (Node*)from->fromlist;
35373553
}
3538-
elseif (IsA(node,Query))
3539-
{
3540-
returnquery_tree_walker((Query*)node,vops_pullvars_walker,ctx,0);
3541-
}
35423554
(void)expression_tree_walker(node,vops_pullvars_walker,ctx);
35433555
ctx->scope=scope;
35443556
return false;
@@ -3652,6 +3664,42 @@ vops_add_literal_type_casts(Node* node, Const** consts)
36523664
returnnode;
36533665
}
36543666

3667+
staticRangeVar*
3668+
vops_get_join_rangevar(Node*node,int*relno)
3669+
{
3670+
if (IsA(node,JoinExpr))
3671+
{
3672+
RangeVar*rv;
3673+
JoinExpr*join= (JoinExpr*)node;
3674+
rv=vops_get_join_rangevar(join->larg,relno);
3675+
if (rv!=NULL)
3676+
returnrv;
3677+
rv=vops_get_join_rangevar(join->rarg,relno);
3678+
if (rv!=NULL)
3679+
returnrv;
3680+
}
3681+
else
3682+
{
3683+
Assert(IsA(node,RangeVar));
3684+
if (--*relno==0)
3685+
return (RangeVar*)node;
3686+
}
3687+
returnNULL;
3688+
}
3689+
3690+
staticRangeVar*
3691+
vops_get_rangevar(List*from,intrelno)
3692+
{
3693+
ListCell*cell;
3694+
foreach (cell,from)
3695+
{
3696+
Node*node= (Node*)lfirst(cell);
3697+
RangeVar*rv=vops_get_join_rangevar(node,&relno);
3698+
if (rv!=NULL)
3699+
returnrv;
3700+
}
3701+
Assert(false);
3702+
}
36553703
/*
36563704
* Try to substitute tables with their VOPS projections.
36573705
* Criterias for such substitution:
@@ -3685,7 +3733,7 @@ vops_substitute_tables_with_projections(char const* queryString, Query *query)
36853733
pullvar_ctx.consts= (Const**)palloc0((strlen(queryString)+1)*sizeof(Const*));
36863734
pullvar_ctx.scope=SCOPE_DEFAULT;
36873735
pullvar_ctx.refs=palloc0(n_rels*sizeof(vops_table_refs));
3688-
query_tree_walker(query,vops_pullvars_walker,&pullvar_ctx,0);
3736+
query_tree_walker(query,vops_pullvars_walker,&pullvar_ctx,QTW_IGNORE_CTE_SUBQUERIES|QTW_IGNORE_RANGE_TABLE);
36893737

36903738
SPI_connect();
36913739

@@ -3768,7 +3816,10 @@ vops_substitute_tables_with_projections(char const* queryString, Query *query)
37683816
}
37693817
/* Replace table with partition */
37703818
elog(DEBUG1,"Use projection %s instead of table %d",projectionName,rte->relid);
3771-
rv=list_nth_node(RangeVar,select->fromClause,fromno-1);
3819+
rv=vops_get_rangevar(select->fromClause,fromno);
3820+
Assert(rv!=NULL);
3821+
if (rv->alias==NULL)
3822+
rv->alias=makeAlias(rv->relname,NULL);
37723823
rv->relname=pstrdup(projectionName);
37733824

37743825
/* Update vector/scalar bitmap sets for this query for this projection */
@@ -3949,8 +4000,13 @@ static void vops_explain_hook(Query *query,
39494000
params==NULL)/* do not support prepared statements yet */
39504001
{
39514002
char*explain=pstrdup(queryString);
3952-
char*select=strstr(explain,"select");
3953-
size_tprefix= (select!=NULL) ? (select-explain) :7;
4003+
char*select;
4004+
size_tprefix;
4005+
select=strstr(explain,"select");
4006+
if (select==NULL) {
4007+
select=strstr(explain,"SELECT");
4008+
}
4009+
prefix= (select!=NULL) ? (select-explain) :7;
39544010
memset(explain,' ',prefix);/* clear "explain" prefix: we need to preseve node locations */
39554011
vops_substitute_tables_with_projections(explain,query);
39564012
}

‎vops_fdw.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -784,7 +784,11 @@ postgresIterateForeignScan(ForeignScanState *node)
784784
*/
785785
MemoryContextSwitchTo(oldcontext);
786786
tup=heap_form_tuple(fsstate->tupdesc,fsstate->dst_values,fsstate->dst_nulls);
787-
ExecStoreTuple(tup,slot,InvalidBuffer, false);
787+
#ifPG_VERSION_NUM>=120000
788+
ExecStoreHeapTuple(tup,slot, false);
789+
#else
790+
ExecStoreTuple(tup,slot,InvalidBuffer, false);
791+
#endif
788792
returnslot;
789793
}
790794
NextTuple:;

0 commit comments

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