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

Commitf8f6e44

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Improve cost and size estimation for aggregate pushdown.
In commit7012b13, which added aggregatepushdown to postgres_fdw, we didn't account for the evaluation cost and theselectivity of HAVING quals attached to ForeignPaths performing aggregatepushdown, as core had never accounted for that for AggPaths and GroupPaths.And we didn't set these values of the locally-checked quals (ie, fpinfo'slocal_conds_cost and local_conds_sel), which were initialized to zeros, butsince estimate_path_cost_size factors in these to estimate the result sizeand the evaluation cost of such a ForeignPath when the use_remote_estimateoption is enabled, this caused it to produce underestimated results in thatcase.By commit7b6c075 core was changed so thatit accounts for the evaluation cost and the selectivity of HAVING quals inaggregation paths, so change the postgres_fdw's aggregate pushdown code aswell as such. This not only fixes the underestimation issue mentionedabove, but improves the estimation using local statistics in that functionwhen that option is disabled.This would be a bug fix rather than an improvement, but apply it to HEADonly to avoid destabilizing existing plan choices.Author: Etsuro FujitaDiscussion:https://postgr.es/m/5BFD3EAD.2060301%40lab.ntt.co.jp
1 parentafc4a78 commitf8f6e44

File tree

3 files changed

+54
-7
lines changed

3 files changed

+54
-7
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3209,6 +3209,8 @@ select array_agg(c1 order by c1 using operator(public.<^)) from ft2 where c2 = 6
32093209
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) AND ((c2 = 6))
32103210
(6 rows)
32113211

3212+
-- Update local stats on ft2
3213+
ANALYZE ft2;
32123214
-- Add into extension
32133215
alter extension postgres_fdw add operator class my_op_class using btree;
32143216
alter extension postgres_fdw add function my_op_cmp(a int, b int);

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 49 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -2844,10 +2844,6 @@ estimate_path_cost_size(PlannerInfo *root,
28442844
* strategy will be considered at remote side, thus for
28452845
* simplicity, we put all startup related costs in startup_cost
28462846
* and all finalization and run cost are added in total_cost.
2847-
*
2848-
* Also, core does not care about costing HAVING expressions and
2849-
* adding that to the costs. So similarly, here too we are not
2850-
* considering remote and local conditions for costing.
28512847
*/
28522848

28532849
ofpinfo= (PgFdwRelationInfo*)fpinfo->outerrel->fdw_private;
@@ -2880,10 +2876,26 @@ estimate_path_cost_size(PlannerInfo *root,
28802876
input_rows,NULL);
28812877

28822878
/*
2883-
*Number of rows expected from foreign server will be same as
2884-
*that of number of groups.
2879+
*Get the retrieved_rows and rows estimates. If there are HAVING
2880+
*quals, account for their selectivity.
28852881
*/
2886-
rows=retrieved_rows=numGroups;
2882+
if (root->parse->havingQual)
2883+
{
2884+
/* Factor in the selectivity of the remotely-checked quals */
2885+
retrieved_rows=
2886+
clamp_row_est(numGroups*
2887+
clauselist_selectivity(root,
2888+
fpinfo->remote_conds,
2889+
0,
2890+
JOIN_INNER,
2891+
NULL));
2892+
/* Factor in the selectivity of the locally-checked quals */
2893+
rows=clamp_row_est(retrieved_rows*fpinfo->local_conds_sel);
2894+
}
2895+
else
2896+
{
2897+
rows=retrieved_rows=numGroups;
2898+
}
28872899

28882900
/*-----
28892901
* Startup cost includes:
@@ -2909,6 +2921,20 @@ estimate_path_cost_size(PlannerInfo *root,
29092921
run_cost+=aggcosts.finalCost*numGroups;
29102922
run_cost+=cpu_tuple_cost*numGroups;
29112923
run_cost+=ptarget->cost.per_tuple*numGroups;
2924+
2925+
/* Accout for the eval cost of HAVING quals, if any */
2926+
if (root->parse->havingQual)
2927+
{
2928+
QualCostremote_cost;
2929+
2930+
/* Add in the eval cost of the remotely-checked quals */
2931+
cost_qual_eval(&remote_cost,fpinfo->remote_conds,root);
2932+
startup_cost+=remote_cost.startup;
2933+
run_cost+=remote_cost.per_tuple*numGroups;
2934+
/* Add in the eval cost of the locally-checked quals */
2935+
startup_cost+=fpinfo->local_conds_cost.startup;
2936+
run_cost+=fpinfo->local_conds_cost.per_tuple*retrieved_rows;
2937+
}
29122938
}
29132939
else
29142940
{
@@ -5496,6 +5522,22 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
54965522
if (!foreign_grouping_ok(root,grouped_rel,extra->havingQual))
54975523
return;
54985524

5525+
/*
5526+
* Compute the selectivity and cost of the local_conds, so we don't have
5527+
* to do it over again for each path. (Currently we create just a single
5528+
* path here, but in future it would be possible that we build more paths
5529+
* such as pre-sorted paths as in postgresGetForeignPaths and
5530+
* postgresGetForeignJoinPaths.) The best we can do for these conditions
5531+
* is to estimate selectivity on the basis of local statistics.
5532+
*/
5533+
fpinfo->local_conds_sel=clauselist_selectivity(root,
5534+
fpinfo->local_conds,
5535+
0,
5536+
JOIN_INNER,
5537+
NULL);
5538+
5539+
cost_qual_eval(&fpinfo->local_conds_cost,fpinfo->local_conds,root);
5540+
54995541
/* Estimate the cost of push down */
55005542
estimate_path_cost_size(root,grouped_rel,NIL,NIL,&rows,
55015543
&width,&startup_cost,&total_cost);

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -807,6 +807,9 @@ create operator class my_op_class for type int using btree family my_op_family a
807807
explain (verbose, costs off)
808808
select array_agg(c1order by c1 using operator(public.<^))from ft2where c2=6and c1<100group by c2;
809809

810+
-- Update local stats on ft2
811+
ANALYZE ft2;
812+
810813
-- Add into extension
811814
alter extension postgres_fdw add operator class my_op_class using btree;
812815
alter extension postgres_fdw add function my_op_cmp(aint, bint);

0 commit comments

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