Subject: analyze foreign tables

From: richard coleman <[email-masked]>
Date: Tue, 1 Aug 2023 09:47:33 -0400

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.

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 01 Aug 2023 18:16:01 +0200

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.
> 
> 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?
> 
> 1. manually run analyze on each foreign table in each database that points to
>    the host table
> 2. add the ability to automatically run analyze on foreign tables just as they
>    are currently run on local tables.
> 

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

---

From: richard coleman <[email-masked]>
Date: Tue, 1 Aug 2023 12:36:28 -0400

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 <[email-masked]>
wrote:

> must be
> containing
> hosts

---

From: "Wetmore, Matthew (CTR)" <[email-masked]>
Date: Tue, 1 Aug 2023 17:51:56 +0000

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 <[email-masked]>
Sent: Tuesday, August 1, 2023 9:36 AM
To: Laurenz Albe <[email-masked]>
Cc: Pgsql-admin <[email-masked]>
Subject: [EXTERNAL] Re: analyze foreign tables

Laurenz,

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?

Thanks again,
rik.

On Tue, Aug 1, 2023 at 12:16 PM Laurenz Albe <[email-masked]<mailto:[email-masked]>> wrote:

Yours,
Laurenz Albe

---

From: richard coleman <[email-masked]>
Date: Tue, 1 Aug 2023 14:36:47 -0400

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) <
[email-masked]> 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
>
>
>
> 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";
>
> -- 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);
>
> ALTER TABLE your_schema.your_table RESET
> (autovacuum_enabled,autovacuum_analyze_scale_factor,autovacuum_vacuum_scale_factor);
>
>
>
> *From:* richard coleman <[email-masked]>
> *Sent:* Tuesday, August 1, 2023 9:36 AM
> *To:* Laurenz Albe <[email-masked]>
> *Cc:* Pgsql-admin <[email-masked]>
> *Subject:* [EXTERNAL] Re: analyze foreign tables
>
>
>
> Laurenz,
>
>
>
> 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.
>
>
>

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 01 Aug 2023 21:15:23 +0200

> 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

---

From: richard coleman <[email-masked]>
Date: Tue, 1 Aug 2023 15:27:40 -0400

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.

---

From: Phani Prathyush Somayajula <[email-masked]>
Date: Wed, 2 Aug 2023 13:51:17 +0000

Hi All,

I’ve a user defined data type as :

CREATE TYPE uibackend."_operation" (
                INPUT = array_in,
                OUTPUT = array_out,
                RECEIVE = array_recv,
                SEND = array_send,
                ANALYZE = array_typanalyze,
                ALIGNMENT = 4,
                STORAGE = any,
                CATEGORY = A,
                ELEMENT = uibackend.operation,
                DELIMITER = ',');

I’ve a table :

And its DDL is :
CREATE TABLE uibackend.auditlog (
                id bigserial NOT NULL,
                "module" varchar(100) NULL,
                submodule varchar(100) NULL,
                operation varchar(100) NULL,
                value jsonb NULL,
                modifiedby varchar(100) NULL,
                modifiedat timestamp NULL,
                status uibackend.auditlogstatus NULL,
                CONSTRAINT auditlog_pkey PRIMARY KEY (id)
);

Now I want to change the data type of the column operation to operation data type(which is user defined)
as
ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE operation USING operation::operation;

But I’ve been facing issues like this :

uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE operation using (operation)::operation;
ERROR:  cannot cast type real to operation
LINE 1: ... COLUMN operation TYPE operation using (operation)::operatio...
                                                             ^
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE operation using (_operation)::operation;
ERROR:  column "_operation" does not exist
LINE 1: ...tlog ALTER COLUMN operation TYPE operation using (_operation...
                                                             ^
HINT:  Perhaps you meant to reference the column "auditlog.operation".
uibackend=>
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation;
ERROR:  column "operation" cannot be cast automatically to type operation
HINT:  You might need to specify "USING operation::operation".
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING operation::operation;
ERROR:  cannot cast type real to operation
LINE 1: ...operation TYPE uibackend.operation USING operation::operatio...
                                                             ^
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING uibackend.operation::operation;
ERROR:  missing FROM-clause entry for table "uibackend"
LINE 1: ...R COLUMN operation TYPE uibackend.operation USING uibackend....
                                                             ^
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING uibackend.operation::uibackend.operation;
ERROR:  missing FROM-clause entry for table "uibackend"
LINE 1: ...R COLUMN operation TYPE uibackend.operation USING uibackend....
                                                             ^
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING operation::uibackend.operation;
ERROR:  cannot cast type real to operation
LINE 1: ...operation TYPE uibackend.operation USING operation::uibacken...
                                                             ^
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING operation::uibackend.operation;
ERROR:  cannot cast type real to operation
LINE 1: ...operation TYPE uibackend.operation USING operation::uibacken...
                                                             ^
uibackend=>
uibackend=>
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING operation::uibackend.operation;^C
uibackend=>
uibackend=> ALTER TABLE table_name auditlog ALTER COLUMN operation set data type uibackend.operation us

uibackend=> ALTER TABLE table_name auditlog ALTER COLUMN operation set data type uibackend.operation using operation::operation;
ERROR:  syntax error at or near "auditlog"
LINE 1: ALTER TABLE table_name auditlog ALTER COLUMN operation set d...
                               ^
uibackend=> ALTER TABLE table_name uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation;
ERROR:  syntax error at or near "uibackend"
LINE 1: ALTER TABLE table_name uibackend.auditlog ALTER COLUMN opera...
                               ^
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation;
ERROR:  column "operation" cannot be cast automatically to type operation
HINT:  You might need to specify "USING operation::operation".
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using operation::operation;
ERROR:  cannot cast type real to operation
LINE 1: ... set data type uibackend.operation using operation::operatio...
                                                             ^
uibackend=>
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using (operation)::operation;
ERROR:  cannot cast type real to operation
LINE 1: ...et data type uibackend.operation using (operation)::operatio...
                                                             ^
uibackend=>
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using (operation)::text;
ERROR:  result of USING clause for column "operation" cannot be cast automatically to type operation
HINT:  You might need to add an explicit cast.
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using operation::text;
ERROR:  result of USING clause for column "operation" cannot be cast automatically to type operation
HINT:  You might need to add an explicit cast.
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using auditlog.operation::text;
ERROR:  result of USING clause for column "operation" cannot be cast automatically to type operation
HINT:  You might need to add an explicit cast.
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using (operation());
ERROR:  function operation() does not exist
LINE 1: ...peration set data type uibackend.operation using (operation(...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using (operation);
ERROR:  result of USING clause for column "operation" cannot be cast automatically to type operation
HINT:  You might need to add an explicit cast.
uibackend=> set search_path to uibackend;
SET
uibackend=> alter table auditlog alter COLUMN operation type operation using operation::operation;
ERROR:  cannot cast type real to operation
LINE 1: ...er COLUMN operation type operation using operation::operatio...
                                                             ^
uibackend=> alter table auditlog alter COLUMN operation type operation using operation::uibackend._operation;
ERROR:  cannot cast type real to operation[]
LINE 1: ...er COLUMN operation type operation using operation::uibacken...
                                                             ^
uibackend=> alter table auditlog alter COLUMN operation type operation using (operation)::uibackend._operation;
ERROR:  cannot cast type real to operation[]
LINE 1: ... COLUMN operation type operation using (operation)::uibacken...
                                                             ^
uibackend=> alter table auditlog alter COLUMN operation type operation using CA

uibackend=> alter table auditlog alter COLUMN operation type operation using CAST(operation as operation);
ERROR:  cannot cast type real to operation
LINE 1: ...itlog alter COLUMN operation type operation using CAST(opera...
                                                             ^
uibackend=> alter table auditlog alter COLUMN operation type operation using CAST(operation as uibackend.operation);
ERROR:  cannot cast type real to operation
LINE 1: ...itlog alter COLUMN operation type operation using CAST(opera...
                                                             ^
uibackend=> alter table auditlog alter COLUMN operation type operation using operation::text;
ERROR:  result of USING clause for column "operation" cannot be cast automatically to type operation
HINT:  You might need to add an explicit cast.
uibackend=> alter table auditlog alter COLUMN operation set data type operation using operation::text;
ERROR:  result of USING clause for column "operation" cannot be cast automatically to type operation
HINT:  You might need to add an explicit cast.
uibackend=> alter table auditlog alter COLUMN operation set data type operation using (operation)::text;
ERROR:  result of USING clause for column "operation" cannot be cast automatically to type operation
HINT:  You might need to add an explicit cast.
uibackend=> alter table auditlog alter COLUMN operation type operation using CAST('operation' as uibackend.operation);
ERROR:  invalid input value for enum operation: "operation"
LINE 1: ... alter COLUMN operation type operation using CAST('operation...
                                                             ^
uibackend=> alter table auditlog alter COLUMN operation type operation using CAST('operation' as uibackend._operation);
ERROR:  malformed array literal: "operation"
LINE 1: ... alter COLUMN operation type operation using CAST('operation...
                                                             ^
DETAIL:  Array value must start with "{" or dimension information.
uibackend=> alter table auditlog alter COLUMN operation type operation using CAST{'operation' as uibackend._operation};
ERROR:  syntax error at or near "{"
LINE 1: ...g alter COLUMN operation type operation using CAST{'operatio...
                                                             ^
uibackend=> alter table auditlog alter COLUMN operation type operation using CAST({'operation'} as uibackend._operation);
ERROR:  syntax error at or near "{"
LINE 1: ... alter COLUMN operation type operation using CAST({'operatio...
                                                             ^
uibackend=> alter table auditlog alter COLUMN operation type operation using CAST({'operation'} as operation);
ERROR:  syntax error at or near "{"
LINE 1: ... alter COLUMN operation type operation using CAST({'operatio...
                                                             ^
uibackend=> alter table auditlog alter COLUMN operation type operation using CAST('operation' as uibackend._operation);
ERROR:  malformed array literal: "operation"
LINE 1: ... alter COLUMN operation type operation using CAST('operation...
                                                             ^
DETAIL:  Array value must start with "{" or dimension information.
uibackend=> alter table auditlog alter COLUMN operation type operation using CAST({operation} as uibackend._operation);
ERROR:  syntax error at or near "{"
LINE 1: ... alter COLUMN operation type operation using CAST({operation...
                                                             ^
uibackend=>
uibackend=> alter table auditlog alter COLUMN operation type operation using CAST('operation{}' as uibackend._operation);
ERROR:  malformed array literal: "operation{}"
LINE 1: ... alter COLUMN operation type operation using CAST('operation...
                                                             ^
DETAIL:  Array value must start with "{" or dimension information.
uibackend=>

Any suggestions how to modify the column ?

PS: I had to paste this lengthy log because I wanted you all know that I’ve tried these many ways to change the data type in vain.

Regards,
Pratz

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 2 Aug 2023 09:42:20 -0700

> CREATE TYPE uibackend."_operation" (

If you find yourself writing user-space code that uses "_{data type}" you
are doing something wrong.  That implementation detail is not something
that is exposed to the user.  If you want to deal with arrays of a type you
say:  {data type}[]

So casting some random text column to an array of operation is simply:

operation_text_col::operation[]

And you can get rid of the above CREATE TYPE command altogether.

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 2 Aug 2023 09:50:41 -0700

Seriously, you sent this to three lists at the same time?  I'm surprised it
came through moderation...

Anyway, my response on the admin list is below - though none of the lists
you picked were actually the appropriate one.  You want the -general list.

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 03 Aug 2023 14:37:11 +0200

On Tue, 2023-08-01 at 09:47 -0400, richard coleman wrote:
> 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

---

From: richard coleman <[email-masked]>
Date: Thu, 3 Aug 2023 09:02:45 -0400

Laurenz,

That's great news.  Now if only transparent data encryption also arrives in
PostgreSQL 16, it will be a time for celebration.

rik.

---

From: Jeff Janes <[email-masked]>
Date: Thu, 3 Aug 2023 10:19:19 -0400

> 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?

> passed through the foreign server mechanism to the remote database that's

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.

> 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

---

From: richard coleman <[email-masked]>
Date: Thu, 3 Aug 2023 12:16:14 -0400

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 <[email-masked]> wrote:

> On Tue, Aug 1, 2023 at 9:47 AM richard coleman <
> [email-masked]> wrote:
>
>>

---

From: Jeff Janes <[email-masked]>
Date: Fri, 4 Aug 2023 10:48:00 -0400

> 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