8000 Skip text->binary conversion of unnecessary columns in contrib/file_fdw. · postgrespro/postgres_cluster@a36088b · GitHub
[go: up one dir, main page]
Skip to content

Commit a36088b

Browse files
tglsfdctglsfdc
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 datatype input routines can consume a significant fraction of the runtime. Often, the query does not need all the columns, so we can get a useful speed 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 parent 76720bd commit a36088b

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
static void fileGetOptions(Oid foreigntableid,
137139
char **filename, List **other_options);
138140
static List *get_file_fdw_attribute_options(Oid relid);
141+
static bool check_selective_binary_conversion(RelOptInfo *baserel,
142+
Oid foreigntableid,
143+
List **columns);
139144
static void estimate_size(PlannerInfo *root, RelOptInfo *baserel,
140145
FileFdwPlanState *fdw_private);
141146
static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel,
@@ -457,20 +462,33 @@ fileGetForeignPaths(PlannerInfo *root,
457462
FileFdwPlanState *fdw_private = (FileFdwPlanState *) baserel->fdw_private;
458463
Cost startup_cost;
459464
Cost total_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
static void
545563
fileBeginForeignScan(ForeignScanState *node, int eflags)
546564
{
565+
ForeignScan *plan = (ForeignScan *) node->ss.ps.plan;
547566
char *filename;
548567
List *options;
549568
CopyState cstate;
@@ -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+
static bool
729+
check_selective_binary_conversion(RelOptInfo *baserel,
730+
Oid foreigntableid,
731+
List **columns)
732+
{
733+
ForeignTable *table;
734+
ListCell *lc;
735+
Relation rel;
736+
TupleDesc tupleDesc;
737+
AttrNumber attnum;
738+
Bitmapset *attrs_used = NULL;
739+
bool has_wholerow = false;
740+
int numattrs;
741+
int i;
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_attribute attr = 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_attribute attr = 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+
bool convert_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
const char *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+
else if (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
else if (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+
int attnum = 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)
0