pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Sample postgres_fdw tables remotely during ANALYZE
2+ messages / 2 participants
[nested] [flat]

* pgsql: Sample postgres_fdw tables remotely during ANALYZE
@ 2022-12-30 22:31 Tomas Vondra <tomas.vondra@postgresql.org>
  2023-01-12 11:17 ` Re: pgsql: Sample postgres_fdw tables remotely during ANALYZE Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Tomas Vondra @ 2022-12-30 22:31 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: pgsql: Sample postgres_fdw tables remotely during ANALYZE
  2022-12-30 22:31 pgsql: Sample postgres_fdw tables remotely during ANALYZE Tomas Vondra <tomas.vondra@postgresql.org>
@ 2023-01-12 11:17 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Alvaro Herrera @ 2023-01-12 11:17 UTC (permalink / raw)
  To: Tomas Vondra <tomas.vondra@postgresql.org>; +Cc: pgsql-committers@lists.postgresql.org

On 2022-Dec-30, Tomas Vondra wrote:

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

You left out the Discussion line, so here's the link:
https://postgr.es/m/151e835f-55d6-ddbc-b5b4-07ee606aba44@enterprisedb.com

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Nunca se desea ardientemente lo que solo se desea por razón" (F. Alexandre)





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2023-01-12 11:17 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-30 22:31 pgsql: Sample postgres_fdw tables remotely during ANALYZE Tomas Vondra <tomas.vondra@postgresql.org>
2023-01-12 11:17 ` Alvaro Herrera <alvherre@alvh.no-ip.org>

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