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

Commitc0c6acd

Browse files
committed
Fix some planning oversights in postgres_fdw.
Include eval costs of local conditions in remote-estimate mode, and don'tassume the remote eval cost is zero in local-estimate mode. (The bestwe can do with that at the moment is to assume a seqscan, which may wellbe wildly pessimistic ... but zero won't do at all.)To get a reasonable local estimate, we need to know the relpages countfor the remote rel, so improve the ANALYZE code to fetch that ratherthan just setting the foreign table's relpages field to zero.
1 parentaf0a4c5 commitc0c6acd

File tree

3 files changed

+104
-7
lines changed

3 files changed

+104
-7
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -456,6 +456,29 @@ appendWhereClause(StringInfo buf,
456456
}
457457
}
458458

459+
/*
460+
* Construct SELECT statement to acquire size in blocks of given relation.
461+
*
462+
* Note: we use local definition of block size, not remote definition.
463+
* This is perhaps debatable.
464+
*
465+
* Note: pg_relation_size() exists in 8.1 and later.
466+
*/
467+
void
468+
deparseAnalyzeSizeSql(StringInfobuf,Relationrel)
469+
{
470+
Oidrelid=RelationGetRelid(rel);
471+
StringInfoDatarelname;
472+
473+
/* We'll need the remote relation name as a literal. */
474+
initStringInfo(&relname);
475+
deparseRelation(&relname,relid);
476+
477+
appendStringInfo(buf,"SELECT pg_catalog.pg_relation_size(");
478+
deparseStringLiteral(buf,relname.data);
479+
appendStringInfo(buf,"::pg_catalog.regclass) / %d",BLCKSZ);
480+
}
481+
459482
/*
460483
* Construct SELECT statement to acquire sample rows of given relation.
461484
*

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 80 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -259,6 +259,9 @@ postgresGetForeignRelSize(PlannerInfo *root,
259259
intwidth;
260260
Coststartup_cost;
261261
Costtotal_cost;
262+
Costrun_cost;
263+
QualCostqpqual_cost;
264+
Costcpu_per_tuple;
262265
List*remote_conds;
263266
List*param_conds;
264267
List*local_conds;
@@ -349,6 +352,16 @@ postgresGetForeignRelSize(PlannerInfo *root,
349352
sel *=clauselist_selectivity(root,local_conds,
350353
baserel->relid,JOIN_INNER,NULL);
351354

355+
/*
356+
* Add in the eval cost of those conditions, too.
357+
*/
358+
cost_qual_eval(&qpqual_cost,param_conds,root);
359+
startup_cost+=qpqual_cost.startup;
360+
total_cost+=qpqual_cost.per_tuple*rows;
361+
cost_qual_eval(&qpqual_cost,local_conds,root);
362+
startup_cost+=qpqual_cost.startup;
363+
total_cost+=qpqual_cost.per_tuple*rows;
364+
352365
/* Report estimated numbers to planner. */
353366
baserel->rows=clamp_row_est(rows*sel);
354367
baserel->width=width;
@@ -367,18 +380,25 @@ postgresGetForeignRelSize(PlannerInfo *root,
367380
* estimate of 10 pages, and divide by the column-datatype-based width
368381
* estimate to get the corresponding number of tuples.
369382
*/
370-
if (baserel->tuples <=0)
383+
if (baserel->pages==0&&baserel->tuples==0)
384+
{
385+
baserel->pages=10;
371386
baserel->tuples=
372387
(10*BLCKSZ) / (baserel->width+sizeof(HeapTupleHeaderData));
388+
}
373389

374390
set_baserel_size_estimates(root,baserel);
375391

376-
/*
377-
* XXX need to do something here to calculate sane startup and total
378-
* cost estimates ... for the moment, we do this:
379-
*/
392+
/* Cost as though this were a seqscan, which is pessimistic. */
380393
startup_cost=0;
381-
total_cost=baserel->rows*cpu_tuple_cost;
394+
run_cost=0;
395+
run_cost+=seq_page_cost*baserel->pages;
396+
397+
startup_cost+=baserel->baserestrictcost.startup;
398+
cpu_per_tuple=cpu_tuple_cost+baserel->baserestrictcost.per_tuple;
399+
run_cost+=cpu_per_tuple*baserel->tuples;
400+
401+
total_cost=startup_cost+run_cost;
382402
}
383403

384404
/*
@@ -1068,9 +1088,62 @@ postgresAnalyzeForeignTable(Relation relation,
10681088
AcquireSampleRowsFunc*func,
10691089
BlockNumber*totalpages)
10701090
{
1071-
*totalpages=0;/* XXX this is probably a bad idea */
1091+
ForeignTable*table;
1092+
ForeignServer*server;
1093+
UserMapping*user;
1094+
PGconn*conn;
1095+
StringInfoDatasql;
1096+
PGresult*volatileres=NULL;
1097+
1098+
/* Return the row-analysis function pointer */
10721099
*func=postgresAcquireSampleRowsFunc;
10731100

1101+
/*
1102+
* Now we have to get the number of pages. It's annoying that the ANALYZE
1103+
* API requires us to return that now, because it forces some duplication
1104+
* of effort between this routine and postgresAcquireSampleRowsFunc. But
1105+
* it's probably not worth redefining that API at this point.
1106+
*/
1107+
1108+
/*
1109+
* Get the connection to use. We do the remote access as the table's
1110+
* owner, even if the ANALYZE was started by some other user.
1111+
*/
1112+
table=GetForeignTable(RelationGetRelid(relation));
1113+
server=GetForeignServer(table->serverid);
1114+
user=GetUserMapping(relation->rd_rel->relowner,server->serverid);
1115+
conn=GetConnection(server,user);
1116+
1117+
/*
1118+
* Construct command to get page count for relation.
1119+
*/
1120+
initStringInfo(&sql);
1121+
deparseAnalyzeSizeSql(&sql,relation);
1122+
1123+
/* In what follows, do not risk leaking any PGresults. */
1124+
PG_TRY();
1125+
{
1126+
res=PQexec(conn,sql.data);
1127+
if (PQresultStatus(res)!=PGRES_TUPLES_OK)
1128+
pgfdw_report_error(ERROR,res, false,sql.data);
1129+
1130+
if (PQntuples(res)!=1||PQnfields(res)!=1)
1131+
elog(ERROR,"unexpected result from deparseAnalyzeSizeSql query");
1132+
*totalpages=strtoul(PQgetvalue(res,0,0),NULL,10);
1133+
1134+
PQclear(res);
1135+
res=NULL;
1136+
}
1137+
PG_CATCH();
1138+
{
1139+
if (res)
1140+
PQclear(res);
1141+
PG_RE_THROW();
1142+
}
1143+
PG_END_TRY();
1144+
1145+
ReleaseConnection(conn);
1146+
10741147
return true;
10751148
}
10761149

‎contrib/postgres_fdw/postgres_fdw.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@ extern void appendWhereClause(StringInfo buf,
4747
boolhas_where,
4848
List*exprs,
4949
PlannerInfo*root);
50+
externvoiddeparseAnalyzeSizeSql(StringInfobuf,Relationrel);
5051
externvoiddeparseAnalyzeSql(StringInfobuf,Relationrel);
5152

5253
#endif/* POSTGRES_FDW_H */

0 commit comments

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