public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Tom Lane <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Fujii Masao <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: postgres_fdw: using TABLESAMPLE to collect remote sample
Date: Sat, 8 Oct 2022 01:43:56 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CA+TgmoYx596-mqUP=xf0DdQ97UERxXfzhR0Otpa411sGV1vKow@mail.gmail.com>
<[email protected]>
<CA+TgmobqyX6s2VQ0=HGZc4xW+tOusPa0FLfSevH_PA2Kum34BA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
Hi!
Here's an updated patch, including all the changes proposed by Tom in
his review. There were two more points left [1], namely:
1) The useless test added to postgres_fdw.sql consuming a lot of time.
I decided to just rip this out and replace it with a much simpler test,
that simply changes the sampling method and does ANALYZE. Ideally we'd
also verify we actually generated the right SQL query to be executed on
the remote server, but there doesn't seem to be a good way to do that
(e.g. we can't do EXPLAIN to show the "Remote SQL" on the "ANALYZE").
So I guess just changing the method and running ANALYZE will have to be
enough for now (coverage report should at least tell us we got to all
the SQL variants).
2) the logic disabling sampling when the fraction gets too high
I based the condition on the absolute number of "unsampled" rows, Tom
suggested maybe it should be just a simple condition on sample_frac
(e.g. 95%). But neither of us was sure what would be a good value.
So I did a couple tests to get at least some idea what would be a good
threshold, and it turns out the threshold is mostly useless. I'll
discuss the measurements I did in a bit (some of the findings are a bit
amusing or even hilarious, actually), but the main reasons are:
- The sampling overhead is negligible (compared to transferring the
data, even on localhost), and the behavior is very smooth. So almost
never exceed reading everything, unless sample_frac >= 0.99 and even
there it's a tiny difference. So there's almost no risk, I think.
- For small tables it doesn't really matter. It's going to be fast
anyway, and the difference between reading 10000 or 11000 rows is going
to be just noise. Who cares if this takes 10 or 11 ms ...
- For large tables, we'll never even get to these high sample_frac
values. Imagine a table with 10M rows - the highers stats target we
allow is 10k, and the sample size is 300 * target, so 3M rows. It
doesn't matter if the condition is 0.95 or 0.99, because for this table
we'll never ask for a sample above 30%.
- For the tables in between it might be more relevant, but the simple
truth is that reading the row and sampling it remotely is way cheaper
than the network transfer, even if on localhost. The data suggest that
reading+sampling a row costs ~0.2us at most, but sending it is ~1.5us
(localhost) or ~5.5us (local network).
So I just removed the threshold from the patch, and we'll request
sampling even with sample_frac=100% (if that happens).
sampling test
-------------
I did a simple test to collect some data - create a table, and sample
various fractions in the ways discussed in this patch - either locally
or through a FDW.
This required a bit of care to ensure the sampling happens in the right
place (and e.g. we don't push the random() or tablesample down), which I
did by pointing the FDW table not to a remote table, but to a view with
an optimization fence (OFFSET 0). See the run-local.sh script.
The script populates the table with different numbers of rows, samples
different fractions of it, etc. I also did this from a different
machine, to see what a bit more network latency would do (that's what
run-remote.sh is for).
results (fdw-sampling-test.pdf)
-------------------------------
The attached PDF shows the results - first page is for the foreign table
in the same instance (i.e. localhost, latency ~0.01ms), second page is
for FDW pointing to a machine in the same network (latency ~0.1ms).
Left column is always the table "directly", right column is through the
FDW. On the x-axis is the fraction of the table we sample, y-axis is
duration in milliseconds. "full" means "reading everything" (i.e. what
the FDW does now), the other options should be clear I think.
The first two dataset sizes (10k and 10M rows) are tiny (10M is ~2GB),
and fit into RAM, which is 8GB. The 100M is ~21GB, so much larger.
In the "direct" (non-FDW) sampling, the various sampling methods start
losing to seqscan fairly soon - "random" is consistently slower,
"bernoulli" starts losing at ~30%, "system" as ~80%. This is not very
surprising, particularly for bernoulli/random which actually read all
the rows anyway. But the overhead is pretty limited to ~30% on top of
the seqscan.
But in the "FDW" sampling (right column), it's entirely different story,
and all the methods clearly win over just transferring everything and
only then doing the sampling.
Who cares if the remote sampling means means we have to pay 0.2us
instead of 0.15us (per row), when the transfer costs 1.5us per row?
The 100M case shows an interesting behavior for the "system" method,
which quickly spikes to ~2x of the "full" method when sampling ~20% of
the table, and then gradually improves again.
My explanation is that this is due to "system" making the I/O patter
increasingly more random, because it jumps blocks in a way that makes
readahead impossible. And then as the fraction increases, it becomes
more sequential again.
All the other methods are pretty much equal to just scanning everything
sequentially, and sampling rows one by one.
The "system" method in TABLESAMPLE would probably benefit from explicit
prefetching, I guess. For ANALYZE this probably is not a huge, as we'll
never sample this large fraction for large tables (for 100M rows we peak
at ~3% with target 10000, which is way before the peak). And for smaller
tables we're more likely to hit cache (which is why the smaller data
sets don't have this issue). But for explicit TABLESAMPLE queries that
may not be the case.
Although, ANALYZE uses something like "system" to sample rows too, no?
However, even this is not an issue for the FDW case - in that case it
still clearly wins over the current "local sample" approach, because
transferring the data is so expensive which makes the "peak" into a tiny
hump.
The second page (different machine, local network) tells the same story,
except that the differences are even clearer.
ANALYZE test
------------
So I changed the patch, and did a similar test by running ANALYZE either
on the local or foreign table, using the different sampling methods.
This does not require the hacks to prevent pushdown etc. but it also
means we can't determine sample_frac directly, only through statistics
target (which is capped to 10k).
In this case, "local" means ANALYZE on the local table (which you might
think of as the "baseline"), and "off" means reading all data without
remote sampling.
For the two smaller data sets (10k and 10M rows), the benefits are
pretty obvious. We're very close to the "local" results, because we save
a lot on copying only some of the rows. For 10M we only get to ~30%
before we hit target=10k, which is we don't see it get closer to "off".
But now we get to the *hilarious* thing - if you look at the 10M result,
you may notice that *all* the sampling methods beat ANALYZE on the local
table.
For "system" (which wins from the very beginning) we might make some
argument that the algorithm is simpler than what ANALYZE does, skips
blocks differently, etc. - perhaps ...
But bernoulli/random are pretty much ideal sampling, reading/sampling
all rows. And yet both methods start winning after crossing ~1% on this
tables. In other words, it's about 3x faster to ANALYZE a table through
FDW than directly ;-)
Anyway, those issues have impact on this patch, I think. I believe the
results show what the patch does is reasonable.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[application/x-shellscript] run-remote.sh (3.0K, ../[email protected]/2-run-remote.sh)
download
[application/x-shellscript] run-local.sh (2.4K, ../[email protected]/3-run-local.sh)
download
[application/x-shellscript] run-patches.sh (2.7K, ../[email protected]/4-run-patches.sh)
download
[application/pdf] fdw-sampling-test.pdf (856.8K, ../[email protected]/5-fdw-sampling-test.pdf)
download
[text/x-patch] 0001-Sample-postgres_fdw-tables-remotely-during--20221008.patch (20.1K, ../[email protected]/6-0001-Sample-postgres_fdw-tables-remotely-during--20221008.patch)
download | inline diff:
From 11f1329cb8840ca664183ed170c446c600e8f38a Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 5 Oct 2022 22:14:32 +0200
Subject: [PATCH] Sample postgres_fdw tables remotely during ANALYZE
When collecting ANALYZE sample on foreign tables, postgres_fdw fetched
all rows and performed the sampling locally. For large tables this means
transferring and immediately discarding large amounts of data.
This commit allows the sampling to be performed on the remote server,
transferring only the much smaller sample. The sampling is performed
using the built-in TABLESAMPLE methods (system, bernoulli) or random()
function, depending on the remote server version.
Remote sampling can be enabled by analyze_sampling on the foreign server
and/or foreign table, with supported values 'off', 'auto', 'system',
'bernoulli' and 'random'. The default value is 'auto' which uses either
'bernoulli' (TABLESAMPLE method) or 'random' (for remote servers without
TABLESAMPLE support).
---
contrib/postgres_fdw/deparse.c | 72 ++++++-
.../postgres_fdw/expected/postgres_fdw.out | 25 +++
contrib/postgres_fdw/option.c | 21 ++
contrib/postgres_fdw/postgres_fdw.c | 189 +++++++++++++++++-
contrib/postgres_fdw/postgres_fdw.h | 15 ++
contrib/postgres_fdw/sql/postgres_fdw.sql | 36 ++++
doc/src/sgml/postgres-fdw.sgml | 35 ++++
7 files changed, 384 insertions(+), 9 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 9524765650..dfdf78d11e 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -2367,14 +2367,57 @@ deparseAnalyzeSizeSql(StringInfo buf, Relation rel)
appendStringInfo(buf, "::pg_catalog.regclass) / %d", BLCKSZ);
}
+/*
+ * Construct SELECT statement to acquire the number of rows of a relation.
+ *
+ * Note: we just return the remote server's reltuples value, which might
+ * be off a good deal, but it doesn't seem worth working harder. See
+ * comments in postgresAcquireSampleRowsFunc.
+ */
+void
+deparseAnalyzeTuplesSql(StringInfo buf, Relation rel)
+{
+ StringInfoData relname;
+
+ /* We'll need the remote relation name as a literal. */
+ initStringInfo(&relname);
+ deparseRelation(&relname, rel);
+
+ appendStringInfoString(buf, "SELECT reltuples FROM pg_catalog.pg_class WHERE oid = ");
+ deparseStringLiteral(buf, relname.data);
+ appendStringInfoString(buf, "::pg_catalog.regclass");
+}
+
/*
* Construct SELECT statement to acquire sample rows of given relation.
*
* SELECT command is appended to buf, and list of columns retrieved
* is returned to *retrieved_attrs.
+ *
+ * We only support sampling methods we can decide based on server version.
+ * Allowing custom TSM modules (like tsm_system_rows) might be useful, but it
+ * would require detecting which extensions are installed, to allow automatic
+ * fall-back. Moreover, the methods may use different parameters like number
+ * of rows (and not sampling rate). So we leave this for future improvements.
+ *
+ * Using random() to sample rows on the remote server has the advantage that
+ * this works on all PostgreSQL versions (unlike TABLESAMPLE), and that it
+ * does the sampling on the remote side (without transferring everything and
+ * then discarding most rows).
+ *
+ * The disadvantage is that we still have to read all rows and evaluate the
+ * random(), while TABLESAMPLE (at least with the "system" method) may skip.
+ * It's not that different from the "bernoulli" method, though.
+ *
+ * We could also do "ORDER BY random() LIMIT x", which would always pick
+ * the expected number of rows, but it requires sorting so it may be much
+ * more expensive (particularly on large tables, which is what what the
+ * remote sampling is meant to improve).
*/
void
-deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs)
+deparseAnalyzeSql(StringInfo buf, Relation rel,
+ PgFdwSamplingMethod sample_method, double sample_frac,
+ List **retrieved_attrs)
{
Oid relid = RelationGetRelid(rel);
TupleDesc tupdesc = RelationGetDescr(rel);
@@ -2422,10 +2465,35 @@ deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs)
appendStringInfoString(buf, "NULL");
/*
- * Construct FROM clause
+ * Construct FROM clause, and perhaps WHERE clause too, depending on the
+ * selected sampling method.
*/
appendStringInfoString(buf, " FROM ");
deparseRelation(buf, rel);
+
+ switch (sample_method)
+ {
+ case ANALYZE_SAMPLE_OFF:
+ /* nothing to do here */
+ break;
+
+ case ANALYZE_SAMPLE_RANDOM:
+ appendStringInfo(buf, " WHERE pg_catalog.random() < %f", sample_frac);
+ break;
+
+ case ANALYZE_SAMPLE_SYSTEM:
+ appendStringInfo(buf, " TABLESAMPLE SYSTEM(%f)", (100.0 * sample_frac));
+ break;
+
+ case ANALYZE_SAMPLE_BERNOULLI:
+ appendStringInfo(buf, " TABLESAMPLE BERNOULLI(%f)", (100.0 * sample_frac));
+ break;
+
+ case ANALYZE_SAMPLE_AUTO:
+ /* should have been resolved into actual method */
+ elog(ERROR, "unexpected sampling method");
+ break;
+ }
}
/*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index cc9e39c4a5..360b176cdc 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11466,3 +11466,28 @@ SELECT * FROM prem2;
ALTER SERVER loopback OPTIONS (DROP parallel_commit);
ALTER SERVER loopback2 OPTIONS (DROP parallel_commit);
+-- ===================================================================
+-- test for ANALYZE sampling
+-- ===================================================================
+CREATE TABLE analyze_table (id int, a text, b bigint);
+CREATE FOREIGN TABLE analyze_ftable (id int, a text, b bigint)
+ SERVER loopback OPTIONS (table_name 'analyze_rtable1');
+INSERT INTO analyze_table (SELECT x FROM generate_series(1,1000) x);
+ANALYZE analyze_table;
+SET default_statistics_target = 10;
+ANALYZE analyze_table;
+ALTER SERVER loopback OPTIONS (analyze_sampling 'invalid');
+ERROR: invalid value for string option "analyze_sampling": invalid
+ALTER SERVER loopback OPTIONS (analyze_sampling 'auto');
+ANALYZE analyze_table;
+ALTER SERVER loopback OPTIONS (SET analyze_sampling 'system');
+ANALYZE analyze_table;
+ALTER SERVER loopback OPTIONS (SET analyze_sampling 'bernoulli');
+ANALYZE analyze_table;
+ALTER SERVER loopback OPTIONS (SET analyze_sampling 'random');
+ANALYZE analyze_table;
+ALTER SERVER loopback OPTIONS (SET analyze_sampling 'off');
+ANALYZE analyze_table;
+-- cleanup
+DROP FOREIGN TABLE analyze_ftable;
+DROP TABLE analyze_table;
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index fa80ee2a55..b5890c92a1 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -210,6 +210,23 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
errmsg("sslcert and sslkey are superuser-only"),
errhint("User mappings with the sslcert or sslkey options set may only be created or modified by the superuser.")));
}
+ else if (strcmp(def->defname, "analyze_sampling") == 0)
+ {
+ char *value;
+
+ value = defGetString(def);
+
+ /* we recognize off/auto/random/system/bernoulli */
+ if (strcmp(value, "off") != 0 &&
+ strcmp(value, "auto") != 0 &&
+ strcmp(value, "random") != 0 &&
+ strcmp(value, "system") != 0 &&
+ strcmp(value, "bernoulli") != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid value for string option \"%s\": %s",
+ def->defname, value)));
+ }
}
PG_RETURN_VOID();
@@ -257,6 +274,10 @@ InitPgFdwOptions(void)
{"keep_connections", ForeignServerRelationId, false},
{"password_required", UserMappingRelationId, false},
+ /* sampling is available on both server and table */
+ {"analyze_sampling", ForeignServerRelationId, false},
+ {"analyze_sampling", ForeignTableRelationId, false},
+
/*
* sslcert and sslkey are in fact libpq options, but we repeat them
* here to allow them to appear in both foreign server context (when
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 8d013f5b1a..e45008915f 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -4969,11 +4969,60 @@ postgresAnalyzeForeignTable(Relation relation,
return true;
}
+/*
+ * postgresCountTuplesForForeignTable
+ * Count tuples in foreign table (just get pg_class.reltuples).
+ */
+static double
+postgresCountTuplesForForeignTable(Relation relation)
+{
+ ForeignTable *table;
+ UserMapping *user;
+ PGconn *conn;
+ StringInfoData sql;
+ PGresult *volatile res = NULL;
+ volatile double reltuples = -1;
+
+ /*
+ * Get the connection to use. We do the remote access as the table's
+ * owner, even if the ANALYZE was started by some other user.
+ */
+ table = GetForeignTable(RelationGetRelid(relation));
+ user = GetUserMapping(relation->rd_rel->relowner, table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ /*
+ * Construct command to get page count for relation.
+ */
+ initStringInfo(&sql);
+ deparseAnalyzeTuplesSql(&sql, relation);
+
+ /* In what follows, do not risk leaking any PGresults. */
+ PG_TRY();
+ {
+ res = pgfdw_exec_query(conn, sql.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(ERROR, res, conn, false, sql.data);
+
+ if (PQntuples(res) != 1 || PQnfields(res) != 1)
+ elog(ERROR, "unexpected result from deparseAnalyzeTuplesSql query");
+ reltuples = strtod(PQgetvalue(res, 0, 0), NULL);
+ }
+ PG_FINALLY();
+ {
+ if (res)
+ PQclear(res);
+ }
+ PG_END_TRY();
+
+ ReleaseConnection(conn);
+
+ return reltuples;
+}
+
/*
* Acquire a random sample of rows from foreign table managed by postgres_fdw.
*
- * We fetch the whole table from the remote side and pick out some sample rows.
- *
* Selected rows are returned in the caller-allocated array rows[],
* which must have at least targrows entries.
* The actual number of rows selected is returned as the function result.
@@ -4996,9 +5045,14 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
ForeignServer *server;
UserMapping *user;
PGconn *conn;
+ int server_version_num;
+ PgFdwSamplingMethod method = ANALYZE_SAMPLE_AUTO; /* auto is default */
+ double sample_frac = -1.0;
+ double reltuples;
unsigned int cursor_number;
StringInfoData sql;
PGresult *volatile res = NULL;
+ ListCell *lc;
/* Initialize workspace state */
astate.rel = relation;
@@ -5026,20 +5080,134 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
user = GetUserMapping(relation->rd_rel->relowner, table->serverid);
conn = GetConnection(user, false, NULL);
+ /* We'll need server version, so fetch it now. */
+ server_version_num = PQserverVersion(conn);
+
+ /*
+ * What sampling method should we use?
+ */
+ foreach(lc, server->options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "analyze_sampling") == 0)
+ {
+ char *value = defGetString(def);
+
+ if (strcmp(value, "off") == 0)
+ method = ANALYZE_SAMPLE_OFF;
+ else if (strcmp(value, "auto") == 0)
+ method = ANALYZE_SAMPLE_AUTO;
+ else if (strcmp(value, "random") == 0)
+ method = ANALYZE_SAMPLE_RANDOM;
+ else if (strcmp(value, "system") == 0)
+ method = ANALYZE_SAMPLE_SYSTEM;
+ else if (strcmp(value, "bernoulli") == 0)
+ method = ANALYZE_SAMPLE_BERNOULLI;
+
+ break;
+ }
+ }
+
+ foreach(lc, table->options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "analyze_sampling") == 0)
+ {
+ char *value = defGetString(def);
+
+ if (strcmp(value, "off") == 0)
+ method = ANALYZE_SAMPLE_OFF;
+ else if (strcmp(value, "auto") == 0)
+ method = ANALYZE_SAMPLE_AUTO;
+ else if (strcmp(value, "random") == 0)
+ method = ANALYZE_SAMPLE_RANDOM;
+ else if (strcmp(value, "system") == 0)
+ method = ANALYZE_SAMPLE_SYSTEM;
+ else if (strcmp(value, "bernoulli") == 0)
+ method = ANALYZE_SAMPLE_BERNOULLI;
+
+ break;
+ }
+ }
+
+ /*
+ * Error-out if explicitly required one of the TABLESAMPLE methods, but
+ * the server does not support it.
+ */
+ if ((server_version_num < 95000) &&
+ (method == ANALYZE_SAMPLE_SYSTEM ||
+ method == ANALYZE_SAMPLE_BERNOULLI))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("remote server does not support TABLESAMPLE feature")));
+
+ /*
+ * For "auto" method, pick the one we believe is best. For servers with
+ * TABLESAMPLE support we pick BERNOULLI, for old servers we fall-back to
+ * random() to at least reduce network transfer.
+ */
+ if (method == ANALYZE_SAMPLE_AUTO)
+ {
+ if (server_version_num < 95000)
+ method = ANALYZE_SAMPLE_RANDOM;
+ else
+ method = ANALYZE_SAMPLE_BERNOULLI;
+ }
+
+ /*
+ * If we've decided to do remote sampling, calculate the sampling rate. We
+ * need to get the number of tuples from the remote server, but skip that
+ * network round-trip if not needed.
+ */
+ if (method != ANALYZE_SAMPLE_OFF)
+ {
+ reltuples = postgresCountTuplesForForeignTable(relation);
+
+ /*
+ * Remote's reltuples could be 0 or -1 if the table has never been
+ * vacuumed/analyzed. In that case, disable sampling after all.
+ */
+ if ((reltuples <= 0) || (targrows >= reltuples))
+ method = ANALYZE_SAMPLE_OFF;
+ else
+ {
+ sample_frac = targrows / reltuples;
+
+ /*
+ * Let's sample a bit more (10%) to compensate for any small
+ * inaccuracy in the reltuples value. We'll reduce the sample
+ * locally if it's too large. Of course, this won't fix a big
+ * error in reltuples --- but big errors in it are most commonly
+ * underestimates due to the table having grown since the last
+ * ANALYZE. That will lead to overestimating the required
+ * sample_frac, which is fine.
+ */
+ sample_frac *= 1.1;
+
+ /*
+ * Ensure the sampling rate is between 0.0 and 1.0, even after the
+ * 10% adjustment above. (Clamping to 0.0 is just paranoia.)
+ */
+ sample_frac = Min(1.0, Max(0.0, sample_frac));
+ }
+ }
+
/*
* Construct cursor that retrieves whole rows from remote.
*/
cursor_number = GetCursorNumber(conn);
initStringInfo(&sql);
appendStringInfo(&sql, "DECLARE c%u CURSOR FOR ", cursor_number);
- deparseAnalyzeSql(&sql, relation, &astate.retrieved_attrs);
+
+ deparseAnalyzeSql(&sql, relation, method, sample_frac, &astate.retrieved_attrs);
/* In what follows, do not risk leaking any PGresults. */
PG_TRY();
{
char fetch_sql[64];
int fetch_size;
- ListCell *lc;
res = pgfdw_exec_query(conn, sql.data, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
@@ -5126,8 +5294,15 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
/* We assume that we have no dead tuple. */
*totaldeadrows = 0.0;
- /* We've retrieved all living tuples from foreign server. */
- *totalrows = astate.samplerows;
+ /*
+ * Without sampling, we've retrieved all living tuples from foreign
+ * server, so report that as totalrows. Otherwise use the reltuples
+ * estimate we got from the remote side.
+ */
+ if (method == ANALYZE_SAMPLE_OFF)
+ *totalrows = astate.samplerows;
+ else
+ *totalrows = reltuples;
/*
* Emit some interesting relation info
@@ -5135,7 +5310,7 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
ereport(elevel,
(errmsg("\"%s\": table contains %.0f rows, %d rows in sample",
RelationGetRelationName(relation),
- astate.samplerows, astate.numrows)));
+ *totalrows, astate.numrows)));
return astate.numrows;
}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index a11d45bedf..9074fa3052 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -134,6 +134,18 @@ typedef struct PgFdwConnState
AsyncRequest *pendingAreq; /* pending async request */
} PgFdwConnState;
+/*
+ * Method used by ANALYZE to sample remote rows.
+ */
+typedef enum PgFdwSamplingMethod
+{
+ ANALYZE_SAMPLE_OFF, /* no remote sampling */
+ ANALYZE_SAMPLE_AUTO, /* choose by server version */
+ ANALYZE_SAMPLE_RANDOM, /* remote random() */
+ ANALYZE_SAMPLE_SYSTEM, /* TABLESAMPLE system */
+ ANALYZE_SAMPLE_BERNOULLI /* TABLESAMPLE bernoulli */
+} PgFdwSamplingMethod;
+
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
@@ -211,7 +223,10 @@ extern void deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
List *returningList,
List **retrieved_attrs);
extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel);
+extern void deparseAnalyzeTuplesSql(StringInfo buf, Relation rel);
extern void deparseAnalyzeSql(StringInfo buf, Relation rel,
+ PgFdwSamplingMethod sample_method,
+ double sample_frac,
List **retrieved_attrs);
extern void deparseTruncateSql(StringInfo buf,
List *rels,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index e48ccd286b..0b28998996 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3682,3 +3682,39 @@ SELECT * FROM prem2;
ALTER SERVER loopback OPTIONS (DROP parallel_commit);
ALTER SERVER loopback2 OPTIONS (DROP parallel_commit);
+
+-- ===================================================================
+-- test for ANALYZE sampling
+-- ===================================================================
+
+CREATE TABLE analyze_table (id int, a text, b bigint);
+
+CREATE FOREIGN TABLE analyze_ftable (id int, a text, b bigint)
+ SERVER loopback OPTIONS (table_name 'analyze_rtable1');
+
+INSERT INTO analyze_table (SELECT x FROM generate_series(1,1000) x);
+ANALYZE analyze_table;
+
+SET default_statistics_target = 10;
+ANALYZE analyze_table;
+
+ALTER SERVER loopback OPTIONS (analyze_sampling 'invalid');
+
+ALTER SERVER loopback OPTIONS (analyze_sampling 'auto');
+ANALYZE analyze_table;
+
+ALTER SERVER loopback OPTIONS (SET analyze_sampling 'system');
+ANALYZE analyze_table;
+
+ALTER SERVER loopback OPTIONS (SET analyze_sampling 'bernoulli');
+ANALYZE analyze_table;
+
+ALTER SERVER loopback OPTIONS (SET analyze_sampling 'random');
+ANALYZE analyze_table;
+
+ALTER SERVER loopback OPTIONS (SET analyze_sampling 'off');
+ANALYZE analyze_table;
+
+-- cleanup
+DROP FOREIGN TABLE analyze_ftable;
+DROP TABLE analyze_table;
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index bfd344cdc0..d681d1675e 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -326,6 +326,41 @@ OPTIONS (ADD password_required 'false');
frequently updated, the local statistics will soon be obsolete.
</para>
+ <para>
+ The following option controls how such an <command>ANALYZE</command>
+ operation behaves:
+ </para>
+
+ <variablelist>
+
+ <varlistentry>
+ <term><literal>analyze_sampling</literal> (<type>text</type>)</term>
+ <listitem>
+ <para>
+ This option, which can be specified for a foreign table or a foreign
+ server, determines if <command>ANALYZE</command> on a foreign table
+ samples the data on the remote side, or reads and transfers all data
+ and performs the sampling locally. The supported values
+ are <literal>off</literal>, <literal>random</literal>,
+ <literal>system</literal>, <literal>bernoulli</literal>
+ and <literal>auto</literal>. <literal>off</literal> disables remote
+ sampling, so all data are transferred and sampled locally.
+ <literal>random</literal> performs remote sampling using the
+ <literal>random()</literal> function to choose returned rows,
+ while <literal>system</literal> and <literal>bernoulli</literal> rely
+ on the built-in <literal>TABLESAMPLE</literal> methods of those
+ names. <literal>random</literal> works on all remote server versions,
+ while <literal>TABLESAMPLE</literal> is supported only since 9.5.
+ <literal>auto</literal> (the default) picks the recommended sampling
+ method automatically; currently it means
+ either <literal>bernoulli</literal> or <literal>random</literal>
+ depending on the remote server version.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+
</sect3>
<sect3>
--
2.37.3
view thread (29+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: postgres_fdw: using TABLESAMPLE to collect remote sample
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox