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

Commita36088b

Browse files
committed
Skip text->binary conversion of unnecessary columns in contrib/file_fdw.
When reading from a text- or CSV-format file in file_fdw, the datatypeinput routines can consume a significant fraction of the runtime.Often, the query does not need all the columns, so we can get a usefulspeed boost by skipping I/O conversion for unnecessary columns.To support this, add a "convert_selectively" option to the core COPY code.This is undocumented and not accessible from SQL (for now, anyway).Etsuro Fujita, reviewed by KaiGai Kohei
1 parent76720bd commita36088b

File tree

2 files changed

+197
-3
lines changed

2 files changed

+197
-3
lines changed

‎contrib/file_fdw/file_fdw.c‎

Lines changed: 144 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@
1616
#include<unistd.h>
1717

1818
#include"access/reloptions.h"
19+
#include"access/sysattr.h"
1920
#include"catalog/pg_foreign_table.h"
2021
#include"commands/copy.h"
2122
#include"commands/defrem.h"
@@ -29,6 +30,7 @@
2930
#include"optimizer/pathnode.h"
3031
#include"optimizer/planmain.h"
3132
#include"optimizer/restrictinfo.h"
33+
#include"optimizer/var.h"
3234
#include"utils/memutils.h"
3335
#include"utils/rel.h"
3436

@@ -136,6 +138,9 @@ static bool is_valid_option(const char *option, Oid context);
136138
staticvoidfileGetOptions(Oidforeigntableid,
137139
char**filename,List**other_options);
138140
staticList*get_file_fdw_attribute_options(Oidrelid);
141+
staticboolcheck_selective_binary_conversion(RelOptInfo*baserel,
142+
Oidforeigntableid,
143+
List**columns);
139144
staticvoidestimate_size(PlannerInfo*root,RelOptInfo*baserel,
140145
FileFdwPlanState*fdw_private);
141146
staticvoidestimate_costs(PlannerInfo*root,RelOptInfo*baserel,
@@ -457,20 +462,33 @@ fileGetForeignPaths(PlannerInfo *root,
457462
FileFdwPlanState*fdw_private= (FileFdwPlanState*)baserel->fdw_private;
458463
Coststartup_cost;
459464
Costtotal_cost;
465+
List*columns;
466+
List*coptions=NIL;
467+
468+
/* Decide whether to selectively perform binary conversion */
469+
if (check_selective_binary_conversion(baserel,
470+
foreigntableid,
471+
&columns))
472+
coptions=list_make1(makeDefElem("convert_selectively",
473+
(Node*)columns));
460474

461475
/* Estimate costs */
462476
estimate_costs(root,baserel,fdw_private,
463477
&startup_cost,&total_cost);
464478

465-
/* Create a ForeignPath node and add it as only possible path */
479+
/*
480+
* Create a ForeignPath node and add it as only possible path. We use the
481+
* fdw_private list of the path to carry the convert_selectively option;
482+
* it will be propagated into the fdw_private list of the Plan node.
483+
*/
466484
add_path(baserel, (Path*)
467485
create_foreignscan_path(root,baserel,
468486
baserel->rows,
469487
startup_cost,
470488
total_cost,
471489
NIL,/* no pathkeys */
472490
NULL,/* no outer rel either */
473-
NIL));/* no fdw_private data */
491+
coptions));
474492

475493
/*
476494
* If data file was sorted, and we knew it somehow, we could insert
@@ -507,7 +525,7 @@ fileGetForeignPlan(PlannerInfo *root,
507525
scan_clauses,
508526
scan_relid,
509527
NIL,/* no expressions to evaluate */
510-
NIL);/* no private state either */
528+
best_path->fdw_private);
511529
}
512530

513531
/*
@@ -544,6 +562,7 @@ fileExplainForeignScan(ForeignScanState *node, ExplainState *es)
544562
staticvoid
545563
fileBeginForeignScan(ForeignScanState*node,inteflags)
546564
{
565+
ForeignScan*plan= (ForeignScan*)node->ss.ps.plan;
547566
char*filename;
548567
List*options;
549568
CopyStatecstate;
@@ -559,6 +578,9 @@ fileBeginForeignScan(ForeignScanState *node, int eflags)
559578
fileGetOptions(RelationGetRelid(node->ss.ss_currentRelation),
560579
&filename,&options);
561580

581+
/* Add any options from the plan (currently only convert_selectively) */
582+
options=list_concat(options,plan->fdw_private);
583+
562584
/*
563585
* Create CopyState from FDW options. We always acquire all columns, so
564586
* as to match the expected ScanTupleSlot signature.
@@ -694,6 +716,125 @@ fileAnalyzeForeignTable(Relation relation,
694716
return true;
695717
}
696718

719+
/*
720+
* check_selective_binary_conversion
721+
*
722+
* Check to see if it's useful to convert only a subset of the file's columns
723+
* to binary. If so, construct a list of the column names to be converted,
724+
* return that at *columns, and return TRUE. (Note that it's possible to
725+
* determine that no columns need be converted, for instance with a COUNT(*)
726+
* query. So we can't use returning a NIL list to indicate failure.)
727+
*/
728+
staticbool
729+
check_selective_binary_conversion(RelOptInfo*baserel,
730+
Oidforeigntableid,
731+
List**columns)
732+
{
733+
ForeignTable*table;
734+
ListCell*lc;
735+
Relationrel;
736+
TupleDesctupleDesc;
737+
AttrNumberattnum;
738+
Bitmapset*attrs_used=NULL;
739+
boolhas_wholerow= false;
740+
intnumattrs;
741+
inti;
742+
743+
*columns=NIL;/* default result */
744+
745+
/*
746+
* Check format of the file. If binary format, this is irrelevant.
747+
*/
748+
table=GetForeignTable(foreigntableid);
749+
foreach(lc,table->options)
750+
{
751+
DefElem*def= (DefElem*)lfirst(lc);
752+
753+
if (strcmp(def->defname,"format")==0)
754+
{
755+
char*format=defGetString(def);
756+
757+
if (strcmp(format,"binary")==0)
758+
return false;
759+
break;
760+
}
761+
}
762+
763+
/* Collect all the attributes needed for joins or final output. */
764+
pull_varattnos((Node*)baserel->reltargetlist,baserel->relid,
765+
&attrs_used);
766+
767+
/* Add all the attributes used by restriction clauses. */
768+
foreach(lc,baserel->baserestrictinfo)
769+
{
770+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
771+
772+
pull_varattnos((Node*)rinfo->clause,baserel->relid,
773+
&attrs_used);
774+
}
775+
776+
/* Convert attribute numbers to column names. */
777+
rel=heap_open(foreigntableid,AccessShareLock);
778+
tupleDesc=RelationGetDescr(rel);
779+
780+
while ((attnum=bms_first_member(attrs_used)) >=0)
781+
{
782+
/* Adjust for system attributes. */
783+
attnum+=FirstLowInvalidHeapAttributeNumber;
784+
785+
if (attnum==0)
786+
{
787+
has_wholerow= true;
788+
break;
789+
}
790+
791+
/* Ignore system attributes. */
792+
if (attnum<0)
793+
continue;
794+
795+
/* Get user attributes. */
796+
if (attnum>0)
797+
{
798+
Form_pg_attributeattr=tupleDesc->attrs[attnum-1];
799+
char*attname=NameStr(attr->attname);
800+
801+
/* Skip dropped attributes (probably shouldn't see any here). */
802+
if (attr->attisdropped)
803+
continue;
804+
*columns=lappend(*columns,makeString(pstrdup(attname)));
805+
}
806+
}
807+
808+
/* Count non-dropped user attributes while we have the tupdesc. */
809+
numattrs=0;
810+
for (i=0;i<tupleDesc->natts;i++)
811+
{
812+
Form_pg_attributeattr=tupleDesc->attrs[i];
813+
814+
if (attr->attisdropped)
815+
continue;
816+
numattrs++;
817+
}
818+
819+
heap_close(rel,AccessShareLock);
820+
821+
/* If there's a whole-row reference, fail: we need all the columns. */
822+
if (has_wholerow)
823+
{
824+
*columns=NIL;
825+
return false;
826+
}
827+
828+
/* If all the user attributes are needed, fail. */
829+
if (numattrs==list_length(*columns))
830+
{
831+
*columns=NIL;
832+
return false;
833+
}
834+
835+
return true;
836+
}
837+
697838
/*
698839
* Estimate size of a foreign table.
699840
*

‎src/backend/commands/copy.c‎

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -121,6 +121,9 @@ typedef struct CopyStateData
121121
bool*force_quote_flags;/* per-column CSV FQ flags */
122122
List*force_notnull;/* list of column names */
123123
bool*force_notnull_flags;/* per-column CSV FNN flags */
124+
boolconvert_selectively;/* do selective binary conversion? */
125+
List*convert_select;/* list of column names (can be NIL) */
126+
bool*convert_select_flags;/* per-column CSV/TEXT CS flags */
124127

125128
/* these are just for error messages, see CopyFromErrorCallback */
126129
constchar*cur_relname;/* table name for error messages */
@@ -961,6 +964,26 @@ ProcessCopyOptions(CopyState cstate,
961964
errmsg("argument to option \"%s\" must be a list of column names",
962965
defel->defname)));
963966
}
967+
elseif (strcmp(defel->defname,"convert_selectively")==0)
968+
{
969+
/*
970+
* Undocumented, not-accessible-from-SQL option: convert only
971+
* the named columns to binary form, storing the rest as NULLs.
972+
* It's allowed for the column list to be NIL.
973+
*/
974+
if (cstate->convert_selectively)
975+
ereport(ERROR,
976+
(errcode(ERRCODE_SYNTAX_ERROR),
977+
errmsg("conflicting or redundant options")));
978+
cstate->convert_selectively= true;
979+
if (defel->arg==NULL||IsA(defel->arg,List))
980+
cstate->convert_select= (List*)defel->arg;
981+
else
982+
ereport(ERROR,
983+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
984+
errmsg("argument to option \"%s\" must be a list of column names",
985+
defel->defname)));
986+
}
964987
elseif (strcmp(defel->defname,"encoding")==0)
965988
{
966989
if (cstate->file_encoding >=0)
@@ -1307,6 +1330,29 @@ BeginCopy(bool is_from,
13071330
}
13081331
}
13091332

1333+
/* Convert convert_selectively name list to per-column flags */
1334+
if (cstate->convert_selectively)
1335+
{
1336+
List*attnums;
1337+
ListCell*cur;
1338+
1339+
cstate->convert_select_flags= (bool*)palloc0(num_phys_attrs*sizeof(bool));
1340+
1341+
attnums=CopyGetAttnums(tupDesc,cstate->rel,cstate->convert_select);
1342+
1343+
foreach(cur,attnums)
1344+
{
1345+
intattnum=lfirst_int(cur);
1346+
1347+
if (!list_member_int(cstate->attnumlist,attnum))
1348+
ereport(ERROR,
1349+
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
1350+
errmsg_internal("selected column \"%s\" not referenced by COPY",
1351+
NameStr(tupDesc->attrs[attnum-1]->attname))));
1352+
cstate->convert_select_flags[attnum-1]= true;
1353+
}
1354+
}
1355+
13101356
/* Use client encoding when ENCODING option is not specified. */
13111357
if (cstate->file_encoding<0)
13121358
cstate->file_encoding=pg_get_client_encoding();
@@ -2565,6 +2611,13 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext,
25652611
NameStr(attr[m]->attname))));
25662612
string=field_strings[fieldno++];
25672613

2614+
if (cstate->convert_select_flags&&
2615+
!cstate->convert_select_flags[m])
2616+
{
2617+
/* ignore input field, leaving column as NULL */
2618+
continue;
2619+
}
2620+
25682621
if (cstate->csv_mode&&string==NULL&&
25692622
cstate->force_notnull_flags[m])
25702623
{

0 commit comments

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