pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Sample postgres_fdw tables remotely during ANALYZE
Date: Fri, 30 Dec 2022 22:31:51 +0000
Message-ID: <E1pBNuo-001DDN-O2@gemulon.postgresql.org> (raw)

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).

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/8ad51b5f446b5c19ba2c0033a0f7b3180b3b6d95

Modified Files
--------------
contrib/postgres_fdw/deparse.c                 |  72 ++++++++-
contrib/postgres_fdw/expected/postgres_fdw.out |  25 +++
contrib/postgres_fdw/option.c                  |  21 +++
contrib/postgres_fdw/postgres_fdw.c            | 202 ++++++++++++++++++++++++-
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, 397 insertions(+), 9 deletions(-)



view thread (2+ messages)  latest in thread

Message-ID: <E1pBNuo-001DDN-O2@gemulon.postgresql.org>
Permalink:  ../E1pBNuo-001DDN-O2@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1pBNuo-001DDN-O2@gemulon.postgresql.org

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: tomas.vondra@postgresql.org
  Subject: Re: pgsql: Sample postgres_fdw tables remotely during ANALYZE
  In-Reply-To: <E1pBNuo-001DDN-O2@gemulon.postgresql.org>
  References: <E1pBNuo-001DDN-O2@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox