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

Commitf18c944

Browse files
committed
postgres_fdw: Add ORDER BY to some remote SQL queries.
If the join problem's entire ORDER BY clause can be pushed to theremote server, consider a path that adds this ORDER BY clause. Ifuse_remote_estimate is on, we cost this path using an additionalremote EXPLAIN. If not, we just estimate that the path costs 20%more, which is intended to be large enough that we won't request aremote sort when it's not helpful, but small enough that we'll havethe remote side do the sort when in doubt. In some cases, the remotesort might actually be free, because the remote query plan mighthappen to produce output that is ordered the way we need, but withoutremote estimates we have no way of knowing that.It might also be useful to request sorted output from the remote sideif it enables an efficient merge join, but this patch doesn't attemptto handle that case.Ashutosh Bapat with revisions by me. Also reviewed by Fabrízio de RoyesMello and Jeevan Chalke.
1 parentfc0b893 commitf18c944

File tree

5 files changed

+222
-30
lines changed

5 files changed

+222
-30
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 53 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -193,9 +193,12 @@ is_foreign_expr(PlannerInfo *root,
193193
if (!foreign_expr_walker((Node*)expr,&glob_cxt,&loc_cxt))
194194
return false;
195195

196-
/* Expressions examined here should be boolean, ie noncollatable */
197-
Assert(loc_cxt.collation==InvalidOid);
198-
Assert(loc_cxt.state==FDW_COLLATE_NONE);
196+
/*
197+
* If the expression has a valid collation that does not arise from a
198+
* foreign var, the expression can not be sent over.
199+
*/
200+
if (loc_cxt.state==FDW_COLLATE_UNSAFE)
201+
return false;
199202

200203
/*
201204
* An expression which includes any mutable functions can't be sent over
@@ -1877,3 +1880,50 @@ printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
18771880

18781881
appendStringInfo(buf,"((SELECT null::%s)::%s)",ptypename,ptypename);
18791882
}
1883+
1884+
/*
1885+
* Deparse ORDER BY clause according to the given pathkeys for given base
1886+
* relation. From given pathkeys expressions belonging entirely to the given
1887+
* base relation are obtained and deparsed.
1888+
*/
1889+
void
1890+
appendOrderByClause(StringInfobuf,PlannerInfo*root,RelOptInfo*baserel,
1891+
List*pathkeys)
1892+
{
1893+
ListCell*lcell;
1894+
deparse_expr_cxtcontext;
1895+
intnestlevel;
1896+
char*delim=" ";
1897+
1898+
/* Set up context struct for recursion */
1899+
context.root=root;
1900+
context.foreignrel=baserel;
1901+
context.buf=buf;
1902+
context.params_list=NULL;
1903+
1904+
/* Make sure any constants in the exprs are printed portably */
1905+
nestlevel=set_transmission_modes();
1906+
1907+
appendStringInfo(buf," ORDER BY");
1908+
foreach(lcell,pathkeys)
1909+
{
1910+
PathKey*pathkey=lfirst(lcell);
1911+
Expr*em_expr;
1912+
1913+
em_expr=find_em_expr_for_rel(pathkey->pk_eclass,baserel);
1914+
Assert(em_expr!=NULL);
1915+
1916+
appendStringInfoString(buf,delim);
1917+
deparseExpr(em_expr,&context);
1918+
if (pathkey->pk_strategy==BTLessStrategyNumber)
1919+
appendStringInfoString(buf," ASC");
1920+
else
1921+
appendStringInfoString(buf," DESC");
1922+
1923+
if (pathkey->pk_nulls_first)
1924+
appendStringInfoString(buf," NULLS FIRST");
1925+
1926+
delim=", ";
1927+
}
1928+
reset_transmission_modes(nestlevel);
1929+
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 43 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -134,15 +134,13 @@ ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
134134
-- ===================================================================
135135
-- simple queries
136136
-- ===================================================================
137-
-- single table, with/without alias
137+
-- single tablewithout alias
138138
EXPLAIN (COSTS false) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
139-
QUERY PLAN
140-
---------------------------------
139+
QUERY PLAN
140+
---------------------------
141141
Limit
142-
-> Sort
143-
Sort Key: c3, c1
144-
-> Foreign Scan on ft1
145-
(4 rows)
142+
-> Foreign Scan on ft1
143+
(2 rows)
146144

147145
SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
148146
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
@@ -159,20 +157,21 @@ SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
159157
110 | 0 | 00110 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
160158
(10 rows)
161159

162-
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
160+
-- single table with alias - also test that tableoid sort is not pushed to remote side
161+
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10;
163162
QUERY PLAN
164163
-------------------------------------------------------------------------------------
165164
Limit
166-
Output: c1, c2, c3, c4, c5, c6, c7, c8
165+
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
167166
-> Sort
168-
Output: c1, c2, c3, c4, c5, c6, c7, c8
169-
Sort Key: t1.c3, t1.c1
167+
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
168+
Sort Key: t1.c3, t1.c1, t1.tableoid
170169
-> Foreign Scan on public.ft1 t1
171-
Output: c1, c2, c3, c4, c5, c6, c7, c8
170+
Output: c1, c2, c3, c4, c5, c6, c7, c8, tableoid
172171
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
173172
(8 rows)
174173

175-
SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
174+
SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10;
176175
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
177176
-----+----+-------+------------------------------+--------------------------+----+------------+-----
178177
101 | 1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
@@ -189,17 +188,14 @@ SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
189188

190189
-- whole-row reference
191190
EXPLAIN (VERBOSE, COSTS false) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
192-
QUERY PLAN
193-
-------------------------------------------------------------------------------------
191+
QUERY PLAN
192+
----------------------------------------------------------------------------------------------------------
194193
Limit
195194
Output: t1.*, c3, c1
196-
->Sort
195+
->Foreign Scan on public.ft1 t1
197196
Output: t1.*, c3, c1
198-
Sort Key: t1.c3, t1.c1
199-
-> Foreign Scan on public.ft1 t1
200-
Output: t1.*, c3, c1
201-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
202-
(8 rows)
197+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c3 ASC, "C 1" ASC
198+
(5 rows)
203199

204200
SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
205201
t1
@@ -650,6 +646,32 @@ SELECT * FROM ft2 WHERE c1 = ANY (ARRAY(SELECT c1 FROM ft1 WHERE c1 < 5));
650646
4 | 4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo
651647
(4 rows)
652648

649+
-- we should not push order by clause with volatile expressions or unsafe
650+
-- collations
651+
EXPLAIN (VERBOSE, COSTS false)
652+
SELECT * FROM ft2 ORDER BY ft2.c1, random();
653+
QUERY PLAN
654+
-------------------------------------------------------------------------------
655+
Sort
656+
Output: c1, c2, c3, c4, c5, c6, c7, c8, (random())
657+
Sort Key: ft2.c1, (random())
658+
-> Foreign Scan on public.ft2
659+
Output: c1, c2, c3, c4, c5, c6, c7, c8, random()
660+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
661+
(6 rows)
662+
663+
EXPLAIN (VERBOSE, COSTS false)
664+
SELECT * FROM ft2 ORDER BY ft2.c1, ft2.c3 collate "C";
665+
QUERY PLAN
666+
-------------------------------------------------------------------------------
667+
Sort
668+
Output: c1, c2, c3, c4, c5, c6, c7, c8, ((c3)::text)
669+
Sort Key: ft2.c1, ft2.c3 COLLATE "C"
670+
-> Foreign Scan on public.ft2
671+
Output: c1, c2, c3, c4, c5, c6, c7, c8, c3
672+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
673+
(6 rows)
674+
653675
-- ===================================================================
654676
-- parameterized queries
655677
-- ===================================================================

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 113 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,9 @@ PG_MODULE_MAGIC;
4747
/* Default CPU cost to process 1 row (above and beyond cpu_tuple_cost). */
4848
#defineDEFAULT_FDW_TUPLE_COST0.01
4949

50+
/* If no remote estimates, assume a sort costs 20% extra */
51+
#defineDEFAULT_FDW_SORT_MULTIPLIER 1.2
52+
5053
/*
5154
* FDW-specific planner information kept in RelOptInfo.fdw_private for a
5255
* foreign table. This information is collected by postgresGetForeignRelSize.
@@ -296,6 +299,7 @@ static List *postgresImportForeignSchema(ImportForeignSchemaStmt *stmt,
296299
staticvoidestimate_path_cost_size(PlannerInfo*root,
297300
RelOptInfo*baserel,
298301
List*join_conds,
302+
List*pathkeys,
299303
double*p_rows,int*p_width,
300304
Cost*p_startup_cost,Cost*p_total_cost);
301305
staticvoidget_remote_estimate(constchar*sql,
@@ -497,7 +501,7 @@ postgresGetForeignRelSize(PlannerInfo *root,
497501
* values in fpinfo so we don't need to do it again to generate the
498502
* basic foreign path.
499503
*/
500-
estimate_path_cost_size(root,baserel,NIL,
504+
estimate_path_cost_size(root,baserel,NIL,NIL,
501505
&fpinfo->rows,&fpinfo->width,
502506
&fpinfo->startup_cost,&fpinfo->total_cost);
503507

@@ -527,7 +531,7 @@ postgresGetForeignRelSize(PlannerInfo *root,
527531
set_baserel_size_estimates(root,baserel);
528532

529533
/* Fill in basically-bogus cost estimates for use later. */
530-
estimate_path_cost_size(root,baserel,NIL,
534+
estimate_path_cost_size(root,baserel,NIL,NIL,
531535
&fpinfo->rows,&fpinfo->width,
532536
&fpinfo->startup_cost,&fpinfo->total_cost);
533537
}
@@ -546,6 +550,7 @@ postgresGetForeignPaths(PlannerInfo *root,
546550
ForeignPath*path;
547551
List*ppi_list;
548552
ListCell*lc;
553+
List*usable_pathkeys=NIL;
549554

550555
/*
551556
* Create simplest ForeignScan path node and add it to baserel. This path
@@ -563,6 +568,60 @@ postgresGetForeignPaths(PlannerInfo *root,
563568
NIL);/* no fdw_private list */
564569
add_path(baserel, (Path*)path);
565570

571+
/*
572+
* Determine whether we can potentially push query pathkeys to the remote
573+
* side, avoiding a local sort.
574+
*/
575+
foreach(lc,root->query_pathkeys)
576+
{
577+
PathKey*pathkey= (PathKey*)lfirst(lc);
578+
EquivalenceClass*pathkey_ec=pathkey->pk_eclass;
579+
Expr*em_expr;
580+
581+
/*
582+
* is_foreign_expr would detect volatile expressions as well, but
583+
* ec_has_volatile saves some cycles.
584+
*/
585+
if (!pathkey_ec->ec_has_volatile&&
586+
(em_expr=find_em_expr_for_rel(pathkey_ec,baserel))&&
587+
is_foreign_expr(root,baserel,em_expr))
588+
usable_pathkeys=lappend(usable_pathkeys,pathkey);
589+
else
590+
{
591+
/*
592+
* The planner and executor don't have any clever strategy for
593+
* taking data sorted by a prefix of the query's pathkeys and
594+
* getting it to be sorted by all of those pathekeys. We'll just
595+
* end up resorting the entire data set. So, unless we can push
596+
* down all of the query pathkeys, forget it.
597+
*/
598+
list_free(usable_pathkeys);
599+
usable_pathkeys=NIL;
600+
break;
601+
}
602+
}
603+
604+
/* Create a path with useful pathkeys, if we found one. */
605+
if (usable_pathkeys!=NULL)
606+
{
607+
doublerows;
608+
intwidth;
609+
Coststartup_cost;
610+
Costtotal_cost;
611+
612+
estimate_path_cost_size(root,baserel,NIL,usable_pathkeys,
613+
&rows,&width,&startup_cost,&total_cost);
614+
615+
add_path(baserel, (Path*)
616+
create_foreignscan_path(root,baserel,
617+
rows,
618+
startup_cost,
619+
total_cost,
620+
usable_pathkeys,
621+
NULL,
622+
NIL));
623+
}
624+
566625
/*
567626
* If we're not using remote estimates, stop here. We have no way to
568627
* estimate whether any join clauses would be worth sending across, so
@@ -710,7 +769,7 @@ postgresGetForeignPaths(PlannerInfo *root,
710769

711770
/* Get a cost estimate from the remote */
712771
estimate_path_cost_size(root,baserel,
713-
param_info->ppi_clauses,
772+
param_info->ppi_clauses,NIL,
714773
&rows,&width,
715774
&startup_cost,&total_cost);
716775

@@ -811,6 +870,10 @@ postgresGetForeignPlan(PlannerInfo *root,
811870
appendWhereClause(&sql,root,baserel,remote_conds,
812871
true,&params_list);
813872

873+
/* Add ORDER BY clause if we found any useful pathkeys */
874+
if (best_path->path.pathkeys)
875+
appendOrderByClause(&sql,root,baserel,best_path->path.pathkeys);
876+
814877
/*
815878
* Add FOR UPDATE/SHARE if appropriate. We apply locking during the
816879
* initial row fetch, rather than later on as is done for local tables.
@@ -1728,6 +1791,7 @@ static void
17281791
estimate_path_cost_size(PlannerInfo*root,
17291792
RelOptInfo*baserel,
17301793
List*join_conds,
1794+
List*pathkeys,
17311795
double*p_rows,int*p_width,
17321796
Cost*p_startup_cost,Cost*p_total_cost)
17331797
{
@@ -1780,6 +1844,9 @@ estimate_path_cost_size(PlannerInfo *root,
17801844
appendWhereClause(&sql,root,baserel,remote_join_conds,
17811845
(fpinfo->remote_conds==NIL),NULL);
17821846

1847+
if (pathkeys)
1848+
appendOrderByClause(&sql,root,baserel,pathkeys);
1849+
17831850
/* Get the remote estimate */
17841851
conn=GetConnection(fpinfo->server,fpinfo->user, false);
17851852
get_remote_estimate(sql.data,conn,&rows,&width,
@@ -1837,6 +1904,21 @@ estimate_path_cost_size(PlannerInfo *root,
18371904
cpu_per_tuple=cpu_tuple_cost+baserel->baserestrictcost.per_tuple;
18381905
run_cost+=cpu_per_tuple*baserel->tuples;
18391906

1907+
/*
1908+
* Without remote estimates, we have no real way to estimate the cost
1909+
* of generating sorted output. It could be free if the query plan
1910+
* the remote side would have chosen generates properly-sorted output
1911+
* anyway, but in most cases it will cost something. Estimate a value
1912+
* high enough that we won't pick the sorted path when the ordering
1913+
* isn't locally useful, but low enough that we'll err on the side of
1914+
* pushing down the ORDER BY clause when it's useful to do so.
1915+
*/
1916+
if (pathkeys!=NIL)
1917+
{
1918+
startup_cost *=DEFAULT_FDW_SORT_MULTIPLIER;
1919+
run_cost *=DEFAULT_FDW_SORT_MULTIPLIER;
1920+
}
1921+
18401922
total_cost=startup_cost+run_cost;
18411923
}
18421924

@@ -3002,3 +3084,31 @@ conversion_error_callback(void *arg)
30023084
NameStr(tupdesc->attrs[errpos->cur_attno-1]->attname),
30033085
RelationGetRelationName(errpos->rel));
30043086
}
3087+
3088+
/*
3089+
* Find an equivalence class member expression, all of whose Vars, come from
3090+
* the indicated relation.
3091+
*/
3092+
externExpr*
3093+
find_em_expr_for_rel(EquivalenceClass*ec,RelOptInfo*rel)
3094+
{
3095+
ListCell*lc_em;
3096+
3097+
foreach(lc_em,ec->ec_members)
3098+
{
3099+
EquivalenceMember*em=lfirst(lc_em);
3100+
3101+
if (bms_equal(em->em_relids,rel->relids))
3102+
{
3103+
/*
3104+
* If there is more than one equivalence member whose Vars are
3105+
* taken entirely from this relation, we'll be content to choose
3106+
* any one of those.
3107+
*/
3108+
returnem->em_expr;
3109+
}
3110+
}
3111+
3112+
/* We didn't find any suitable equivalence class expression */
3113+
returnNULL;
3114+
}

‎contrib/postgres_fdw/postgres_fdw.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -74,5 +74,8 @@ extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel);
7474
externvoiddeparseAnalyzeSql(StringInfobuf,Relationrel,
7575
List**retrieved_attrs);
7676
externvoiddeparseStringLiteral(StringInfobuf,constchar*val);
77+
externExpr*find_em_expr_for_rel(EquivalenceClass*ec,RelOptInfo*rel);
78+
externvoidappendOrderByClause(StringInfobuf,PlannerInfo*root,
79+
RelOptInfo*baserel,List*pathkeys);
7780

7881
#endif/* POSTGRES_FDW_H */

0 commit comments

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