pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedanalyze foreign tables
12+ messages / 4 participants
[nested] [flat]
* analyze foreign tables
@ 2023-08-01 13:47 richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 16:16 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-03 12:37 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-03 14:19 ` Re: analyze foreign tables Jeff Janes <jeff.janes@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: richard coleman @ 2023-08-01 13:47 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello all,
In PostgreSQL foreign tables are not automatically analyzed and analyze
must be specifically called on each table. In the case of sharing tables
between PostgreSQL clusters, there is the use_remote_estimate foreign
server option.
In some of our multi terabyte databases, manually running analyze on all of
the foreign tables can take more than a day. This is per database
containing the foreign schema. Since we have certain large schema that we
have centrally located and share to all of our other database clusters,
this really adds up.
use_remote_estimate isn't really a solution as it adds way too much
overhead and processing time to every query run.
Since these tables are being continuously analyzed in the database that
hosts the data, is there some way that they statistics could be easily
passed through the foreign server mechanism to the remote database that's
calling the query?
Unless I'm missing something we can either:
1. manually run analyze on each foreign table in each database that points
to the host table
2. set use_remote_estimate = true which will cause PostgreSQL to re-obtain
statistics on a per query basis.
What I am hoping for is either:
1. pass through the results of analyze from the source database to the one
where the foreign query is being run
2. add the ability to automatically run analyze on foreign tables just as
they are currently run on local tables.
Of the two, #1 would seem to be the easiest and least wasteful of resources.
Thanks,
rik.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-08-01 16:16 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-01 16:36 ` Re: analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2023-08-01 16:16 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> In PostgreSQL foreign tables are not automatically analyzed and analyze must be
> specifically called on each table. In the case of sharing tables between
> PostgreSQL clusters, there is the use_remote_estimate foreign server option.
>
> In some of our multi terabyte databases, manually running analyze on all of
> the foreign tables can take more than a day. This is per database containing
> the foreign schema. Since we have certain large schema that we have centrally
> located and share to all of our other database clusters, this really adds up.
>
> use_remote_estimate isn't really a solution as it adds way too much overhead
> and processing time to every query run.
>
> Since these tables are being continuously analyzed in the database that hosts
> the data, is there some way that they statistics could be easily passed through
> the foreign server mechanism to the remote database that's calling the query?
>
> Unless I'm missing something we can either:
> 1. manually run analyze on each foreign table in each database that points to
> the host table
> 2. set use_remote_estimate = true which will cause PostgreSQL to re-obtain
> statistics on a per query basis.
>
> What I am hoping for is either:
> 1. pass through the results of analyze from the source database to the one
> where the foreign query is being run
> 2. add the ability to automatically run analyze on foreign tables just as they
> are currently run on local tables.
>
> Of the two, #1 would seem to be the easiest and least wasteful of resources.
Unfortunately, both your wishes don't look feasible:
- Transferring table statistics would mean that PostgreSQL understands statistics
from other server versions. This is complicated, and we have decided not to
do this for pg_upgrade, so I don't think we'll try to do it here.
- Autoanalyzing foreign tables would mean that we have some idea how much data
has changed on the remote server. How should we do that?
What I can imagine is that instead of reading the complete remote table during
ANALYZE, PostgreSQL applies TABLESAMPLE to fetch only part. That could be a
workable enhancement.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 16:16 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-08-01 16:36 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 17:51 ` analyze foreign tables Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-08-01 19:15 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 12+ messages in thread
From: richard coleman @ 2023-08-01 16:36 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Laurenz,
Thanks for taking the time to respond.
Right now I'm stuck with cronning a script to manually run analyze on every
foreign table in every database, which in our case is most of them.
Would it be possible to transfer table statistics between the same version
of PostgreSQL, ex: source is pg15, target is pg15?
Otherwise, anything that can be done to speed this up would be very helpful.
Thanks again,
rik.
On Tue, Aug 1, 2023 at 12:16 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> > In PostgreSQL foreign tables are not automatically analyzed and analyze
> must be
> > specifically called on each table. In the case of sharing tables between
> > PostgreSQL clusters, there is the use_remote_estimate foreign server
> option.
> >
> > In some of our multi terabyte databases, manually running analyze on all
> of
> > the foreign tables can take more than a day. This is per database
> containing
> > the foreign schema. Since we have certain large schema that we have
> centrally
> > located and share to all of our other database clusters, this really
> adds up.
> >
> > use_remote_estimate isn't really a solution as it adds way too much
> overhead
> > and processing time to every query run.
> >
> > Since these tables are being continuously analyzed in the database that
> hosts
> > the data, is there some way that they statistics could be easily passed
> through
> > the foreign server mechanism to the remote database that's calling the
> query?
> >
> > Unless I'm missing something we can either:
> > 1. manually run analyze on each foreign table in each database that
> points to
> > the host table
> > 2. set use_remote_estimate = true which will cause PostgreSQL to
> re-obtain
> > statistics on a per query basis.
> >
> > What I am hoping for is either:
> > 1. pass through the results of analyze from the source database to the
> one
> > where the foreign query is being run
> > 2. add the ability to automatically run analyze on foreign tables just
> as they
> > are currently run on local tables.
> >
> > Of the two, #1 would seem to be the easiest and least wasteful of
> resources.
>
> Unfortunately, both your wishes don't look feasible:
>
> - Transferring table statistics would mean that PostgreSQL understands
> statistics
> from other server versions. This is complicated, and we have decided
> not to
> do this for pg_upgrade, so I don't think we'll try to do it here.
>
> - Autoanalyzing foreign tables would mean that we have some idea how much
> data
> has changed on the remote server. How should we do that?
>
> What I can imagine is that instead of reading the complete remote table
> during
> ANALYZE, PostgreSQL applies TABLESAMPLE to fetch only part. That could be
> a
> workable enhancement.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 16:16 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-01 16:36 ` Re: analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-08-01 17:51 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-08-01 18:36 ` Re: analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-08-01 17:51 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
I just do it like this per table. Might not solve your exact issue, but another option. You can scale down the analyze factor to something very small like 0.00000001
-- Find current setting (this is at database level)
select * from pg_settings where name in ('autovacuum','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold','autovacuum_vacuum_scale_factor');
select current_setting('autovacuum_vacuum_scale_factor') as "analyze_scale_factor",current_setting('autovacuum_vacuum_threshold') as "vacuum_threshold";
select current_setting('autovacuum_analyze_scale_factor') as "analyze_scale_factor", current_setting('autovacuum_analyze_threshold') as "analyze_threshold";
-- Note: The smaller number = more aggressive = vacuum more frequence
-- Current:
-- autovacuum_analyze_scale_factor = 0.05 ---> 0.002
-- autovacuum_vacuum_scale_factor = 0.1 ---> 0.001
-- Fine Tune at table level = ALTER TABLE mytable SET (autovacuum_analyze_scale_factor = 0.02);
ALTER TABLE your_schema.your_table SET (autovacuum_enabled = true,autovacuum_analyze_scale_factor = 0.002,autovacuum_vacuum_scale_factor = 0.001);
-- Put it back to use global setting
ALTER TABLE your_schema.your_table RESET (autovacuum_enabled,autovacuum_analyze_scale_factor,autovacuum_vacuum_scale_factor);
From: richard coleman <rcoleman.ascentgl@gmail.com>
Sent: Tuesday, August 1, 2023 9:36 AM
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: analyze foreign tables
Laurenz,
Thanks for taking the time to respond.
Right now I'm stuck with cronning a script to manually run analyze on every foreign table in every database, which in our case is most of them.
Would it be possible to transfer table statistics between the same version of PostgreSQL, ex: source is pg15, target is pg15?
Otherwise, anything that can be done to speed this up would be very helpful.
Thanks again,
rik.
On Tue, Aug 1, 2023 at 12:16 PM Laurenz Albe <laurenz.albe@cybertec.at<mailto:laurenz.albe@cybertec.at>> wrote:
On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> In PostgreSQL foreign tables are not automatically analyzed and analyze must be
> specifically called on each table. In the case of sharing tables between
> PostgreSQL clusters, there is the use_remote_estimate foreign server option.
>
> In some of our multi terabyte databases, manually running analyze on all of
> the foreign tables can take more than a day. This is per database containing
> the foreign schema. Since we have certain large schema that we have centrally
> located and share to all of our other database clusters, this really adds up.
>
> use_remote_estimate isn't really a solution as it adds way too much overhead
> and processing time to every query run.
>
> Since these tables are being continuously analyzed in the database that hosts
> the data, is there some way that they statistics could be easily passed through
> the foreign server mechanism to the remote database that's calling the query?
>
> Unless I'm missing something we can either:
> 1. manually run analyze on each foreign table in each database that points to
> the host table
> 2. set use_remote_estimate = true which will cause PostgreSQL to re-obtain
> statistics on a per query basis.
>
> What I am hoping for is either:
> 1. pass through the results of analyze from the source database to the one
> where the foreign query is being run
> 2. add the ability to automatically run analyze on foreign tables just as they
> are currently run on local tables.
>
> Of the two, #1 would seem to be the easiest and least wasteful of resources.
Unfortunately, both your wishes don't look feasible:
- Transferring table statistics would mean that PostgreSQL understands statistics
from other server versions. This is complicated, and we have decided not to
do this for pg_upgrade, so I don't think we'll try to do it here.
- Autoanalyzing foreign tables would mean that we have some idea how much data
has changed on the remote server. How should we do that?
What I can imagine is that instead of reading the complete remote table during
ANALYZE, PostgreSQL applies TABLESAMPLE to fetch only part. That could be a
workable enhancement.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 16:16 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-01 16:36 ` Re: analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 17:51 ` analyze foreign tables Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-08-01 18:36 ` richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: richard coleman @ 2023-08-01 18:36 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Mathew,
Thanks but I think you might have misunderstood my concern. I am talking
about foreign tables, autovacuum explicitly doesn't work on foreign tables.
rik.
On Tue, Aug 1, 2023 at 1:51 PM Wetmore, Matthew (CTR) <
Matthew.Wetmore@express-scripts.com> wrote:
> I just do it like this per table. Might not solve your exact issue, but
> another option. You can scale down the analyze factor to something very
> small like 0.00000001
>
>
>
> -- Find current setting (this is at database level)
>
> select * from pg_settings where name in
> ('autovacuum','autovacuum_analyze_scale_factor','autovacuum_analyze_threshold','autovacuum_vacuum_scale_factor');
>
> select current_setting('autovacuum_vacuum_scale_factor') as
> "analyze_scale_factor",current_setting('autovacuum_vacuum_threshold') as
> "vacuum_threshold";
>
> select current_setting('autovacuum_analyze_scale_factor') as
> "analyze_scale_factor", current_setting('autovacuum_analyze_threshold') as
> "analyze_threshold";
>
> -- Note: The smaller number = more aggressive = vacuum more frequence
>
> -- Current:
>
> -- autovacuum_analyze_scale_factor = 0.05 ---> 0.002
>
> -- autovacuum_vacuum_scale_factor = 0.1 ---> 0.001
>
> -- Fine Tune at table level = ALTER TABLE mytable SET
> (autovacuum_analyze_scale_factor = 0.02);
>
> ALTER TABLE your_schema.your_table SET (autovacuum_enabled =
> true,autovacuum_analyze_scale_factor = 0.002,autovacuum_vacuum_scale_factor
> = 0.001);
>
> -- Put it back to use global setting
>
> ALTER TABLE your_schema.your_table RESET
> (autovacuum_enabled,autovacuum_analyze_scale_factor,autovacuum_vacuum_scale_factor);
>
>
>
> *From:* richard coleman <rcoleman.ascentgl@gmail.com>
> *Sent:* Tuesday, August 1, 2023 9:36 AM
> *To:* Laurenz Albe <laurenz.albe@cybertec.at>
> *Cc:* Pgsql-admin <pgsql-admin@lists.postgresql.org>
> *Subject:* [EXTERNAL] Re: analyze foreign tables
>
>
>
> Laurenz,
>
>
>
> Thanks for taking the time to respond.
>
>
>
> Right now I'm stuck with cronning a script to manually run analyze on
> every foreign table in every database, which in our case is most of them.
>
>
>
> Would it be possible to transfer table statistics between the same version
> of PostgreSQL, ex: source is pg15, target is pg15?
>
>
>
> Otherwise, anything that can be done to speed this up would be very
> helpful.
>
>
>
> Thanks again,
>
> rik.
>
>
>
>
>
> On Tue, Aug 1, 2023 at 12:16 PM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>
> On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> > In PostgreSQL foreign tables are not automatically analyzed and analyze
> must be
> > specifically called on each table. In the case of sharing tables between
> > PostgreSQL clusters, there is the use_remote_estimate foreign server
> option.
> >
> > In some of our multi terabyte databases, manually running analyze on all
> of
> > the foreign tables can take more than a day. This is per database
> containing
> > the foreign schema. Since we have certain large schema that we have
> centrally
> > located and share to all of our other database clusters, this really
> adds up.
> >
> > use_remote_estimate isn't really a solution as it adds way too much
> overhead
> > and processing time to every query run.
> >
> > Since these tables are being continuously analyzed in the database that
> hosts
> > the data, is there some way that they statistics could be easily passed
> through
> > the foreign server mechanism to the remote database that's calling the
> query?
> >
> > Unless I'm missing something we can either:
> > 1. manually run analyze on each foreign table in each database that
> points to
> > the host table
> > 2. set use_remote_estimate = true which will cause PostgreSQL to
> re-obtain
> > statistics on a per query basis.
> >
> > What I am hoping for is either:
> > 1. pass through the results of analyze from the source database to the
> one
> > where the foreign query is being run
> > 2. add the ability to automatically run analyze on foreign tables just
> as they
> > are currently run on local tables.
> >
> > Of the two, #1 would seem to be the easiest and least wasteful of
> resources.
>
> Unfortunately, both your wishes don't look feasible:
>
> - Transferring table statistics would mean that PostgreSQL understands
> statistics
> from other server versions. This is complicated, and we have decided
> not to
> do this for pg_upgrade, so I don't think we'll try to do it here.
>
> - Autoanalyzing foreign tables would mean that we have some idea how much
> data
> has changed on the remote server. How should we do that?
>
> What I can imagine is that instead of reading the complete remote table
> during
> ANALYZE, PostgreSQL applies TABLESAMPLE to fetch only part. That could be
> a
> workable enhancement.
>
> Yours,
> Laurenz Albe
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 16:16 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-01 16:36 ` Re: analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-08-01 19:15 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-01 19:27 ` Re: analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2023-08-01 19:15 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, 2023-08-01 at 12:36 -0400, richard coleman wrote:
> Would it be possible to transfer table statistics between the same version
> of PostgreSQL, ex: source is pg15, target is pg15?
There is no support for that.
If you know what you are doing, you might be able to mess with the catalog tables,
but you would probably need server C code for that, since you cannot normally
write to an "anyarray".
I wouldn't recommend to go that way.
> Otherwise, anything that can be done to speed this up would be very helpful.
Run the ANALYZE in many parallel sessions. I cannot think of anything smarter.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 16:16 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-01 16:36 ` Re: analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 19:15 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-08-01 19:27 ` richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: richard coleman @ 2023-08-01 19:27 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Laurenz,
Since my ability to program in C is basically non-existent, there's no
danger of me writing custom PostgreSQL C code anytime soon.
Maybe someday when I retire I'll take up C coding to keep my mind nimble.
So increasing parallelization it is.
Thanks again,
rik.
On Tue, Aug 1, 2023 at 3:15 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Tue, 2023-08-01 at 12:36 -0400, richard coleman wrote:
> > Would it be possible to transfer table statistics between the same
> version
> > of PostgreSQL, ex: source is pg15, target is pg15?
>
> There is no support for that.
>
> If you know what you are doing, you might be able to mess with the catalog
> tables,
> but you would probably need server C code for that, since you cannot
> normally
> write to an "anyarray".
>
> I wouldn't recommend to go that way.
>
> > Otherwise, anything that can be done to speed this up would be very
> helpful.
>
> Run the ANALYZE in many parallel sessions. I cannot think of anything
> smarter.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-08-03 12:37 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-03 13:02 ` Re: analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-04 14:48 ` Re: analyze foreign tables Jeff Janes <jeff.janes@gmail.com>
2 siblings, 2 replies; 12+ messages in thread
From: Laurenz Albe @ 2023-08-03 12:37 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> In PostgreSQL foreign tables are not automatically analyzed and analyze must be
> specifically called on each table. In the case of sharing tables between PostgreSQL
> clusters, there is the use_remote_estimate foreign server option.
>
> In some of our multi terabyte databases, manually running analyze on all of the
> foreign tables can take more than a day. This is per database containing the
> foreign schema. Since we have certain large schema that we have centrally located
> and share to all of our other database clusters, this really adds up.
I just saw that PostgreSQL v16 uses remote sampling for ANALYZE on foreign tables.
This is governed by the option "analyze_sampling" on the foreign table or the foreign
server, and the default value "auto" should be just what you need.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-03 12:37 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-08-03 13:02 ` richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: richard coleman @ 2023-08-03 13:02 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Laurenz,
That's great news. Now if only transparent data encryption also arrives in
PostgreSQL 16, it will be a time for celebration.
rik.
On Thu, Aug 3, 2023 at 8:37 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> > In PostgreSQL foreign tables are not automatically analyzed and analyze
> must be
> > specifically called on each table. In the case of sharing tables
> between PostgreSQL
> > clusters, there is the use_remote_estimate foreign server option.
> >
> > In some of our multi terabyte databases, manually running analyze on all
> of the
> > foreign tables can take more than a day. This is per database
> containing the
> > foreign schema. Since we have certain large schema that we have
> centrally located
> > and share to all of our other database clusters, this really adds up.
>
> I just saw that PostgreSQL v16 uses remote sampling for ANALYZE on foreign
> tables.
> This is governed by the option "analyze_sampling" on the foreign table or
> the foreign
> server, and the default value "auto" should be just what you need.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-03 12:37 ` Re: analyze foreign tables Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-08-04 14:48 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Jeff Janes @ 2023-08-04 14:48 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: richard coleman <rcoleman.ascentgl@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Aug 3, 2023 at 8:37 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> > In PostgreSQL foreign tables are not automatically analyzed and analyze
> must be
> > specifically called on each table. In the case of sharing tables
> between PostgreSQL
> > clusters, there is the use_remote_estimate foreign server option.
> >
> > In some of our multi terabyte databases, manually running analyze on all
> of the
> > foreign tables can take more than a day. This is per database
> containing the
> > foreign schema. Since we have certain large schema that we have
> centrally located
> > and share to all of our other database clusters, this really adds up.
>
> I just saw that PostgreSQL v16 uses remote sampling for ANALYZE on foreign
> tables.
> This is governed by the option "analyze_sampling" on the foreign table or
> the foreign
> server, and the default value "auto" should be just what you need.
>
>
In addition to this new feature, analyzing foreign tables can also be
highly dependent on an old feature, fetch_size. The default fetch_size is
really quite small and might be a bottleneck for ANALYZE.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-08-03 14:19 ` Jeff Janes <jeff.janes@gmail.com>
2023-08-03 16:16 ` Re: analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Jeff Janes @ 2023-08-03 14:19 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Aug 1, 2023 at 9:47 AM richard coleman <rcoleman.ascentgl@gmail.com>
wrote:
>
> use_remote_estimate isn't really a solution as it adds way too much
> overhead and processing time to every query run.
>
Maybe this is the thing which should be addressed. Can you quantify what
you see here? How much overhead is being added for each query? Is this
principally processing time, or network latency?
Since these tables are being continuously analyzed in the database that
> hosts the data, is there some way that they statistics could be easily
> passed through the foreign server mechanism to the remote database that's
> calling the query?
>
Since FDW can cross version boundaries, it is hard to see how this would
work. Maybe something could be done for the special case of where the
versions match. I think collations/encoding would be a problem, though.
> What I am hoping for is either:
>
> 2. add the ability to automatically run analyze on foreign tables just as
> they are currently run on local tables.
>
That wouldn't work because communication is always initiated on the wrong
side. But it should be fairly easy to script something outside of the
database which would connect to both, and poll the "foreign"
pg_stat_all_tables.last_autovacuum and initiate a local ANALYZE for each
table which was recently autoanalyzed on the foreign side.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: analyze foreign tables
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-03 14:19 ` Re: analyze foreign tables Jeff Janes <jeff.janes@gmail.com>
@ 2023-08-03 16:16 ` richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: richard coleman @ 2023-08-03 16:16 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Jeff,
In my experience the overhead is directly related to the size and the
complexity of the tables in the query.
A simple query only referencing a small < 5M table with only a primary key,
it isn't noticeable.
A typical complicated query referencing numerous tables ranging in sizes
from <5M to > 2.5T each with a primary key and 0 - 30 indices (per table),
it can add 30 seconds to many minutes to each run of the query. When some
of these queries are re-run constantly it becomes untenable. In one case,
manually running analyze on the foreign tables and then running the query
it returns in about 30 sec. Setting use_remote_estimate = true made each
run return in about 5 minutes.
Unfortunately, manually running analyze on each foreign table in the schema
(500+ tables, ranging from < 1M to > 3T) takes more than a day to
complete. On the server hosting the tables, the auto analyze is running
constantly, as expected. All of the clusters are sitting at PostgreSQL 15.
Hence my desire to find a more performant, less resource intensive way to
pass the continuously updated statistics of these tables to the other
PostgreSQL clusters holding the foreign table pointers to them.
I know it's anecdotal, but I hope it helps anyway.
rik.
On Thu, Aug 3, 2023 at 10:19 AM Jeff Janes <jeff.janes@gmail.com> wrote:
> On Tue, Aug 1, 2023 at 9:47 AM richard coleman <
> rcoleman.ascentgl@gmail.com> wrote:
>
>>
>> use_remote_estimate isn't really a solution as it adds way too much
>> overhead and processing time to every query run.
>>
>
> Maybe this is the thing which should be addressed. Can you quantify what
> you see here? How much overhead is being added for each query? Is this
> principally processing time, or network latency?
>
>
> Since these tables are being continuously analyzed in the database that
>> hosts the data, is there some way that they statistics could be easily
>> passed through the foreign server mechanism to the remote database that's
>> calling the query?
>>
>
> Since FDW can cross version boundaries, it is hard to see how this would
> work. Maybe something could be done for the special case of where the
> versions match. I think collations/encoding would be a problem, though.
>
>
>> What I am hoping for is either:
>>
>> 2. add the ability to automatically run analyze on foreign tables just as
>> they are currently run on local tables.
>>
>
> That wouldn't work because communication is always initiated on the wrong
> side. But it should be fairly easy to script something outside of the
> database which would connect to both, and poll the "foreign"
> pg_stat_all_tables.last_autovacuum and initiate a local ANALYZE for each
> table which was recently autoanalyzed on the foreign side.
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2023-08-04 14:48 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-01 13:47 analyze foreign tables richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 16:16 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-01 16:36 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 17:51 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-08-01 18:36 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-01 19:15 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-01 19:27 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-03 12:37 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-08-03 13:02 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-04 14:48 ` Jeff Janes <jeff.janes@gmail.com>
2023-08-03 14:19 ` Jeff Janes <jeff.janes@gmail.com>
2023-08-03 16:16 ` richard coleman <rcoleman.ascentgl@gmail.com>
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